←back to thread

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

(www.cs.cmu.edu)
366 points virtualwhys | 2 comments | 20 Oct 24 15:30 UTC | HN request time: 0.407s | source
Show context
fweimer ◴[20 Oct 24 18:48 UTC] No.41897588[source]▶
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'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/undo space separately from table storage.

Every application is a bit different, but it's not that the PostgreSQL design is a loser in all regards. It's not like bubble sort.

replies(4): >>41897732 #>>41902493 #>>41904079 #>>41905601 #
1. winternewt ◴[21 Oct 24 13:41 UTC] No.41904079[source]▶
> Every application is a bit different, but it's not that the PostgreSQL design is a loser in all regards. It's not like bubble sort.

When doing game development in the early 2000s I learned that bubble sort is not a loser in all regards. It performs well when a list is usually almost sorted. One situation when this is the case is in 3D rendering, which sorts objects by their distance from the camera. As you move the camera around or rotate it, bubble sort works very well for re-sorting the objects given the order they had in the previous frame.

To prevent bad worst-case scenarios you can count the number of comparisons that failed on the last pass and the number of passes you have performed so far, then switch to a different sort algorithm after reaching a threshold.

replies(1): >>41909394 #
2. jeltz ◴[21 Oct 24 22:52 UTC] No.41909394[source]▶
But wouldn't insertion sort be better than bubble sort in those cases?