Postgres: Set fillfactor to 50?

Question

I have a table of records that is populated sequentially once, but then every record is updated (the order in which they are updated and the timing of the updates are both random). The updates are not HOT updates. Is there any advantage to setting my fillfactor for this table to 50, or even less than 50, given these facts?

Ochlophobia

"every record is updated" in a single transaction? If so, autovacuum won't be happy, regardless of any setting. – Crevice

It's a 30 million row table. Every record is updated in transactions sized between ~1,000 to 10,000 records (at times), and totally at random in a transaction size of 1 record (at times). – Ochlophobia

Answer 1

Ok, as you mentioned in the comments to your question, you're making changes in your table using transactions updating 1-10k records in each transaction. This is right approach leaving some chances to autovacuum to make its work. But table's fillfactor is not the first thing I'd check/change. Fillfactor can help you to speed up the process, but if autovacuum is not aggressive enough, you'll get very bloated table and bad performance soon.

So, first, I'd suggest you to control your table's bloating level. There is a number of queries which can help you:

• https://wiki.postgresql.org/wiki/Show_database_bloat
• http://blog.ioguix.net/postgresql/2014/09/10/Bloat-estimation-for-tables.html
• https://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/table_bloat-82-84.sql
• https://github.com/dataegret/pg-utils/blob/master/sql/table_bloat.sql (and for indexes:
• https://github.com/dataegret/pg-utils/blob/master/sql/index_bloat.sql; these queries require pgstattuple extension)

Next, I'd tune autovacuum to much more aggressive state than default, like this (this is usually good idea even if you don't need to process whole table in short period of time), something like this:

log_autovacuum_min_duration = 0
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.05
autovacuum_naptime = 60
autovacuum_vacuum_cost_delay = 20

After some significant number of transactions with UPDATEs, check the bloating level.

Finally, yes, I'd tune fillfactor but probably to some higher (and more usual) value like 80 or 90 – here you need to make some predictions, what is the probability that 10% or more tuples inside a page will be updated by the single transaction? If the chances are very high, reduce fillfactor. But you've mentioned that order of rows in UPDATEs is random, so I'd use 80-90%. Keep in mind that there is an obvious trade-off here: if you set fillfactor to 50, your table will need 2x more disk space and all operations will naturally become slower. If you want to go deep to this question, I suggest creating 21 tables with fillfactors 50..100 with the same data and testing UPDATE TPS with pgbench.

Crevice

Thanks, this is a really thoughtful answer. It gets at my underlying question about how to best think about fillfactor, and gives me suggestions for how to tackle my underlying issue. – Ochlophobia

I would like to add that HOT only works when updating non-indexed fields – Bugg