How to safely remove database indexes (without bringing down your application)

The reasons to delete indexes

  • Lower memory consumption : each index uses memory. Lots of indexes on large tables means a lot of memory being used
  • Lower CPU usage : whenever a query updates a table (insert/update/delete), the indexes of that table must be updated as well. The more indexes, the more CPU power is required

The dangers of deleting indexes

  • Performance issues : if the index was still in use, queries will slow down, in some cases causing major issues
  • Recreating the index might take long : if the table is big (or extremely big) it could take minutes to hours to recreate the index, impacting your application the entire time. Plus, the process of recreating the index adds additional CPU load.

Don’t delete… just yet. Disable instead !

The solution is simple : instead of permanently deleting the index, temporarily disable it, then let your application run for a few weeks before you permanently delete the index. If you do notice performance issues, just enable the index again :


					ALTER TABLE MyBigTable ALTER INDEX someIndex IGNORED; -- "NOT IGNORED" to undo this action



					ALTER TABLE MyBigTable ALTER INDEX someIndex INVISIBLE; -- "VISIBLE" to undo this action



					update pg_index set indisvalid = false where indexrelid = 'test_pkey'::regclass;


Microsoft SQL Server and Oracle Server

					ALTER INDEX IX_The_Index_You_Want_To_Dsiable ON TheSchema.TheTableName  
DISABLE; -- "REBUILD" to undo this action
Wim Godden

Wim Godden

Tag Post :
Share This :

Are you looking for a reliable partner who understands you and your business?

No big words, endless planning or months of searching.