Re: Minor TODO list changes

In my data warehousing situation, I'd like to be able to specify that
the indexes be as compact as possible (fillfactor = 100%) in order to
hit as few index pages as necessary.

For summary tables there will not be any more inserts or deletions so
the index will not change either. In that case, there's no point to
leaving any extra room for page-splitting.

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.

Darren

-----Original Message-----
From: Bruce Momjian [mailto:pgman(at)candle(dot)pha(dot)pa(dot)us]
Sent: Thursday, November 04, 2004 12:19 PM
To: Simon Riggs
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] Minor TODO list changes

Simon Riggs wrote:
> On Thu, 2004-11-04 at 16:51, Bruce Momjian wrote:
> > OK, I updated all your items.
>
> Thanks
>
> > I removed fillfactor because I thought I was the only one who
> > thought it was valuable and as I remember it was mostly useful for
> > ISAM, which we don't support. Can you think of a use for a non-100%

> > fillfactor?
> >
>
> I was under the impression the factor was 67% for data loaded on the
> leading-edge of an index, and 50% for other INSERTs.
> (backend/access/nbtree/nbtinsert.c)
>
> Not sure, without checking, what CREATE INDEX and COPY do, but I'm
> guessing it is similar?
>
> Other RDBMS use a higher leading-edge/standard fill factor.
>
> There are situations where I'd want to set it at 90%, or even 100%. If

> I know the update rate is likely to be zero, then I'd like my indexes
> to fit in 10-30% less memory and disk, please.
>
> Or am I missing something?

Oh, good point. I was thinking of just the leaf pages which I think are
100% filled.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania
19073

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

> 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.

On Thu, 2004-11-04 at 17:59, Darren King wrote:
> In my data warehousing situation, I'd like to be able to specify that
> the indexes be as compact as possible (fillfactor = 100%) in order to
> hit as few index pages as necessary.
>

Yes, that's my intent.

> 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.
>

Sounds cool. Good idea,

--
Best Regards, Simon Riggs

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