depesz

Sigh. The usual downvotes because... why, you can pick holes in what I said, or some people's identity is so tied up with a piece of open-source software that they can't tolerate apparent criticism of it?

Fact: the optimiser in an SQL engine is almost the quintessence of it. If you don't understand that then you don't understand that the potential cost of the declarative nature of SQL.

Fact: SQL optimisers are complex.

New fact: microsoft has pumped fantastic amounts of cash into MSSQL. Posgres just doesn't have those resources (and other resources, such as the late lamented Jim Gray).

Other fact. Postgres CTEs were an optimisation barrier until PG11 or PG12 (https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user-control-of-cte-materialization-and-change-the-default-behavior/). AFAIK MSSQL's CTEs have never been an optimisation barrier, and I've used them long enough to know. I'm also trying to understand the cost of MVCC, which PG uses but MSSQL now has as an option, to try and understand where that costs, because depending on the scenario, it will. PG does not have READ UNCOMMITTED.

Fact: The guy I quoted had used postgres on large data a while back and said it wasn't as good as MSSQL. He had experience, which is the bottom line. I also acknowledged this may have changed recently.

Other fact: I don't dislike postgres and recently have decided learn PG also, because, for very good reason, MSSQL has its problems (the ridiculous cost not being the…

MongoDB accrued a lot of bad-will due to some extremely questionable defaults, which remain defaults to this day. There's no question that you can write a fast database when there's no guarantee that data ever hits the disk, but developers tend not to like it when a database accepts their write and then silently loses data. It's also great for toy problems and 15-minute-demos... but then you inevitably run into its limitations and end up re-implementing a database in your app.

Even at its best, there is essentially no reason to choose MongoDB over Postgres with JSONB-type columns. They are essentially the same data model but Postgres gives you better guarantees of data consistency, plus a forward migration path to relational data when the day inevitably arrives when you need to model relationships between entities.

At this point Postgres is where most open-source RDBMS development work is concentrated. It's not only a solid codebase, it's piling up features pretty quickly and there are relatively few niches it doesn't fill at least adequately. All of these niches are covered by some commercial products built on top of Postgres (eg EnterpriseDB or CitusDB). It's pretty much a one-stop shop for application development. You can use it for everything from GIS to machine learning [0] pretty efficiently, and it pretty much will just do the right thing without you watching.

NoSQL really fits best around the margins, like as an auxiliary system for analytics. There is…

For those who may be interested: I was curious about the difference between MERGE [1] and UPSERT, and why database engines seem to support a custom (nonstandard) UPSERT instead of the MERGE introduced in standard SQL in 2003, so I did some web research and came across a reasonable-sounding answer in a comment on the following article.

https://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/

> As noted, MERGE is complicated and slow. If two different database servers implement it, then it is going to be implemented with different schematics [sic]. Oracle and MySQL already have different implementation’s, and to nobody’s surprise, MySQL’s implementation allows for risky behavior that does not protect the data. PostgreSQL users expect that any data put into the database will reliably come out of the database. So MERGE is slow and inconsistently implemented. The correct thing to do is make the application aware of if a record is new or existing. Any application making use of MERGE should open a bug to replace it with more predictable and faster logic using INSERT and UPDATE.

> If an application does use MERGE, it has to account for implementation specific behavior and not will not be portable, in a safe way, to other database servers. So it is not suitable for ORM’s or database independent applications.

> So what is a legitimate use case for MERGE where not knowing if a record is new or existing is not possible prior to the transaction? They only thing that I can…

Here's a great article[0] regarding different ways to accomplish to upsert in Postgres. I bring it up because the author adds his thought on why Postgres hasn't implemented it unlike the other open source RDBMS:

> As a final word – yeah, but why PostgreSQL doesn't handle it internally, as the other database?

> Answer lies somewhere between "developers have other things to do first" and "it's complicated, because there are many ways to do it, and the most correct one is slow – so there is a trade off decision to be made".

I'm not sure how upsert is implemented in MySql but given its reputation and the fact that it is open source; I would imagine the Postgres team has considered their solution and have decided it wasn't good enough for their standards.

Postgres may not have an out-of-the-box solution but there are solutions out there which are mostly pretty trivial functions. Although they may add a bit of bloat since it seems you'd need a function for each table you'd want to use upsert on.

[0] http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/

I don't think upsert has taken so long because of the community and processes, but because the Postgresql team has high requirements for it: http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/

For example, upsert that is worth releasing should:

* Work with all unique indexes that may exist on a table, not only a primary key,

* Be correct even at highly concurrent writes, at any isolation level from Read Uncommitted to Serializable,

* Be fast even at highly concurrent writes, so no table locks, locks may not be held for more than an instant, and no re-running the entire transaction in a loop,

* Never throw an "another transaction modified data" exception at Read Committed isolation level, it should always correctly succeed as an insert or update even if another parallel transaction inserted or deleted the matching row.

I'm not familiar much with the other database engines, but my impression has been that their upsert or SQL merge do not provide all of these.

Logical replication is how this is typically done. If you have some infra-as-code setup, you create a new cluster with identical settings except for the major version, import the schema, start copying data from a read-replica running the old version, stop accepting writes from the old version (downtime starts), sync the sequence numbers, and point your services to the new cluster (downtime ends).

If you use something like CloudNativePG they automate parts of the process with cli tools and declarative syntax. Otherwise you take the time to figure it out by hand. It might sound complicated, but just practice on your staging DB, and if all goes well you do the same procedure in prod.

Edit: Apparently Postgres 19 has a patch for one-shot logical replication of sequences! https://www.depesz.com/2025/11/11/waiting-for-postgresql-19-sequence-synchronization-in-logical-replication/