4 ms·

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, perha

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)?

gshulegaard 6y ago
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-vacuum-not-needed-with-mysql-compared-to-the-postgresql 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).
nicoburns 6y ago
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-engine-to-provide-better.html?m=1 http://amitkapila16.blogspot.com/2018/03/zheap-storage-engin...
petergeoghegan 6y ago
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/06/p700-antonopoulos.pdf 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.
evanelias 6y ago
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.

gshulegaard 6y ago

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-vacuum-not-needed-with-mysql-compared-to-the-postgresql 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).

nicoburns 6y ago

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-engine-to-provide-better.html?m=1 http://amitkapila16.blogspot.com/2018/03/zheap-storage-engin...

petergeoghegan 6y ago

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/06/p700-antonopoulos.pdf 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.

evanelias 6y ago

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.