We are experiencing many slowdowns due to index bloat. On trying to optimize the index, recreating it seems to generate a much smaller index:
reviews_hotel_id_connection_id_review_date_created_at_review_id
: original index
reviews_hotel_id_connection_id_review_date_created_at_new
: exact duplicate of the original index, recreated
reviews | reviews_hotel_id_connection_id_review_date_created_at_review_id | 5296 kB
reviews | reviews_hotel_id_connection_id_review_date_created_at_new | 2944 kB
The new index is only 55% the size of the original index!
Running VACUUM ANALYSE reviews
doesn't result in any index reduction!
Running VACUUM FULL reviews
makes the original index have exactly the same size as the new index:
reviews | reviews_hotel_id_connection_id_review_date_created_at_new | 2944 kB
reviews | reviews_hotel_id_connection_id_review_date_created_at_review_id | 2944 kB
Although VACUUM FULL
locks the table even for reads! Is are an option for optimizing indexes without causing downtime?
Best Answer
VACUUM
typically does not shrink the tables or indexes.From v12 on, you can use
REINDEX CONCURRENTLY
for that.Before, you would create a second index with
CREATE INDEX CONCURRENTLY
, then drop the old index. But that wouldn't work for a constraint index.But 50% bloat like in this case is quite normal and healthy for a b-tree index, and I wouldn't worry about it.