Re: vacuum, performance, and MVCC

Christopher Browne <cbbrowne(at)acm(dot)org> writes:
> After a long battle with technology, pgsql(at)mohawksoft(dot)com ("Mark Woodward"), an earthling, wrote:
>> Not true. Oracle does not seem to exhibit this problem.

> Oracle suffers a problem in this regard that PostgreSQL doesn't; in
> Oracle, rollbacks are quite expensive, as "recovery" requires doing
> extra work that PostgreSQL doesn't do.

The Oracle design has got other drawbacks: if you need to access a row
version other than than the very latest, you need to go searching in the
rollback segments for it. This is slow (no index help) and creates
significant amounts of contention (since lots of processes are competing
to touch the rollback segments). Plus there's the old bugaboo that
long-running transactions require indefinite amounts of rollback space,
and Oracle is apparently unable to enlarge that space on-the-fly.
(This last seems like a surmountable problem, but maybe there is some
non-obvious reason why it's hard.)

Basically there's no free lunch: if you want the benefits of MVCC it's
going to cost you somewhere. In the Postgres design you pay by having
to do VACUUM pretty often for heavily-updated tables. I don't think
that decision is fundamentally wrong --- the attractive thing about it
is that the overhead is pushed out of the foreground query-processing
code paths. We still have lots of work to do in making autovacuum
smarter, avoiding vacuuming parts of relations that have not changed,
and so on. But I have no desire to go over to an Oracle-style solution
instead. We can't beat them by trying to be like them, and we run no
small risk of falling foul of some of their patents if we do.

regards, tom lane