Re: Plans for solving the VACUUM problem

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Hey, I have an idea. Can we do subtransactions as separate transactions
> > (as Tom mentioned), and put the subtransaction id's in the WAL, so they
> > an be safely committed/rolledback as a group?
>
> It's not quite that easy: all the subtransactions have to commit at
> *the same time* from the point of view of other xacts, or you have
> consistency problems. So there'd need to be more xact-commit mechanism
> than there is now. Snapshots are also interesting; we couldn't use a
> single xact ID per backend to show the open-transaction state.

OK, I have another idea about subtransactions as multiple transaction
ids.

I realize that the snapshot problem would be an issue, because now
instead of looking at your own transaction id, you have to look at
multiple transaction ids. We could do this as a List of xid's, but that
will not scale well.

My idea is for a subtransaction backend to have its own pg_log-style
memory area that shows which transactions it owns and has
committed/aborted. It can have the log start at its start xid, and can
look in pg_log and in there anytime it needs to check the visibility of
a transaction greater than its minium xid. 16k can hold 64k xids, so it
seems it should scale pretty well. (Each xid is two bits in pg_log.)

In fact, multi-query transactions are just a special case of
subtransactions, where all previous subtransactions are
committed/visible. We could use the same pg_log-style memory area for
multi-query transactions, eliminating the command counter and saving 8
bytes overhead per tuple.

Currently, the XMIN/XMAX command counters are used only by the current
transaction, and they are useless once the transaction finishes and take
up 8 bytes on disk.

So, this idea gets us subtransactions and saves 8 bytes overhead. This
reduces our per-tuple overhead from 36 to 28 bytes, a 22% reduction!

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026