Subject: Re: Index Partition Size Double of its Table Partition?

From: Peter Geoghegan <[email-masked]>
Date: Wed, 30 Oct 2024 12:22:37 -0400

On Wed, Oct 30, 2024 at 12:08 PM Don Seiler <[email-masked]> wrote:
> Why would last month's index be so much smaller?

Because the split heuristics worked as designed there. That's the
theory, at least.

> Both indexes were created using CONCURRENTLY, as each was created during its month when we started testing. The September index was created toward the end of the month (Sep 26), whereas the October one was created Oct 1.  Both table partitions are getting regularly autovacuum/autoanalyze work.

If a substantial amount of the index was written by CREATE INDEX (and
not by retail inserts) then my theory is unlikely to be correct. It
could just be that you managed to absorb most inserts in one
partition, but not in the other. That's probably possible when there
are only relatively small differences in the number of inserts that
need to use of the space left behind by fillfactor in each case. In
general page splits tend to come in distinct "waves" after CREATE
INDEX is run.

-- 
Peter Geoghegan

---

From: Don Seiler <[email-masked]>
Date: Wed, 30 Oct 2024 11:28:54 -0500

> general page splits tend to come in distinct "waves" after CREATE

What do you mean by "absorb" the inserts?

It sounds like the answer will be "No", but: Would rebuilding the index
after the month-end (when inserts have stopped on this partition) change
anything?

Don.
-- 
Don Seiler
www.seiler.us

---

From: David Mullineux <[email-masked]>
Date: Wed, 30 Oct 2024 21:59:07 +0000

Are you able to cluster the table ? The idea is that rows ordered in the
same way  as the index might reduce it's size ?

---

From: Don Seiler <[email-masked]>
Date: Thu, 31 Oct 2024 11:01:55 -0500

> same way  as the index might reduce it's size ?
I'm not sure on this. There are other indexes on these table partitions as
well.

Another bit of useful info that I should have shared immediately is that
this is a monthly partitioned table, going back years. We don't drop old
partitions (yet) on this one. For now we've only added this index to a few
individual partitions. The hope was to add it to all of them and then
eventually the template (using an older version of pg_partman).

Don.
-- 
Don Seiler
www.seiler.us

---

From: Rob Sargent <[email-masked]>
Date: Thu, 31 Oct 2024 10:13:03 -0600

On Oct 31, 2024, at 10:02 AM, Don Seiler <[email-masked]> wrote:

Are you able to cluster the table ? The idea is that rows ordered in the same way  as the index might reduce it's size ?

I'm not sure on this. There are other indexes on these table partitions as well.

Another bit of useful info that I should have shared immediately is that this is a monthly partitioned table, going back years. We don't drop old partitions (yet) on this one. For now we've only added this index to a few individual partitions. The hope was to add it to all of them and then eventually the template (using an older version of pg_partman).

Don.

--

Whats the fill factor?

---

From: Don Seiler <[email-masked]>
Date: Wed, 6 Nov 2024 21:41:02 -0600

> Whats the fill factor?

No fill factor is specified, so I'm assuming it's the default 90% for
indexes.

FYI we did a REINDEX for the index in question tonight. Since the index was
for last month, there are no more writes to it so we didn't use
CONCURRENTLY either. The size went from 83GB to 48GB, which also happens to
be the size of the table partition.

-- 
Don Seiler
www.seiler.us