Hacker News Reader

Comment by luhn | Hacker News Reader

When you update a row in a SQL database, you need to keep the old row around until the transaction is committed.

In Postgres, a new row is written, and both the new and old coexist in the table. Even after the transaction is committed, the old row stays in the table. VACUUM scans the table, finds rows that are no longer needed, and marks them as okay to overwrite.

In MySQL, the row is overwritten in the table immediately, and then the old row is temporarily written to a separate area (the UNDO Segment), so the old record is available if needed. Since old rows don't accumulate in the main table, there's no need for VACUUM.

View on HN

Actually for MySQL it depends on the storage engine, what you described is how InnoDB works.

MyRocks storage, however, only appends data, and requires regular Compaction to clean up old rows, similar to Postgres VACUUM.

Your point about MyRocks is a good one. It's also true that MyRocks takes a similar approach to Postgres when it comes to implementing the higher isolation levels. It wouldn't make sense to add InnoDB style next-key locks to MyRocks, either.

Systems that closely adhere to traditional 2PL designs such as InnoDB or DB2 end up with tight coupling between recovery, concurrency control, and storage. This has many consequences, both good and bad. It's no coincidence that Postgres can support quite a variety of index access methods, including support for transactional full text search. You get the transactional stuff "as standard" with the Postgres approach to versioned storage. There is no need to bake concurrency control into each and every index access method.

Yeah, I should have specified that. Thanks for mentioning it.

Thank you for your answer, this makes sense! I guess that there must be some tradeoff, right? What I mean is that while in MySQL you don't need to vacuum, perhaps updates would be slightly slower (due to the need to write into the UNDO segment)?

I think this might provide more context which you are looking for (and your suspicion seems spot on!):

> This means more writes to do on updates and makes access to old row versions quite a lot slower, but gets rid of the need for asynchronous vacuum and means you don't have table bloat issues. Instead you can have huge rollback segments or run out of space for rollback.

https://stackoverflow.com/questions/25153532/why-is-it-a-vac...

Edit: Just wanted to explicitly call out that the Robert Haas article linked in the answer is excellent (I am still reading my way through it).

There are plans for postgres to move to a more similar strategy to MySQL, at least in some cases:

http://amitkapila16.blogspot.com/2018/03/zheap-storage-engin...

Recovery in Postgres is simplified and sped up considerably by the approach taken to MVCC. Same is true with transaction rollback. SQL Server users (on recent SQL Server versions) can optionally get a similar trade-off by enabling constant time recovery (CTR), which is called Accelerated Database recovery (ADR) in the MS marketing material. See the paper:

https://www.microsoft.com/en-us/research/uploads/prod/2019/0...

SQL Server has something called "background cleanup" when this feature is enabled, which takes place in a separate process called "the cleanup process" - just like VACUUM. The paper itself explicitly acknowledges the similarity. All of the advantages the paper claims for CTR/ADR are also advantages for Postgres.

It's also true that both traditional SQL Server (SS with ADR/CTR disabled) and MySQL have background cleanup processes of their own for indexes, but that is a little different. It's about asynchronously reclaiming space for index entries that were already delete-marked.

Conceptually you still need to clean up the old row versions, regardless of where they're located. InnoDB has purge threads to clean up undo log entries that are older than the oldest active transaction. If too many of them accumulate, such as due to a long-running transaction preventing purge progress, it increasingly harms performance. This can be a major problem in MySQL for workloads which mix a high-volume of quick UPDATE and/or DELETE operations concurrently with other slower/longer transactions.

“In MySQL, the row is overwritten in the table immediately, and then the old row is temporarily written to a separate area (the UNDO Segment)”

I would hope and expect MySQL does that in reverse order, as “yeah, we have that row in memory” isn’t a good answer to “are you sure you can roll back that change if needed?”

For future reference, I have found this excellent post about the same topic https://rhaas.blogspot.com/2011/02/mysql-vs-postgresql-part-...

Reply on news.ycombinator.com