Reviving Time Travel (was Re: 'TID index')

On P, 2004-09-26 at 09:17, Tom Lane wrote:
> "Ross J. Reedstrom" <reedstrm(at)rice(dot)edu> writes:
> > ... So, all this append-only writing leads to files with lots of dead
> > tuples, so the vacuum command was added to reclaim space.
>
> Actually, I believe the original Postgres design envisioned that no
> tuple would ever be permanently deleted: the idea was that you would
> always be able to query the database state as of past times as well
> as the present instant. Stonebraker intended to use the WORM drive as
> the repository for dead tuples that might be needed to answer such
> historical queries. The "vacuum cleaner" was originally a background
> process that pushed dead tuples from magnetic disk to WORM storage.

We are now getting back to the point where the "background process" part
is true again - how hard would it be to modify vacuum to write recalimed
tuples to somewhere (network pipe, WORM drive, other DB).

It seems that in addition to writing deleted tuples out to history DB
and making create and delete transaction ids explicitly visible (and do
something(tm) about the transaction id wraparound while at it), the only
thing left to do is some kind of transaction time log - and voila! we
have the original Time Travel feature back - a great feature for
resolving both "audit trail" and "clumsy dba" problems.

The modern WORM drive equivalent is an IDE(-RAID) disk with its very
tape-like access profile (3+ hours to write full 300GB disk, random
access order(s) of magnitude slower than sequential);

So if someone is looking for a project, this seems to be something that
is both theoretically possible and also theoretically useful ;)

----------------
Hannu