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

Wow, as someone who feels like I&#x27;m decently familiar with the ins and outs of Postgres, I thought this was a great article and I learned a ton.<p>It seems like one of the biggest fundamental flaws is that Postgres chose the O2N approach for tracking row versions instead of N2O. While switching to N2O wouldn&#x27;t solve all problems (e.g. the article also talks about how Postgres stores full row copies and not just diffs), from an &quot;80&#x2F;20 rule&quot; perspective, it seems like it would get rid of most of the downsides with the current implementation. For example, I&#x27;d assume that the vast majority of the time that transactions want the latest row version, so using the N2O ordering means you could probably do away with storing each row version in an index, as you&#x27;d only need to traverse the linked list of you needed an older version, which should be much less common.

The big advantage is that you do not need any extra space if your workload mostly consists of INSERTs (followed by table drops). And it&#x27;s generally unnecessary to split up insertion transactions because there is no size limit as such (neither on the generated data or the total count of rows changed). There is a limit on statements in a transaction, but you can sidestep that by using COPY FROM if you do not have to switch tables too frequently. From a DBA point of view, there is no need to manage a rollback&#x2F;undo space separately from table storage.<p>Every application is a bit different, but it&#x27;s not that the PostgreSQL design is a loser in all regards. It&#x27;s not like bubble sort.

This article is incorrect IMO - the following section in particular.<p>“ In the 2000s, the conventional wisdom selected MySQL because rising tech stars like Google and Facebook were using it. Then in the 2010s, it was MongoDB because non-durable writes made it “webscale“. In the last five years, PostgreSQL has become the Internet’s darling DBMS. And for good reasons! It’s dependable, feature-rich, extensible, and well-suited for most operational workloads.”<p>Smart engineers were choosing postgres not because of the logical fallacy of popularum, but for the following reasons:<p>Data safety - not MyIsam, ACID, Similarity to Oracle, MVCC, SQL standards adherence, Postgres team, Helpful awesome community, Data types, High performance, BSD flexibility<p>Above are the reasons I selected Postgres while at ATT early 2000s and our Oracle DBA found it a very easy transition. While Mysql went through rough transitions, PG has gone from strength to strength and ever improving path.<p>I think Bruce Momjian is a big part of this success; they truly have an excellent community. &lt;3

&gt; MySQL and Oracle store a compact delta between the new and current versions (think of it like a git diff).<p>Doesn&#x27;t git famously _not_ store diffs and instead follows the same storage pattern postgres uses here and stores the full new and old objects?

&gt; The need for PostgreSQL to modify all of a table’s indexes for each update has several performance implications. Obviously, this makes update queries slower because the system has to do more work.<p>You know, I was wondering something regarding this write amplification. It&#x27;s true that MySQL doesn&#x27;t need to update its indexes like that. However, MySQL replication relies on the binlog, where every change has to be written in addition to the database itself (InnoDB redo log and so on). So, it seems to me, MySQL, if used in a cluster, has a different kind of write amplification. One that PostgreSQL does not have, because it reuses its WAL for the replication.<p>In addition, on the receiving side, MySQL first writes the incoming binlog to the relay log. The relay log is then consumed by the applier threads, creating more InnoDB writes and (by default) more binlog.

This topic cannot be discussed alone without talking about disks. SSDs write 4k page at a time. Meaning if you&#x27;re going to update 1 bit, the disk will read 4k, you update the bit, and it writes back a 4k page in a new slot. So the penalty for copying varies depending on the disk type.

&gt; Oracle and MySQL do not have this problem in their MVCC implementation because their secondary indexes do not store the physical addresses of new versions. Instead, they store a logical identifier (e.g., tuple id, primary key) that the DBMS then uses to look up the current version’s physical address. Now this may make secondary index reads slower since the DBMS has to resolve a logical identifier, but these DBMS have other advantages in their MVCC implementation to reduce overhead.<p>Interesting behavior of MySQL that I have observed (~500GB database, with a schema that is more of an document oriented than relational) is that when you update single row doing SELECT id WHERE something; UPDATE what WHERE id=id is orders of magnitudes faster than UPDATE what WHERE something. I somehow suspect that this is the reason for this behavior. But well, the normal workload will not do that and this only slows down ad-hoc DML when you fix some inconsistency.

&gt; Then in the 2010s, it was MongoDB because non-durable writes made it “webscale“.<p>Off topic, it was marketing all along: <a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=15124306">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=15124306</a>

The part I hate the most is that in 2024 I still need a connection pooler (such as pgbouncer) in front of it to make it usable.

