Re: Fillfactor for GIN indexes

Hi,

I've been wondering whether this might improve behavior with one of my
workloads, suffering by GIN bloat - the same one I used to test GIN
fastscan, for example.

It's a batch process that loads a mailing list archive into a table with
a GIN index on message body, by doing something like this:

for file in files:
BEGIN;
for message in file:

SAVEPOINT s;
INSERT INTO messages VALUES (...)

if error:
ROLLBACK TO s;

COMMIT;

And there are multiple processes, each processing subset of mbox files.

There are ~1M messages and right after the load I see this:

List of relations
Schema | Name | Type | Owner | Table | Size
--------+------------------+-------+-------+----------+---------
public | message_body_idx | index | tomas | messages | 2247 MB
(1 row)

and after VACUUM FULL:

List of relations
Schema | Name | Type | Owner | Table | Size
--------+------------------+-------+-------+----------+---------
public | message_body_idx | index | tomas | messages | 403 MB
(1 row)

So the index is ~5x larger, which is probably expected due to the amount
of random inserts within a very short time (~15 minutes), executed in
parallel.

I hoped lowering the fillfactor will improve this, but fillfactor=75 had
pretty much no effect in this case. Is that expected for this kind of
workload? I see the previous discussion talked about random updates, not
inserts, so maybe that's the culprit?

--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services