Re: Minor TODO list changes

You are correct.

I would envision being able to alter a table "read-write" at any point.
If the index(es) on the table are completely filled from being created
in read-only mode, then the affected pages should be split with the
default fillfactor when/if a row is inserted or updated. Altering the
table back to read-only would simple leave the index as is with a few
pages not filled, but still fully functional.

The insert-only index is intriguing as well though.

Darren

-----Original Message-----
From: Simon Riggs [mailto:simon(at)2ndquadrant(dot)com]
Sent: Thursday, November 04, 2004 1:50 PM
To: Rod Taylor
Cc: Darren King; Bruce Momjian; PostgreSQL Development
Subject: Re: [HACKERS] Minor TODO list changes

On Thu, 2004-11-04 at 18:15, Rod Taylor wrote:
> > At some point it would also be nice to be able to mark tables as
> > read-only and then any indexes created on that table after that
> > would have a fillfactor of 100%. Then I'd be able to load the
> > table, alter it to be read-only, then add the appropriate indexes
> > that are automatically compacted.
>
> If it's read-only, you might as well remove a number of the internal
> fields used for visibility as well.

Yes, should be able to save 16 bytes/row for an INSERT only table that
would still allow multiple simultaneous COPY jobs against it, with no
more than 1 statement per transaction. I'd like to create an additional
tuple layout using the tuple version bits, so you'd be able to set a
flag at CREATE TABLE time to use that as an optional alternative from
the standard one. UPDATEs and DELETEs would be permanently disallowed
against such tables, just as if privileges had not been granted.
TRUNCATE would still work, however.

Call it something like NOMODIFY? You could then alter VACUUM to skip
such tables, so you'd be able to do a VACUUM database without scanning
all of the largest tables in your system.

Darren's first idea would then be interpreted as automatically setting
FILLFACTOR=100 on indexes of NOMODIFY tables.

Darren's second idea was dynamic: i.e. an ALTER TABLE READONLY after
loading, rather than using a different tuple layout, which would need to
be done before loading, probably at CREATE TABLE time.

Darren's second idea of READONLY tables is related, but not necessarily
the same as the NOMODIFY concept that Rod brings up.

--
Best Regards, Simon Riggs