On 07/26/2016 03:07 PM, Tom Lane wrote: > Josh Berkus <j...@agliodbs.com> writes:
>> 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. Not in practice. Don't forget that you also have bloat of the indexes as well. I encountered multiple cases of this particular failure case, and often bloat ended up at something like 100X of the clean table/index size, with no stable size (that is, it always kept growing). This was the original impetus for wanting REINDEX CONCURRENTLY, but really that's kind of a workaround. (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. You'd need a dedicated slave for the pg_dump, otherwise you'd hit query cancel. > Or in short, this seems like an annoyance, not a time-for-a-new-database > kind of problem. It's considerably more than an annoyance for the people who suffer from it; for some databases I dealt with, this one issue was responsible for 80% of administrative overhead (cron jobs, reindexing, timeouts ...). But no, it's not a database-switcher *by itself*. But is is a chronic, and serious, problem. I don't have even a suggestion of a real solution for it without breaking something else, though. -- -- Josh Berkus Red Hat OSAS (any opinions are my own) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
