Re: Plans for solving the VACUUM problem - Mailing list pgsql-hackers

"Vadim Mikheev" <vmikheev@sectorbase.com> writes:
>> 1. Space reclamation via UNDO doesn't excite me a whole lot, if we can
>> make lightweight VACUUM work well.

> Sorry, but I'm going to consider background vacuum as temporary solution
> only. As I've already pointed, original PG authors finally became
> disillusioned with the same approach.

How could they become disillusioned with it, when they never tried it?
I know of no evidence that any version of PG has had backgroundable
(non-blocking-to-other-transactions) VACUUM, still less within-relation
space recycling.  They may have become disillusioned with the form of
VACUUM that they actually had (ie, the same one we've inherited) --- but
please don't call that "the same approach" I'm proposing.

Certainly, doing VACUUM this way is an experiment that may fail, or may
require further work before it really works well.  But I'd appreciate it
if you wouldn't prejudge the results of the experiment.

>> Would much rather have the space reclamation happen in
>> background.)

> Understandable, but why other transactions should read dirty data again
> and again waiting for background vacuum? I think aborted transaction
> should take some responsibility for mess made by them -:)

They might read it again and again before the failed xact gets around to
removing the data, too.  You cannot rely on UNDO for correctness; at
most it can be a speed/space optimization.  I see no reason to assume
that it's a more effective optimization than a background vacuum
process.

>> 3. Reusing xact IDs would be nice, but there's an answer with a lot less
>> impact on the system: go to 8-byte xact IDs.

> +8 bytes in tuple header is not so tiny thing.

Agreed, but the people who need 8-byte IDs are not running small
installations.  I think they'd sooner pay a little more in disk space
than risk costs in performance or reliability.

>> Another thought: do we need WAL UNDO at all to implement savepoints?
>> Is there some way we could do them like nested transactions, wherein
>> each savepoint-to-savepoint segment is given its own transaction number?

> Implicit savepoints wouldn't be possible - this is very convenient
> feature I've found in Oracle.

Why not?  Seems to me that establishing implicit savepoints is just a
user-interface issue; you can do it, or not do it, regardless of the
underlying mechanism.

>> Implementing UNDO without creating lots of performance issues looks
>> a lot harder.

> What *performance* issues?!
> The only issue is additional disk requirements.

Not so.  UNDO does failed-transaction cleanup work in the interactive
backends, where it necessarily delays clients who might otherwise be
issuing their next command.  A VACUUM-based approach does the cleanup
work in the background.  Same work, more or less, but it's not in the
clients' critical path.

BTW, UNDO for failed transactions alone will not eliminate the need for
VACUUM.  Will you also make successful transactions go back and
physically remove the tuples they deleted?
        regards, tom lane