Re: Another idea for dealing with cmin/cmax

Ühel kenal päeval, E, 2006-10-02 kell 01:30, kirjutas Tom Lane:
> "Jim C. Nasby" <jim@nasby.net> writes:
> > ... place a limit on the number of transactions that can be live in a table
> > at once.
> 
> Urk, well maybe, but ...
> 
> > you could shrink all the visibility info to 1 byte if you
> > wanted to.
> 
> ... 256 of 'em is surely not an acceptable limit.

I have been thinking about this, and it seems that especially for OLAP
loads it would be much better to keep tuple visibility info in a
separate file, lets call it Tuple Visibility Map (TVM)

TVM would have the following benefits:

1) TVM could be uses for index-only lookups as well as heap-only
lookups, also other index lookups could be filtered against it fast
before going to heap.

2) TVM could be heavily compressed, especially for bulk loads something
like a single (xmin, xmax,cmin,cmax) tuple plus RLE-encoded list of
pointers to it will do.

3) In case TVM space is needed in in any page, it would be easy to just
throw away cmin/cmax from tuples from committed/aborted transactions.

4) First pass of VACUUM would be much faster, as it has to scan only
TVM. Pages with no expired tuples would not need to be touched. 


If we can come up with a good design for TVM, it may also be an overall
win for many kinds of OLTP queries, as it may result in less writes to
disk and almost the same amount of writing to WAL.

Maybe bitmap or btree index would be something to use as a starting
point when designing TVM.

Another idea to consider would be to merge FSM and TVM and then use them
also for keeping data in cluster order.

-- 
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me:  callto:hkrosing
Get Skype for free:  http://www.skype.com