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
