Tom Dearman <tom.dearman@xxxxxxxxx> writes: > We have change autovacuum so that it runs more frequently autovacuum_vacuum_scale_factor=0.035, the reason we have a partial index 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 clause status=‘IN_PROGRESS’. In theory it works well, but we get a lot of index bloat as there is a lot of churn on the status value, 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
