3 ms·

It's not that simple... The whole point of keeping dead rows around is so the mvcc snapshot can show a consistent view of the database for any user within a tra

It's not that simple... The whole point of keeping dead rows around is so the mvcc snapshot can show a consistent view of the database for any user within a transaction. If you instantly cleaned up those dead rows, other users suddenly don't have a consistent view of data, or have data disappear entirely on them. The other way this is handled is to move dead rows to an undo log, and the min heap only contains the current version of any specific row, and a pointer to where to find the dead rows in the undo log. The above approach keeps your main heap from bloating when updates and deletes happen, but it causes transactions which reference the old version of the rows to be slower, as well as slowing down rollbacks. This is exactly what the zHeap project is doing along with the pluggable storage API.

dap 8y ago
> The above approach keeps your main heap from bloating when updates and deletes happen, but it causes transactions which reference the old version of the rows to be slower, as well as slowing down rollbacks. I think that's consistent the parent's goal (and mine) of allowing ongoing requests to be slower in exchange for not having to pay a major cost later to vacuum.
- Tostino 8y ago
  Yup, I meant for it to be consistent with the OPs concerns. I just wanted to point out that "just paying for it up front" also has other trade-offs. For a lot of use cases, the way PG went ends up better for performance than using an undo log (after all the performance opimizations PG has put in place like freezing pages, etc). And for some use cases an undo log is by far better, and more consistent.
  - dap 8y ago
    I can see that. I've really only worked with a few workloads where this behavior was quite bad. (In particular, the out-of-the-box policy to vacuum when the dead tuple fraction reaches 20% means that for an INSERT-mostly workload with a bit of UPDATE/DELETE, the vacuums get further and further apart and take longer and longer.) In the worst one I've worked on, the effects take several days or weeks to show up, but they degrade system throughput by 50-80% (!) for days or weeks on end. The experience makes me wonder if the database is designed primarily using short-term, modest-scale workloads (which are much easier to test) and not behavior that only shows up after several weeks of a continuous, heavy workload. It obviously works for a lot of users.
etaioinshrdlu 8y ago
The trouble is that old rows can stick around many hours after any transaction could ever see them. It's good that it's being addressed.

dap 8y ago

> The above approach keeps your main heap from bloating when updates and deletes happen, but it causes transactions which reference the old version of the rows to be slower, as well as slowing down rollbacks. I think that's consistent the parent's goal (and mine) of allowing ongoing requests to be slower in exchange for not having to pay a major cost later to vacuum.

Tostino 8y ago
Yup, I meant for it to be consistent with the OPs concerns. I just wanted to point out that "just paying for it up front" also has other trade-offs. For a lot of use cases, the way PG went ends up better for performance than using an undo log (after all the performance opimizations PG has put in place like freezing pages, etc). And for some use cases an undo log is by far better, and more consistent.
- dap 8y ago
  I can see that. I've really only worked with a few workloads where this behavior was quite bad. (In particular, the out-of-the-box policy to vacuum when the dead tuple fraction reaches 20% means that for an INSERT-mostly workload with a bit of UPDATE/DELETE, the vacuums get further and further apart and take longer and longer.) In the worst one I've worked on, the effects take several days or weeks to show up, but they degrade system throughput by 50-80% (!) for days or weeks on end. The experience makes me wonder if the database is designed primarily using short-term, modest-scale workloads (which are much easier to test) and not behavior that only shows up after several weeks of a continuous, heavy workload. It obviously works for a lot of users.

etaioinshrdlu 8y ago

The trouble is that old rows can stick around many hours after any transaction could ever see them. It's good that it's being addressed.