0

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?

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.