OrioleDB was supposed to tackle this problem with a new storage engine . <a href="https:&#x2F;&#x2F;github.com&#x2F;orioledb&#x2F;orioledb">https:&#x2F;&#x2F;github.com&#x2F;orioledb&#x2F;orioledb</a>

The article says the benefit of O2N is there&#x27;s no need to immediately update indexes, but then goes on to say postgres updates the indexes anyway! So is there actually any advantage to O2N at all?

How big of an issue is this really for db users who work daily with large tables that are frequently updated but still have to be fast for queries?<p>The article mentioned that there are nearly 900 different databases on the market. I am trying to build yet another one using a unique architecture I developed. It is very fast, but although it is designed for transactions; I haven&#x27;t implemented them yet.<p>I think if I spend the time and effort to do it right, this could be a real game changer (I think the architecture lends itself very well to a superior implementation); but I don&#x27;t want to waste too much time on it if people don&#x27;t really care one way or the other.

With every new PostgreSQL release we see yet more features and sugar added to the frontend, yet seemingly no meaningful improvement to the backend&#x2F;storage layer which suffers these fundamental problems.<p>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.<p>It&#x27;s absurd that in 2024, &quot;the world&#x27;s most advanced open source database&quot; doesn&#x27;t have a method of doing upgrades between major versions that doesn&#x27;t involve taking the database down.<p>Yes, logical replication exists, but it still doesn&#x27;t do DDL, so it has big caveats attached.

Loved this post! Could anyone recommend a book (or other medium) with similar content about RMBDS internals?

Don&#x27;t agree with their characterization of `pg_repack`. `VACUUM FULL` is definitely crushing, but that&#x27;s why repack exists as a faster&#x2F;lighter alternative. Anyone have a different experience?

&quot;And second, traversing the entire version chain just to find the latest version (which is what most queries want) is wasteful.&quot;<p>Is that what most queries want? I would have thought, that the latest version is part of a transaction which isn&#x27;t committed yet and hence isn&#x27;t even meant to be found by most queries (only those within the session which opened the modifying transaction). Where did I go wrong?

They mention autvacuum_vacuum_scale_factor, and its default value, but give no hint if they tried to change that. Obviously I have no access to their database, but one of piece of advice for ages has been, in situations similar to theirs where a lot of dead tuples accumulate and autovacuum is having trouble finishing, to lower this value so autovacuum runs much more often, so each run has less to do and less gets blocked.

Putting aside the license &#x2F; closed source issues with CockroachDB (CRDB) and just focus at it technically: CRDB uses MVVM too, but its storage is a key-value store. I know it uses some kind of garbage collection to remove the old versions.<p>I wonder if CRDB (or other newer designed DBs) has circumvented those issues? Or don&#x27;t we just hear from those issues as CRDB and the other newer DBs are not that widely used and mainly in the commercial space?

So, if this is such a problem, my question is — are the poor MVCC choices of Postgres enough to make the authors (or people here) recommend another RDBMS?

My problem with PG is it just doesn&#x27;t seem to help much with my situation. I want to write apps that work offline and sync data across devices using the &#x27;cloud&#x27;. I think that means Sqlite on the client, but ?? for the server. I have yet to find a good book explaining techniques for this kind of replication&#x2F;syncing.

Question: is storing full new row-tuple versions something fundamental to Postgres as a whole, or is it just a property of the default storage engine &#x2F; “table access method”?

Nicely written article. Easy to read and understand!

pretty informative. now i understand why people are often having issues with vacuum-related stuff. i like the diagrams too.

It’s really annoying to see people write that Postgres has a “primary index” and “secondary indexes”. No. That’s not what those words mean. Every index in Postgres is a secondary index.

<a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=41892830">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=41892830</a>

For most cases, MVCC sounds like over-engineering. From the problem description:<p>&gt; The goal of MVCC in a DBMS is to allow multiple queries to read and write to the database simultaneously without interfering with each other when possible.<p>How is that a problem for most use cases?<p>If there is a read query which is taking a long time, with many rows, and some of these later rows happen to be updated mid-read but the earlier rows are not... It&#x27;s not really a problem for the vast majority of application. Why is it better for all rows to be delivered out of date versus just the first half fetched being out of date? It&#x27;s not ideal in either case but it&#x27;s unavoidable that some requests can sometimes return out of date data. It seems like a tiny advantage.<p>I suspect the real need to implement MVCC arose out of the desire for databases like Postgres to implement atomic transactions as a magical black box.<p>IMO, two-phase commit is a simpler solution to this problem. It&#x27;s not possible to fully hide concurrency complexity from the user; it ends up with tradeoffs.