Tom Dearman <tom.dearman@gmail.com> writes:
> We have change autovacuum so that it runs more frequently autovacuum_vacuum_scale_factor=0.035, the reason we have a
partialindex on the status is that in a table of 300 million entries, only about 100 or so would have
status=‘IN_PROGRESS’so we think this should be a nice small index and many of our queries want to look up with a where
clausestatus=‘IN_PROGRESS’. In theory it works well, but we get a lot of index bloat as there is a lot of churn on the
statusvalue, ie each row starts as IN_PROGRESS and then goes to one of 4 possible completed statuses.
Is it really the case that only this index is bloating? In principle, an
update on a row of the table should result in new entries in every index
of the table. A partial index, due to the filter applied to possibly not
store any index entry, should in theory have less bloat than other
indexes.
If that's not what you're seeing, there must be something about the data
being stored in that index (not the partial-index filter condition) that
results in a lot of low-occupancy index pages over time. You didn't say
anything about what the data payload is. But we've seen bloat problems in
indexes where, say, every tenth or hundredth value in the index ordering
would persist for a long time while the ones in between get deleted
quickly. That leads to low-density indexes that VACUUM can't do anything
about.
regards, tom lane