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!
VACUUM ANALYSE reviews doesn't result in any index reduction!
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
VACUUM FULL locks the table even for reads! Is are an option for optimizing indexes without causing downtime?