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

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