Re: Question about behavior of conditional indexes - Mailing list pgsql-general

From Michael Lewis
Subject Re: Question about behavior of conditional indexes
Date
Msg-id CAHOFxGpCduNGDBZXWkbqUa1rHBNP9Gb-nT=Y3bfSM2rOXHF=ng@mail.gmail.com
Whole thread Raw
In response to Re: Question about behavior of conditional indexes  (Koen De Groote <kdg.dev@gmail.com>)
List pgsql-general
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.

pgsql-general by date:

Previous
From: Koen De Groote
Date:
Subject: Re: Question about behavior of conditional indexes
Next
From: Karsten Hilbert
Date:
Subject: Re: Timestamp with vs without time zone.