Re: Question about behavior of conditional indexes

Just to clarify, I suggested fillfactor likely could *not* help because you
are indexing a column that has the value change. The idea with reducing
fillfactor is that the rows can be updated with the new versions remaining
in the same 8KB block in the file for table/row storage. If the new version
of the row remains in the same page, then the index pointer doesn't have to
be updated until that old version of the row gets vacuumed away. But alas,
when the value in the index changes, then all bets are off. Although, I
suppose in your workflow you might update these rows frequently and NOT
change the status column, then I would certainly consider reducing the
fillfactor, but it will mean perpetual "reserved space" (normally called
bloat though that has a different implication) in the blocks/pages that
only hold old records that won't be getting updates anymore.

If you check pg_stat_user_tables, then you will see autovauum count and can
check it periodically to see how often it is doing work. I'd lower
autovacuum_vacuum_scale_factor and perhaps autovacuum_vacuum_cost_delay
(default was 20ms and lowered to 2ms in PG12) to ensure work is done more
often, and more work is done in each cycle before it pauses to avoid
overloading the I/O system.

>