[SOLVED] Does PostgreSQL remove values from partial index if conditon is no longer met?

Does PostgreSQL remove values from partial index if conditon is no longer met?

My partial index in PostgreSQL 14:

CREATE INDEX idx_invoice_account_id ON invoice (account_id) WHERE NOT "is_deleted";

Every invoice row starts with is_deleted = false before 50% of them get deleted (is_deleted = true). Does PostgreSQL remove it from index? If not, is REINDEX the only way to shrink the index?

Solution

The data will get removed from the index by some future vacuum. But that freed up space is only reusable under one of two conditions. Either the leaf page is completely empty and can get recycled somewhere else in the index tree (mostly as a new leaf page), or if some new row can go onto the partially-empty leaf page, meaning the account_id of the new row is the same or adjacent to an account_id of a deleted-then-vacuumed-away row.

If neither of those conditions are met, or if your goal is to currently shrink the index (not just reuse space in the future to forestall future growth), then you would need a REINDEX.