3 ms·
> 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
by 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 agoYup, 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 agoI 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.