On Tue, 21 Sept 2021 at 15:59, Koen De Groote <kdg....@gmail.com> wrote:
> Greetings all, > > Working on postgres 11. > > I'm researching an index growing in size and never shrinking, and not > being used anymore after a while. > > The index looks like this: > > "index002" btree (action_performed, should_still_perform_action, > action_performed_at DESC) WHERE should_still_perform_action = false AND > action_performed = true > > So, there are 2 boolean fields, and a conditional clause for both. The > table has about 50M rows, the index barely ever goes over 100K matched rows. > > The idea is to search for rows matching these conditions quickly, and then > update them. This happens daily. > > This means the condition no longer match the index. At this point, does > the row get removed from the index? Or does it stay in there as a dead row? > > I'm noticing index bloat on this index and am wondering if all these > updated rows just stay in the index? > > The index currently sits at 330MB. If I drop and re-create it, it's 1.5MB. > > A cronjob runs a vacuum once per day, I can see the amount of dead rows > dropping in monitoring software. > - This doesn't reclaim the space. VACUUM operation cleans up space above the upper edge(High-water mark). Interleaved fragmentation will be marked for reuse. > > But should this also take care of indexes? In postgres 11, you can't > reindex concurrently, so I was wondering if indexes are skipped by vacuum? > Or only in case of conditional indexes? > - They aren't left untouched by VACUUM, but as I mentioned reclaiming space is not a job of VACUUM operation. You must execute VACUUM FULL. AFAIK, reindexing the 1.5MB index may not need a lot of time(regardless of concurrent reindexing feature). > > > > So I'm wondering if the behavior is as I described. > > Regards, > Koen De Groote >
