Re: ARCHIVE TABLES (was: possible TODO: read-only

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(at)skype(dot)net>