Re: Question about behavior of conditional indexes - Mailing list pgsql-general

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