Indexes used to enforce unique or primary key constraints for tables that were not updated in a while. For example, when the optimizer uses meta data from the index, but not the index itself. The documentation lists a few scenarios when this is possible. Some indexes may seem like they were not used but they were in-fact used: The query is looking for indexes that were not scanned or fetched since the last time the statistics were reset. SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch, pg_size_pretty ( pg_relation_size ( indexrelname :: regclass )) as size FROM pg_stat_all_indexes WHERE schemaname = 'public' AND indexrelname NOT LIKE 'pg_toast_%' AND idx_scan = 0 AND idx_tup_read = 0 AND idx_tup_fetch = 0 ORDER BY pg_relation_size ( indexrelname :: regclass ) DESC This is what the free storage chart of one of our databases looked like in the process: Free space over time (higher means more free space) Using conventional techniques such as rebuilding indexes and tables we cleared up a lot of space, but then one surprising find helped us clear an additional ~20GB of unused indexed values! To start from the end, we ended up freeing more than 70GB of un-optimized and un-utilized space without dropping a single index or deleting any data! We thought this is a good opportunity to do some cleanups that would otherwise be much more challenging. Usually we just provision more storage and forget about it, but this time we were under quarantine, and the system in question was under less load than usual. The Unexpected Find That Freed 20GB of Unused Index Space How to free space without dropping indexes or deleting dataĮvery few months we get an alert from our database monitoring to warn us that we are about to run out of space.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |