From: Ron <ronljohnsonjr@gmail.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Synthetic keys and index fillfactor
Date: Thu, 12 Jan 2023 17:43:05 -0600
Message-ID: <4efb3483-dc9d-66ec-1190-25b58d5886cb@gmail.com> (raw)
In-Reply-To: <CAKFQuwanbq3T1xiysk=kP9Oy5qtNyXdFG8asPxttBq_fJg==8w@mail.gmail.com>
References: <d50cc687-d903-3312-cee2-4f4fdc864b0a@gmail.com>
<CAKFQuwanbq3T1xiysk=kP9Oy5qtNyXdFG8asPxttBq_fJg==8w@mail.gmail.com>
On 1/12/23 16:54, David G. Johnston wrote:
> On Thu, Jan 12, 2023 at 3:45 PM Ron <ronljohnsonjr@gmail.com> wrote:
>
>
> (This mostly pertains to recreating a PK on an existing table.)
>
> Is there any reason to have the PK index on an ever-increasing field (for
> example SERIAL, sequence or timestamp fed by clock_timestamp() at time
> zone
> 'UTC') be anything but fillfactor=100?
>
> New records will always be added to the "lower right hand corner" of the
> tree, so having 20% empty space in the rest of the tree would just waste
> space (mainly buffers, but disk space could even start to add up on
> Very Big
> Tables).
>
>
> Yes, at least that is what I gather from the advice on the CREATE INDEX page.
>
> https://www.postgresql.org/docs/15/sql-createindex.html
>
"even a few updates or inserts will cause a sudden flood of page splits"
shouldn't matter for ever-increasing PK values, since you're always filling
that bottom right corner anyway.
This might be a killer if the applications update many indexed fields:
https://www.postgresql.org/docs/15/btree-implementation.html#BTREE-DELETION
"Changing the value of only one column covered by one index during
an|UPDATE|/always/necessitates a new set of index tuples — one for/each and
every/index on the table."
--
Born in Arizona, moved to Babylonia.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, david.g.johnston@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Synthetic keys and index fillfactor
In-Reply-To: <4efb3483-dc9d-66ec-1190-25b58d5886cb@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox