Auditing PostgreSQL Indexes: Identifying Unused, Redundant, and Overlapping Indexes
- 2 days ago
- 3 min read
Indexes are essential for accelerating read query performance in PostgreSQL. However, every index maintained on a table comes with a tangible performance cost. Each time an INSERT, UPDATE, or DELETE mutation occurs, PostgreSQL must synchronously update all associated B-Tree, GIN, or GiST index structures.
Over time, active databases accumulate unused, duplicate, and overlapping indexes—often remnants of legacy software releases, redundant migration scripts, or temporary debugging tasks.
These redundant indexes consume valuable RAM inside shared_buffers, bloat physical disk storage, slow down background autovacuum processes, and severely degrade write throughput.
This guide provides an auditing framework to detect unused and overlapping indexes using PostgreSQL system catalogs, evaluate write amplification costs, and safely eliminate unnecessary indexes from production environments.
1. The Cost of Over-Indexing: Write Amplification & Buffer Bloat
Before auditing, DBAs must evaluate the hidden performance costs associated with unoptimized indexes:
+-----------------------------------------------------------------------+
| WRITE AMPLIFICATION OVERHEAD |
| |
| [ INSERT Transaction ] ---> [ Primary Heap Table ] |
| | |
| +---> Update Index 1 (Primary Key)|
| +---> Update Index 2 (idx_user) |
| +---> Update Index 3 (UNUSED!) |
| +---> Update Index 4 (DUPLICATE!) |
| |
| * 1 Mutation triggers 4 synchronous disk/RAM write operations! |
+-----------------------------------------------------------------------+
Key Performance Impact Factors
Write Amplification: Inserting a single tuple into a table with 10 indexes requires 11 discrete write operations (1 heap write + 10 index page writes).
Buffer Pool Depletion: Index pages compete with hot table data for space inside shared_buffers. Unused index pages displace actual table pages from RAM, forcing disk reads.
HOT Update Suppression: Excessive indexing disables Heap-Only Tuple (HOT) updates, preventing PostgreSQL from executing lightweight, same-page row modifications without updating index line pointers.
2. Step 1: Detecting Unused Indexes
PostgreSQL tracks index usage statistics in the pg_stat_user_indexes catalog view. An index with zero or low scan counts on a table subjected to millions of write operations is a primary candidate for removal.
Unused Index Diagnostic SQL Query
Execute this diagnostic query to find indexes with low scan counts relative to table write volume:
SELECT
schemaname,
relname AS table_name,
indexrelname AS index_name,
idx_scan AS number_of_scans,
idx_tup_read AS tuples_read,
idx_tup_fetch AS tuples_fetched,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan < 50 -- Low scan threshold
AND indisunique = FALSE -- Exclude UNIQUE constraints
AND indisprimary = FALSE -- Exclude Primary Keys
AND pg_relation_size(indexrelid) > 1024*1024 -- Filter indexes > 1MB
ORDER BY pg_relation_size(indexrelid) DESC;
CRITICAL DBA WARNING: Statistics in pg_stat_user_indexes reset upon server restarts or running pg_stat_reset(). Ensure the database uptime (pg_postmaster_start_time()) covers several months of representative business activity (including monthly reporting and batch jobs) before dropping "unused" indexes.
3. Step 2: Detecting Redundant and Overlapping Indexes
B-Tree indexes operate on a left-to-right prefix rule. An index defined on columns (A, B) can satisfy queries filtering on column (A) as well as queries filtering on (A, B).
Therefore, maintaining a separate standalone index on (A) alongside an index on (A, B) creates a Redundant Overlapping Index.
Overlapping Index Scenario:
Index 1: CREATE INDEX idx_orders_a_b ON orders (customer_id, status);
Index 2: CREATE INDEX idx_orders_a ON orders (customer_id); <-- REDUNDANT!
Automated Overlapping Index Detection Query
Run this system catalog query to detect prefix-overlapping indexes across your schemas:
SELECT
a.indrelid::regclass AS table_name,
a.indexrelid::regclass AS redundant_index,
b.indexrelid::regclass AS covering_index,
pg_size_pretty(pg_relation_size(a.indexrelid)) AS redundant_index_size
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid AND a.indexrelid <> b.indexrelid
WHERE a.indkey[0:array_length(a.indkey, 1)-1] = b.indkey[0:array_length(a.indkey, 1)-1]
AND array_length(a.indkey, 1) < array_length(b.indkey, 1)
AND a.indisunique = FALSE
AND a.indisprimary = FALSE;
4. Safe Index Deletion Protocol (Zero-Downtime)
Dropping an index directly using DROP INDEX index_name; acquires an AccessExclusiveLock on the parent table, blocking concurrent application queries.
To safely drop indexes in production without blocking writes, follow this 3-step lifecycle protocol:
+-----------------------------------------------------------------------+
| SAFE INDEX DELETION RUNBOOK |
| |
| 1. Mark Index INVALID / UNUSABLE (Hypothetical testing or comment) |
| 2. Execute DROP INDEX CONCURRENTLY |
| 3. Monitor Query Latency via pg_stat_statements |
+-----------------------------------------------------------------------+
Step 1: Use DROP INDEX CONCURRENTLY
Always append the CONCURRENTLY keyword to prevent table locking:
-- Non-blocking index removal
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_redundant_customer;
Step 2: Emergency Rollback Strategy
If dropping an index accidentally triggers a query execution plan regression, immediately recreate the missing index using CREATE INDEX CONCURRENTLY:
-- Rebuilding index online without blocking reads/writes
CREATE INDEX CONCURRENTLY idx_orders_redundant_customer
ON orders (customer_id);
Index Audit Summary Checklist
Audit Task | Metric / Catalog | Success Indicator |
Uptime Check | pg_postmaster_start_time() | Uptime exceeds 30-90 representative days |
Unused Indexes | pg_stat_user_indexes.idx_scan = 0 | Zero scans on non-PK/non-Unique indexes |
Overlapping Indexes | pg_index prefix matching | Single composite index covers multiple scalar indexes |
Non-Blocking Drop | DROP INDEX CONCURRENTLY | Zero lock queuing in pg_stat_activity |
Comments