Downsides of Indexes Use disk space. Slow down UPDATE , INSERT and DELETE operations Indexed columns prevent H.O.T. updates They contribute to VACUUM time They add to query planning time Space contributes to backup and restore time
How did we start down this path? Exceeding IOPS limits on cloud provider For very large tables we were seeing very infrequent and long running autovacuum Removing indexes helps improve write rate
Removal Process Fetch the create index statement prior to the removal, as a recovery plan. SELECT pg_get_indexdef(indexrelid) AS index_query
FROM pg_index WHERE indrelid = 'users'::regclass;
Perform the create index... with the CONCURRENTLY option Rails: We drop them concurrently, which requires a migration file with disable_ddl_transaction! and we use the concurrently option. Rails: We check for the presence of the index first e.g. index_exists?(:posts, :title) in case the index wasn't added to all environments
Future investigations: Corruption and bloat Both tables and indexes may have bloat To address bloat, reindex the index To reindex on PG < 12 consider tools like pg_repack On PG >= 12, consider REINDEX INDEX CONCURRENTLY idx ;
References Some helpful posts on removing unused indexes, common DBA tasks, queries for index maintenance, and index bloat and index types. 1. https://www.cybertec-postgresql.com/en/get-rid-of-your-unused-indexes 2. Common DBA tasks for PostgreSQL 3. https://www.percona.com/blog/2020/03/31/useful-queries-for-postgresql-index- maintenance 4. Significant database and index bloat 5. https://thoughtbot.com/blog/postgres-index-types
Summary Remove unused indexes to improve write rate Find unused indexes by looking for zero scans For frequently utilized indexes that are bloated, remove the bloat