Re: Question about behavior of conditional indexes

On 21/09/21 22:28, Koen De Groote 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.
>
> 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?
>
>
>
> So I'm wondering if the behavior is as I described.
>
> Regards,
> Koen De Groote

Can you upgrade to pg13?  A lot of work was done on indexes in pg12 & 
13.  So possibly your problem may have been resolved, at least in part.

Note that pg 14 is due out this month.


Cheers,
Gavin