Re: Index Partition Size Double of its Table Partition?

On Wed, Oct 30, 2024 at 12:08 PM Don Seiler <don(at)seiler(dot)us> 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