MVCC – the part of PostgreSQL we hate the most (2023)

With every new PostgreSQL release we see yet more features and sugar added to the frontend, yet seemingly no meaningful improvement to the backend/storage layer which suffers these fundamental problems.

I wish the PostgreSQL community would stop chasing more frontend features and spend a concerted few years completely renovating their storage layer. The effort in each release seems massively and disproportionately skewed towards frontend improvements without the will to address these fundamental issues.

It's absurd that in 2024, "the world's most advanced open source database" doesn't have a method of doing upgrades between major versions that doesn't involve taking the database down.

Yes, logical replication exists, but it still doesn't do DDL, so it has big caveats attached.

what about https://github.com/orioledb/orioledb ?

> a method of doing upgrades between major versions that doesn't involve taking the database down.

For large instances this is a big ask, especially of a project without single person in charge. MySQL does have better replication, yet still often requires manually setting that up and cutting it over to do major version upgrades.

The design of good storage layers in databases is deeply architectural. As a consequence, it is essentially a "forever" design decision. Fundamentally changing the storage architecture will alter the set of tradeoffs being made such that it will break the assumptions of existing user applications, which is generally considered a Very Bad Thing. The existing architecture, with all its quirks and behaviors, is part of the public API (see also: Hyrum's Law).

In practice, the only way to change the fundamental architecture of a database is to write a new one, with everything that entails.

Better yet: decouple front and back ends. Let them talk over a stable interface and evolve independently. The SQLite ecosystem is evolving in this direction, in fits and starts.

You can implement a different mvcc model today, without patching code.

You could select which storage approach on a per table level. That way the new characteristics don’t surprise anyone.

This is the MySQL approach, but it isn't without downsides - consistency, predictability, etc.

That's my big issue with PostgreSQL. I don't care about DDL with logical replication as I can build an replica for the switch, but no LOBs?

Rearchitecting the storage layer takes time. A storage manager API didn't even exist until fairly recently, maybe 14. That API needs to undergo changes to account for things that Oriole is trying to do. Postgres is not developed by a team. It's a community effort, and people work on what they want to work on. If you're interested in a previous attempt to change the storage layer, you can learn about what happened with zheap[0].

[0]: https://pgpedia.info/z/zheap.html

This has already been done.