David Scott <davids@apptechsys.com> writes:
> Since I am sure everyone is tired of the intro by now, I'll get to the
> questions:
...
> Is there any way to modify PostgreSQL to allow index lookups without heap
> validation that doesn't involve re-writing the MVCC implementation of
> keeping dead rows on the live table? Is the additional overhead of keeping
> full tuple visibility information inside of the index so odious to the
> Postgres community as to prevent a patch with this solution from being
> applied back to the head?
The consequences of full visibility information in indexes would indeed be
pretty odious.
However the general gist the conversation led last time it came up had what
sounded like a feasible compromise:
Keep a very compact bitmap outside the table (not attached to any single
index) with one bit per tuple indicating whether the tuple was known to be
visible to every transaction. The hope being this bitmap would be small enough
to sit in memory pretty much permanently. Even if not then it should be much
smaller than the table and impose a pretty small i/o overhead.
If most of the records in the table are old records that are visible to every
transaction then the index scan would be able to avoid reading in pages of the
heap. "Most" would have to be a pretty big percentage though since even a
single tuple with unknown visibility would have to be read in.
The bitmap would be useful for vacuum too. Any page that contained only tuples
with known visibility could be skipped. That would mean running vacuum for
extremely large tables that have only moderate activity wouldn't have to scan
all those static pages. (There could be an issue with people whose FSM can't
track all the free space but expect it to be found on subsequent vacuums, but
details details.)
I wonder if the bitmap can actually be one bit per page actually. A single
update has to set the bit for the tuple, and that will make the whole page
have to be read in for both vacuum and index lookups. Only a vacuum will be
able to verify that all the tuples in the page are known-visible and index
entries have been cleaned up, and the vacuum is going to be operating on the
whole page anyways. A one-bit-per-page bitmap will easily fit in RAM even for
very large tables.
--
greg