Identify invalid indexes that were created during index rebuild SELECT c. Rebuild the index: Instead of dropping and creating the index yourself, PostgreSQL provides a way to re-build an existing index in-place using the REINDEX command: Re-create the index: If you re-create the index, it will be built in an optimal way. There are several ways to rebuild an index: To clear bloat in an index, you need to rebuild it. After running these queries you will most likely find some bloat, so the next thing to do it clear up that space. Lucky for us, some good people on the world wide web already did the hard work and wrote queries to estimate table bloat and index bloat. Bloat also affects indexes, so to free up space, bloat is a good place to look.Įstimating bloat in tables and indexes is apparently not a simple task. This process creates what's called "bloat", which can cause tables to consume more space than they really need. When you update rows in a table, PostgreSQL marks the tuple as dead and adds the updated tuple in the next available space. We do this every once in a while, so in our case there were no unused indexes to drop. Find table oid by name SELECT oid FROM pg_class c WHERE relname = 'table_name' - Reset counts for all indexes of table SELECT pg_stat_reset_single_table_counters ( 14662536 ) When we find an index we suspect is not being used, or when we add new indexes in place of old ones, we usually reset the counters for the table and wait for a while: PostgreSQL provides a few functions to reset statistics at different levels. It's also a good idea to reset the statistics counters from time to time, usually right after you finished inspecting the list. This can be time consuming in the first couple of times, but after you get rid of most unused indexes it becomes easier. To find the unused indexes you can actually drop, you usually have to go over the list one by one and make a decision. The indexes will look like they were not used, but it doesn't mean we can dispose of them. 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 technics 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. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |