Perhaps I don't know enough about databases and their differences.
Anyone have some pros and cons of others?
Like why would I pick MySQL, Microsoft, Oracle, Maria etc over Postgres?
Apart from support that you gotta pay for.
In the same way that Golang claims it's the 90% language ( https://talks.golang.org/2014/gocon-tokyo.slide#1 ), I'd say Postgres is the perfect 90% database.
However, we're still using at least four other databases in production, and the reason why is that because it's doing something of everything, there are lots of niches that it doesn't fill well. It's all about tradeoffs.
If you've got a Full Text Search problem, PG is "good enough", that is until you need to index more challenging languages like Korean or need more exotic scoring functions. For that, Elastic is better.
You can do small scale analytics on PG, but at larger sizes, the transactional setup gets in the way and you're better off with BigQuery/Redshift/Snowflake.
You can scale vertical pretty big these days, but if you need linear scalability while still guaranteeing single digit ms access, you're better off with ScyllaDB.
However, there isn't a single project I wouldn't not start on a Postgres these days, that's how much I love it.
PG was driven by engineering correctness, by considering what DBAs 'Are Going to Need'. Sometimes that strictness worked against popular growth but in the end it has worked out well, as many programmers figured out they also needed it. I'd say the programming language analogue would likely be Rust, lets see in 10 years where the 90% case lies.
However, if you are writing some code with multi-threading (who isn't these days), then you may eventually want the help Rust gives you there as far as correctness goes.
I'b be interested to know about the limits of the full text feature.
... and wins, which surprised me when I found out.
At the very least, CockroachDB is much simpler to set up and manage. Cannot vouch for its stability and complexity of abnormal emergency recoveries, though - haven't used it long enough, only had simplest outages (that it had handled flawlessly).
Just haven't had the reason to dive in yet, but seems good. Their PR department just isn't as good as CockroachDBs one.
I wonder why Postgre hasn't improved in these areas and instead leaving it to third party solution. I mean every year these few points are still listed as something that flavours MySQL.
Oracle is the choice of organisations with money to burn who don't believe that free products can be good. An Oracle installation nearly always comes with some consultants to write your application for you, and define your application for you, and write your invoices for you ...
It's not that you can't create a master-master setup using Postgresql, I believe 2ndquadrant have and add-on that allows this. It just feels like it's messing with the fundamentals of the database on such a low level that I really only trust it, if it's part of Postgresql it self.
There are naturally caveats: sequences don't get replicated, so you need to configure each replica with a non-overlapping range for each sequence. And DDL statements are also not replicated, so you have to migrate database schemas by hand on each replica.
[1] https://wiki.postgresql.org/wiki/Replication,_Clustering,_an...
It is however a building block for logical replication, and used by https://www.postgresql.org/docs/current/logical-replication....
> from what I can see of the wiki [1
I really wish we'd just shut off the wiki. There's random pages starting to be maintained by someone that then stops at some point. Leads to completely outdated content, as in this case.
- the code is not reusable outside of a database setting. So not cacheable.
- the code is not reusable accross different storage layers. So not portable.
- the code may needs updating if the schema change, you can't abstract that
- changing the logic means a db migration
- testing the code requires a DB
- tooling support to check that code si limited to SQL tooling, which is very weak, especially for code completion, refactor and debugging.
That's a lot of constraints for just making the application logic a lot simpler.
- it is cached, in the database’s memory, where the cache can be invalidated automatically. It is better to cache views than data anyway.
- it is portable to every platform postgres runs, which in practice means it will run everywhere. Portability between databases is overrated because it rarely happens in practice.
- the access control logic evolves together with the schema, guaranteeing they have an exact correspondence. This is a good thing.
- integration tests should involve a live database
- have you looked at jetbrains datagrip?
Since most popular product in the JS world are product to avoid writing Javascript (webpack+babel, typescript, coffeescript, jsx, etc), and plv8 supports non of that nor does it support standard unit tests framework like Jest, I'm not convinced.
To make things even worst, remote debugging is not supported anymore: https://github.com/plv8/plv8/issues/131#issuecomment-2377111...
If I had to do something that feels too complex, I'd write some interfaces in TypeScript to represent the tuples, then implement the function in TS, and compile it into plain JS in order to deploy it to plv8.
The cache may not be for the data in the data base, but for something else (task queue, calculation, user session, pre-rendering, etc). You effectively split your cache into several systems.
> it is portable to every platform postgres runs, which in practice means it will run everywhere. Portability between databases is overrated because it rarely happens in practice.
As I mentioned, for a project it doesn't matter. For a lib it does.
> the access control logic evolves together with the schema, guaranteeing they have an exact correspondence. This is a good thing.
Not always. Your access control logic could be evolving with your model abstraction layer, which frees you to make changes to the underlying implementation without having to change the control logic every time. It's also a good for your unit tests, as they are not linked to something with a side effect.
> integration tests should involve a live database
Integration tests are slow. You can run them as you develop.
> Have you looked at jetbrains datagrip?
It's fantastic. If you are willing to pay the price of it for and force it on your entire team. If you are on an open source project, will your expect that from all your contributors ?
High barrier of entry, with no modularity. Linters, formatters, debuggers, auto-importers, they all depend on that one graphical commercial product that is not integrated with your regular IDE and other tools.
Not to say it's not a good editor if you do write a lot of SQL, as JetBrains products are always worth their price.
In my 23-and-a-bit years of web development I've literally never changed the database engine on a project. Maybe that happens on other people's projects, but it's not something I consider important or even useful really. The notion that you can swap out your database for a different one without changing the application code to take advantage of the db you're moving to is ludicrous. Of course your database code isn't portable.
Websites that used MySQL and Myisam tables with raw SQL statements written as strings in PHP for the first decade of the web is one of the reasons why so many web developers still don't use things like transactions, stored procedures, views, etc. That's a bit of a tragedy. The web would be much better today if everyone had been using Postgres's features from the beginning.
I've literally never changed the database engine on a project
Then there is the case where you don't write a project, but a lib. Or the case where you extract such a lib from a project. In that case, you limit the use case of your lib to your database. Because if you don't change database during your project life time, the users of your lib may start a new project with a different database.
This is why Django ORM allowed such a vibrant ecosystem: not because it allows changing the data base of one project (although it's nice to have and I used it several times), but also because it allow so called "django pluggable apps" to be database independant.
Historically Postgres has thought that the job of the file system, which is basically a bad choice for dbs. MyRocks wipes the floor with it.
TimescaleDB is an interesting new Postgres storage choice. I'm evaluating it. But the free version doesn't do compression on-demand nor transparently. Anyway, it can only get better.
https://stackoverflow.com/questions/1369864/does-postgresql-...
MySQL's innodb, for example, supports per-row-compression. Its completely ineffective. Block compression in tokdub and myrocks etc is a completely different class.
MySQL's innodb also supporta kind of 'page compression' using the file-system's sparse pages. Its also naff.
The closest postgres can get is using zfs with compression. Its a lot better than nothing.
I think that if timescaledb can support time-based or mru-based compression and even row-store partitions then it would take things to the next level.
https://blog.timescale.com/blog/building-columnar-compressio...
Our own experience was that most users were getting 3-6x compression running TimescaleDB with zfs.
With our native compression -- which does columnar projections, where a type-specific compression algorithm is applied per column -- we see median compression rates across our users of 15x, while 25% of our users see more than 50x compression rates.
https://www.timescale.com/products/features https://docs.timescale.com/latest/using-timescaledb/compress...
(The "community" edition is available under our Timescale License. It's all source available and free-to-use, the license just places some restrictions if you are a cloud vendor offering DBaaS.)
In my use cases I have got recent partitions that are upsert heavy, so row-based works best. But as the partitions age, column-based would be better. What every DB seems to force me to do is use the same underlying format for all partitions. What I’d like is a storage engine that automates everything; instead of me picking partition size, it picks on the fly and makes adjustments. Instead of me picking olap vs oltp, it picks and migrates partitions over time etc.
PG sure seems to have better foundations and generally a more engineered approach to everything. This leads to some noteworthy outcomes in the tooling or the developer UX, for example: PG won't allow a union type (like String or Integer) in query arguments. PGs query argument handling is rather simple (at least the stuff provided with libpq), it only allows positional arguments.
Sometimes, MySQL by extending or bending SQL here and there allows for some queries to be written terser, like an update+join over multiple tables. Also I did actually enjoy that MySQL had less advanced features, so it seemed to be easier to reason about performance and overall behaviour.
And in plenty of situations, sqlite is perfectly adequate, and saves you from the extra work of managing yet another service.
But yes, I agree if I'd start from a blank sheet and need a network-accessible SQL DB, pgSQL would be my first choice. Now if I had some very high end requirements, maybe one of those big commercial DB's would have an advantage.
Large companies use some of everything, but I never saw PG in use there.
Yahoo’s use of Postgres was legendary.
That's just one data warehouse application.
The one I administered, just as big, was Saturn. And it was MySQL.
Source: worked at Yahoo, saw way more MySQL than PG.
Now you say you did see some Postgres but there was more MySQL. Fine, you were there, I’ll take your word for it. But I can’t reconcile your own statements on this. Yahoo very clearly used Postgres.
You ran a 2PB MySQL install. Cool, I’d love to hear about that, truly. Do you have any written accounts or talks about that?
There's been an ongoing multi-year project to merge Greenplum up to the mainline so that it's no longer a hard fork.
Disclosure: I work for VMware, which sponsors development and sells commercial offerings of Greenplum.
We also have other DB tech in the company: Teradata, Oracle, Redis, Cassandra.