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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
