Re: Data archiving/warehousing idea

On 2/1/07, Chris Dunlop wrote:
> In maillist.postgres.dev, you wrote:
>> On Thu, 1 Feb 2007, Chris Dunlop wrote:
>>> The main idea is that, there might be space utilisation and
>>> performance advantages if postgres had "hard" read-only
>>> tables, i.e. tables which were guaranteed (by postgres) to
>>> never have their data changed (insert/update/delete).
>>>
>>> This could potentially save disk space by allowing "book
>>> keeping" elements in the page/tuple headers to be removed,
>>> e.g. visibility information etc. Also, some indexes could
>>> potentially be packed tighter if we know the data will never
>>> change (of course this is already available using the
>>> fillfactor control).
>>
>> Well, there is also CPU overhead doing MVCC but there are a
>> few fundamental problems that must be overcome. The most
>> significant is that no useful table is always read only,
>> otherwise you could never load it.
>
> Yes, that's why I was proposing ALTER TABLE... SET ARCHIVE
> rather than CREATE TABLE... ARCHIVE. (Although, for
> consistency, perhaps the CREATE TABLE would be allowed, it's
> just that you couldn't load anything into it until you did a
> ALTER TABLE... DROP ARCHIVE.)

If you want to squeeze the most out of it, CLUSTER would need to be
able to set the archive option too. (Otherwise you first rewrite the
table when you cluster it, and then rewrite it again when you archive
it.)

> Rather than writing in-place, perhaps the SET ARCHIVE would
> create a on-disk copy of the table.

Just like CLUSTER does now: create an on-disk copy first and swap the
relfilenodes of the files and flush the relcache.

> Of course this would demand
> you have twice the disk space available which may be prohibitive
> in a large warehouse. On the other hand, I'm not sure if you
> would have a single humongous table that you'd SET ARCHIVE on,
> you might be as likely to archive on a weekly or yearly or
> whatever is manageable basis, along the lines of:
>
> begin;
> select * into foo_2006 from foo
> where date_trunc('year', timestamp) = '2006-01-01';
> delete from foo
> where date_trunc('year', timestamp) = '2006-01-01';
> alter table foo_2006 set archive;
> alter table foo_2006 inherit foo;
> commit;

Ideally you let most of it run outside a transaction:

create table foo_2006 ();
insert into foo_2006 SELECT * from foo where ....;
cluster foo_2006 on ... ARCHIVE;
begin;
delete from foo where PK in select PK from foo_2006;
alter table foo_2006 inherit foo;
commit;

> You're talking about the "no-WAL" concept? Not quite the same
> thing I think, but perhaps complimentary to the ARCHIVE idea: I
> wouldn't expect an ARCHIVE table to need to generate any WAL
> entries as it would be read only.

The WAL gains come automatically when data isn't changed. But there
are additional advantages that can be obtained with archive tables:
- no need to vacuum them, not even for XID rollover (but you do need
to vacuum their entries in the catalogs)
- index entries are always valid so no need to check the heap (unless
the index is lossy)
- you can force the fillfactor to 100% regardless of the setting
- more agressive compression of toast tables

Jochem