On 07/26/2016 03:07 PM, Tom Lane wrote:
> Josh Berkus <josh@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)
