Index Bloat and When REINDEX Brings the Speed Back
Because PostgreSQL leaves old row versions behind until VACUUM removes them, an index on a heavily updated table can grow far larger than the data it describes, and a bigger index means more pages to read and less of it cached. REINDEX CONCURRENTLY rebuilds it compactly. Bloat is worth checking when an index that used to be fast has slowly become slow without the query or the data volume changing.
Questions this Concept answers
Why does a bloated index make queries slower even when the query and the data volume have not changed?
J
jeremy
Text
PostgreSQL Btree Index Bloat and REINDEX CONCURRENTLY
This page explains why a PostgreSQL B-tree index can quietly grow far bigger than the data it points at. Because every UPDATE writes a new row version, each index gets new entries while the old ones …