Re: ARCHIVE TABLES (was: possible TODO: read-only - Mailing list pgsql-hackers

From Hannu Krosing
Subject Re: ARCHIVE TABLES (was: possible TODO: read-only
Date
Msg-id 1115115005.4852.18.camel@fuji.krosing.net
Whole thread Raw
In response to Re: ARCHIVE TABLES (was: possible TODO: read-only tables, select from indexes only.)  (Jochem van Dieten <jochemd@gmail.com>)
List pgsql-hackers
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>



pgsql-hackers by date:

Previous
From: Hannu Krosing
Date:
Subject: Re: ARCHIVE TABLES (was: possible TODO: read-only
Next
From: Hannu Krosing
Date:
Subject: Re: ARCHIVE TABLES (was: possible TODO: read-only