ARCHIVE TABLES (was: possible TODO: read-only tables, select from indexes only.) - Mailing list pgsql-hackers

From Hannu Krosing
Subject ARCHIVE TABLES (was: possible TODO: read-only tables, select from indexes only.)
Date
Msg-id 1115052791.4997.40.camel@fuji.krosing.net
Whole thread Raw
Responses Re: ARCHIVE TABLES (was: possible TODO: read-only tables, select from indexes only.)  ("Jim C. Nasby" <decibel@decibel.org>)
List pgsql-hackers
Third repeat post. Sorry if previous ones suddenly pop up too.


ARCHIVE TABLES

this is a further development ofthe "read-only tables" proposal,
WHich answers  Tom's question - 'how such a tables comes to exists'
and inspired by someones (can't find the mail, sorry) suggestion 
to think of them as archive tables, not R/O tables.


So ARCHIVE TABLE is is a table with the following properties

1) The ONLY operations allowed on it are APPEND (i.e. INSERT or COPY)  and SELECT.  Forbidden ops are UPDATE and
DELETE.TRUNCATE should probably  allowed too.
 

2) data in heap is dense, each new APPEND adds a set of one or more  tuples with continuous tids, all bigger than
existingany existing  tid.
 
  no system colums are stored in each tuple, WITH OIDS is not  supported for archive tables.

3) tuple visibility is determined by tid of last tuple of last  successfully committed APPEND .    Any tuple with tid >
LastValidTidis not visible. 
 
  The LastValidTid is stored for each archive table.

4) 0nly one session can APPEND to an archive table at any one time.

5) if the transaction doing the insert is rolled back, the table is  locked for further APPENDS until indexes are
cleanedup (by VACUUM   or REINDEX) and the end of heap is truncated to its place before   the failed transaction.
Analtervative behaviour is to do the cleanup immediately on  rollback, but even this has to lock the table until its
done,  to be crash-proof.
 

6) Vacuum on an archive table should 
- examine only tuples with tid > LastValidTid - clean up their index entries- modyfi last valid page and truncate table
toits old length   corresponding to LastValidTid
 


(4, 5 & 6 are needed for 3 to work reliably in case of rollbacked
transactions.)

7) everything else (constraints, triggers, indexes, rules, statistics)  should be the same as for normal tables.
Eveninheriting an ARCHIVE table from ordinary table should be  allowed.
 


This kind of setup allows the following features
- index-only scans for cases where all columns needed are in index.   visibility can be determined from tid without
consultingthe heap.
 
- smaller table sizes due to not storing visibility info with each  tuple

which are often desirable for BusinessIntelligence/DataWarehousing
databases and other systems with huge fact tables.



I don't think that Tom's concern about pervasiveness of TupleHeader is
unsurmountable.

I hope that the only <stupid hopeful grin> thing needing to change is 
visibility checks when fetching the tuples from heap or index, from that
point on it should be possible to handle them as if the data is coming
from a view.

Also I hope that index structure does not have to change at all, only a
new access methods should be added, namely 
* archive-indexscan (replaces ordinary index-scan)
* archive-index-only-scan (new)
* archive-seqscan (replaces ordinary seqscan)

and planner/executor must be teached to use these.

-- 
Hannu Krosing <hannu@skype.net>



pgsql-hackers by date:

Previous
From: Hannu Krosing
Date:
Subject: Re: How to make lazy VACUUM of one table run in several
Next
From: "Merlin Moncure"
Date:
Subject: Re: pg_locks needs a facelift