Re: Question about behavior of conditional indexes

Поиск
Список
Период
Сортировка
От Michael Lewis
Тема Re: Question about behavior of conditional indexes
Дата
Msg-id CAHOFxGpCduNGDBZXWkbqUa1rHBNP9Gb-nT=Y3bfSM2rOXHF=ng@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Question about behavior of conditional indexes  (Koen De Groote <kdg.dev@gmail.com>)
Список 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 по дате отправления:

Предыдущее
От: Koen De Groote
Дата:
Сообщение: Re: Question about behavior of conditional indexes
Следующее
От: Karsten Hilbert
Дата:
Сообщение: Re: Timestamp with vs without time zone.