Question 250709 in PostgreSQL Question posted in
The official documentation can be found here.

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?

Tags:

2

Answers


  1. 0

    If you update a row so that the index condition is no longer met, the row is marked as deleted. As soon as VACUUM removes the old row version, the corresponding index entry is removed.

    So yes, updating lots of rows so that they no longer meet the index condition will eventually lead to index bloat, and you will need REINDEX to get rid of the bloat.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search