Re: Question about behavior of conditional indexes

On Tue, 21 Sept 2021 at 15:59, Koen De Groote <kdg(dot)dev(at)gmail(dot)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
>