* Hannu Krosing (hkros...@gmail.com) wrote: > On 07/27/2016 12:07 AM, Tom Lane wrote: > > > >> 4. Now, update that small table 500 times per second. > >> That's a recipe for runaway table bloat; VACUUM can't do much because > >> there's always some minutes-old transaction hanging around (and SNAPSHOT > >> TOO OLD doesn't really help, we're talking about minutes here), and > >> because of all of the indexes HOT isn't effective. > > Hm, I'm not following why this is a disaster. OK, you have circa 100% > > turnover of the table in the lifespan of the slower transactions, but I'd > > still expect vacuuming to be able to hold the bloat to some small integer > > multiple of the minimum possible table size. (And if the table is small, > > that's still small.) I suppose really long transactions (pg_dump?) could > > be pretty disastrous, but there are ways around that, like doing pg_dump > > on a slave. > Is there any theoretical obstacle which would make it impossible to > teach VACUUM not to hold back the whole vacuum horizon, but just > to leave a single transaction alone in case of a long-running > REPEATABLE READ transaction ?
I've looked into this a couple of times and I believe it's possible to calculate what records have to remain available for the long-running transaction, but it's far from trivial. I do think that's a direction which we really need to go in, however. Having a single horizon which is dictated by the oldest running transaction isn't a tenable solution in environments with a lot of churn. Thanks! Stephen
signature.asc
Description: Digital signature
