Thread: ARCHIVE TABLES (was: possible TODO: read-only tables, select from indexes only.)

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>

Out of curiosity, what would be required to allow deletes (but not
updates)? My thinking is that you'd want *some* way to be able to prune
data. Since you won't want to store an entire XID/CID for the delete, I
think it would be acceptable to keep a table of XID/CID values for
deletes and just store a pointer to that table in the tuple header. This
means you would be limited (perhaps severely) in the number of deletes
you could issue between vacuums, but for this instance that seems
perfectly reasonable. It might be worth using this same technique for
inserts as well. If the only inserting into the table is from some
nightly bulk process, you certainly don't need to store 4 (or is it 8)
bytes of inserting transaction data with each tuple.

Also, how does this allow for index scans without touching the heap?
AFAIK when a tuple is inserted but not committed it is already in the
index.
-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On 5/2/05, Jim C. Nasby wrote:
> Out of curiosity, what would be required to allow deletes (but not
> updates)?

The same as updates (because updates are essentially a delete + insert).


> My thinking is that you'd want *some* way to be able to prune
> data. Since you won't want to store an entire XID/CID for the delete, I
> think it would be acceptable to keep a table of XID/CID values for
> deletes and just store a pointer to that table in the tuple header. This
> means you would be limited (perhaps severely) in the number of deletes
> you could issue between vacuums, but for this instance that seems
> perfectly reasonable.

Since the (pointer to) the visibility information is only stored in
the heap, not the index, how are you going to do index-only scans?


> Also, how does this allow for index scans without touching the heap?
> AFAIK when a tuple is inserted but not committed it is already in the
> index.

Hannu's design has a table-wide MaxVisibleTID variable. Since the
index entry contains the TID it is easy to compare them.
I don't think index-only scans are possible with your design. You
could use the same hack and add a table-wide MinVisibleTID variable to
drop tuples of the other end of the table.


I think the advantages of both the ability to append to and delete
from an archived table are largely negated with the design for table
partitioning as emerging from the work of Simon e.a. on the bizgres
list.
The advantage of being able to append would be negated by having a
partitioned table where you archive certain partitions and all
attempts to subsequently append to those partitions are redirected to
the catch-all partition. For the delete case that would fit the most
common usage pattern of an archive to periodically drop off historic
data, is to simply drop an entire partition.

Within such a partitioning framework a "CLUSTER partitionname ARCHIVE"
operation that truly sets the data in that partition in stone might
not be a totally outrageous concept :)

Jochem

On E, 2005-05-02 at 15:59 -0500, Jim C. Nasby wrote:
> Out of curiosity, what would be required to allow deletes (but not
> updates)?

Deletes *are* the problem (and update is internally a delete+insert).
Allowing deletes means no more selects from indexes only as tid can't be
used for determining visibility.

>  My thinking is that you'd want *some* way to be able to prune
> data. Since you won't want to store an entire XID/CID for the delete, I
> think it would be acceptable to keep a table of XID/CID values for
> deletes and just store a pointer to that table in the tuple header. This
> means you would be limited (perhaps severely) in the number of deletes
> you could issue between vacuums, but for this instance that seems
> perfectly reasonable. It might be worth using this same technique for
> inserts as well. If the only inserting into the table is from some
> nightly bulk process, you certainly don't need to store 4 (or is it 8)
> bytes of inserting transaction data with each tuple.

The idea was using archive tables in partitioned tables if you need to
prune data.

> Also, how does this allow for index scans without touching the heap?
> AFAIK when a tuple is inserted but not committed it is already in the
> index.

You can check if tid (tuple id) is bigger than last valid (committed)
tuple id in the table.

-- 
Hannu Krosing <hannu@skype.net>

On E, 2005-05-02 at 23:59 +0200, Jochem van Dieten wrote:
> On 5/2/05, Jim C. Nasby wrote:


> I think the advantages of both the ability to append to and delete
> from an archived table are largely negated with the design for table
> partitioning as emerging from the work of Simon e.a. on the bizgres
> list.

True for delete, but not for append (see below)

> The advantage of being able to append would be negated by having a
> partitioned table where you archive certain partitions and all
> attempts to subsequently append to those partitions are redirected to
> the catch-all partition. 

If you can append to the table, it will get the advantage of index-only
scans and space-saving headerless tuples from the start, not just after
freezing it. 

Of course we could add a feature of 'closing' the archive table for
appends at some point and then omit the tid checksm but I think the tid
checks are so cheap that the added need to check if the tid comparison
is needed will make this useless.

> For the delete case that would fit the most
> common usage pattern of an archive to periodically drop off historic
> data, is to simply drop an entire partition.
> 
> Within such a partitioning framework a "CLUSTER partitionname ARCHIVE"
> operation that truly sets the data in that partition in stone might
> not be a totally outrageous concept :)

The main use of archive tables would be for use as partitions in a
partitioned table. But some big static fact tables could also benefit.

The advantage of having the ability to append to such tables is mainly
performance/storage one - you can create a large table with no per-tuple
visibility info (no stored tuple header) from the start, without the
hassle of reclustering it.

Headerless tuples were not an original design target, but they do seem 
desirable for huge but narrow tables.

-- 
Hannu Krosing <hannu@skype.net>

On E, 2005-05-02 at 15:59 -0500, Jim C. Nasby wrote:
> Out of curiosity, what would be required to allow deletes (but not
> updates)?

Perhaps a "CLUSTER indexname ON tablename WHERE xxx" could be used for
that. 

That is a CLUSTER command that leaves out the tuples which do not
satisfy "xxx".

-- 
Hannu Krosing <hannu@skype.net>