SQL 75 🛢️ Index Maintenance, Overhead, and Unused Indexes
Indexes are created for queries, but they live with writes. Every index you add to a table becomes a permanent obligation: the database must update it on every insert, every update that touches an indexed column, and every delete. Over months and years, indexes accumulate bloat from dead entries, statistics drift out of date, and some indexes stop being used at all as query patterns change. A schema that was well-indexed at launch can, two years later, be a schema held back by a dozen indexes that serve nothing but still tax every write. This chapter is about the lifecycle that follows index creation: understanding the true cost of an index, measuring its usage, recognizing when it has become overhead, and maintaining it so it stays useful.
Key point: An index is not a one-time optimization. It is an ongoing liability that must earn its cost on every write. The indexes that hurt most are the ones nobody remembers creating and nobody notices are unused.
Why index maintenance matters
The invisible write tax. A slow query is visible: someone complains, a dashboard fires an alert, a page times out. Index write overhead is invisible. The insert that would take half a millisecond with three indexes takes one and a half milliseconds with eight, and the application absorbs it silently. At low volume this is imperceptible. At high volume, it becomes the difference between a system that scales and one that falls over during a traffic spike. The cost of unused indexes is paid continuously and noticed rarely.
The bloat accumulation problem. A B-tree index does not shrink when rows are deleted. A deleted row’s index entry is marked dead but remains physically present until a maintenance process removes it. Over time, a table that has churned through millions of inserts and deletes has an index whose physical size far exceeds the number of live entries. The index takes more space, fits in memory less easily, and requires more page reads per lookup. Bloat is silent and gradual, and it degrades every query that uses the index.
The statistics drift problem. The query optimizer decides whether to use an index based on statistics: how many rows, how many distinct values, how the values are distributed. These statistics are gathered by an automatic process that samples the table periodically. If the table changes quickly and statistics are stale, the optimizer makes bad decisions. It might ignore an index that would help, or use one that would be better left alone. Stale statistics cause unpredictable performance even when the indexes themselves are correct.
The index decay problem. Query patterns change as applications evolve. A feature is removed, a report is replaced, a table is no longer queried by a column that used to be central. The indexes created for those queries remain. They are never used again, but they still cost every write. Over years, a table can accumulate a dozen unused indexes, each one a permanent tax on a workload that no longer resembles the one it was designed for.
The measurement problem. Most developers do not know which indexes their tables actually use. Without usage statistics, index decisions are guesses. The database records how many times each index has been scanned since the last statistics reset, and this data is the only reliable way to know which indexes earn their keep and which do not. A schema without index usage auditing is a schema managed by intuition.
The rebuild problem. Even a well-used index degrades over time. Page splits leave nodes half full, dead entries accumulate, and the physical layout drifts away from the logical order. Rebuilding the index, either by re-creating it or using a dedicated command, restores it to a compact state. This is periodic maintenance, like changing the oil in a car, and skipping it leads to a slow decline that is easy to ignore until it is severe.
a. The true cost of an index
Every index carries three kinds of cost: write cost, storage cost, and maintenance cost. Understanding each is what lets a developer decide whether an index is worth keeping.
Write cost
Every INSERT into a table must add an entry to every index on that table. Every UPDATE that changes an indexed column must remove the old entry and add a new one. Every DELETE must mark the index entry for the deleted row as dead. The write cost is proportional to the number of indexes, and it applies to every row, forever.
-- Insert into a table with five indexes.
INSERT INTO orders (customer_id, status, total, created_at)
VALUES (42, 'pending', 99.99, NOW());
-- Physical work performed:
-- 1 heap row insert
-- 1 index insert into orders_pkey
-- 1 index insert into idx_orders_customer
-- 1 index insert into idx_orders_status
-- 1 index insert into idx_orders_created
-- 1 index insert into idx_orders_total
-- Total: 6 write operations for one logical row.The multiplication is unavoidable. Each index is a separate physical structure, and each must be updated independently. A table with a dozen indexes performs a dozen index updates per insert. The write amplification factor is essentially the index count plus one.
Storage cost
Indexes consume disk space and memory. A table with five indexes may be smaller than its indexes combined. Every byte of index is a byte that could be holding data pages in the buffer pool, which means more memory pressure and more disk reads as the working set grows beyond available RAM.
-- Check total index size versus table size (PostgreSQL):
SELECT
pg_size_pretty(pg_relation_size('orders')) AS table_size,
pg_size_pretty(pg_indexes_size('orders')) AS indexes_size;
-- Sample output:
-- table_size | indexes_size
-- ------------+--------------
-- 4200 MB | 9800 MB
-- The indexes are more than twice the size of the table itself.Maintenance cost
The database must periodically update statistics on indexed columns, run vacuum or cleanup processes to remove dead index entries, and handle page splits that fragment the index structure. Each index on a table increases the work these background processes must perform, and under heavy write load, they can fall behind.
-- PostgreSQL autovacuum must process each index of each table.
-- A table with 8 indexes takes roughly 8× longer to vacuum than
-- a table with 1 index, at the same row count.
-- Vacuum falling behind means bloat accumulates and performance degrades.Query cost of too many indexes
More indexes do not always mean better query performance. The optimizer must consider every index when planning a query, and with many overlapping indexes, planning takes longer and may choose a suboptimal one. On high-frequency query workloads, planning time becomes a measurable cost.
-- A query with a simple predicate on customer_id.
-- If five indexes all start with customer_id, the planner evaluates all five.
-- The chosen one may not be the best; the planner's cost estimates
-- rely on statistics that may be stale or coarse.The break-even calculation
An index is worth keeping if the read benefit exceeds the write cost. This is hard to compute precisely, but the direction is clear: an index used many times per write is worth keeping, and an index used rarely or never is not. An index scanned a million times a day on a table written a thousand times a day is clearly earning its keep. An index scanned zero times on a table written a million times a day is pure loss.
-- Rough value metric:
-- Value = (scans per day) / (writes per day)
-- Value > 1: index is doing more reads than it costs in writes.
-- Value ≈ 0: index is pure overhead.b. Measuring index usage
Every major relational database records statistics about how often each index is scanned. These statistics are the foundation of index auditing. Without them, index decisions are guesses.
PostgreSQL: pg_stat_user_indexes
PostgreSQL accumulates per-index scan counts in the pg_stat_user_indexes view. The idx_scan column counts the number of index scans initiated on that index since the last statistics reset. An index with idx_scan = 0 has not been used at all.
-- List all indexes on a table with size and scan count.
SELECT
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS size,
idx_scan AS scans
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan ASC;
-- Sample output:
-- index_name | size | scans
-- -------------------------+---------+---------
-- idx_orders_notes | 180 MB | 0
-- idx_orders_legacy_flag | 45 MB | 3
-- idx_orders_status | 120 MB | 1200
-- idx_orders_created | 210 MB | 45000
-- idx_orders_customer | 195 MB | 890000
-- orders_pkey | 220 MB | 2400000The index on notes has never been scanned. The index on legacy_flag has been scanned three times, likely during a migration or a one-off report. Both are candidates for removal.
Resetting statistics
Statistics accumulate since the last reset. After a schema change or a major query pattern change, resetting the statistics gives a clean measurement of current usage. The reset is global or per-database, not per-table.
-- Reset statistics for the current database.
SELECT pg_stat_reset();
-- Wait for a representative period (a week, a month).
-- Then re-query pg_stat_user_indexes to see current usage.MySQL: performance_schema and sys schema
MySQL exposes index usage through the sys.schema_unused_indexes view, which lists indexes that have never been used since the last server restart. The underlying data comes from performance_schema.table_io_waits_summary_by_index_usage.
-- Find unused indexes in MySQL.
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'mydb';
-- Sample output:
-- object_schema | object_name | index_name
-- --------------+-------------+---------------------
-- mydb | orders | idx_orders_notes
-- mydb | orders | idx_orders_legacy_flagSQL Server: sys.dm_db_index_usage_stats
SQL Server tracks index seeks, scans, lookups, and updates in a dynamic management view. The user_seeks and user_scans columns show read usage, while user_updates shows write cost.
-- Find indexes with no reads but many writes.
SELECT
OBJECT_NAME(i.object_id) AS table_name,
i.name AS index_name,
us.user_seeks,
us.user_scans,
us.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats us
ON i.object_id = us.object_id AND i.index_id = us.index_id
WHERE OBJECT_NAME(i.object_id) = 'orders'
ORDER BY us.user_updates DESC;
-- An index with user_seeks = 0, user_scans = 0, user_updates > 0
-- is pure overhead: it costs writes and serves no reads.Oracle: v$object_usage
Oracle requires explicit monitoring to be enabled for each index, then reports usage in v$object_usage. Unlike PostgreSQL or MySQL, it does not track usage automatically.
-- Enable monitoring on an index.
ALTER INDEX idx_orders_notes MONITORING USAGE;
-- After a period, check usage.
SELECT index_name, used, start_monitoring, end_monitoring
FROM v$object_usage
WHERE table_name = 'ORDERS';Interpreting the numbers
A scan count of zero is a strong signal, but it is not conclusive on its own. An index might be used only during a monthly report, or only during a rare incident. Before dropping an index, consider the time window the statistics cover, whether any known periodic workload might use it, and whether a constraint depends on it.
-- Distinguish indexes that must be kept:
-- 1. Primary key indexes: always required.
-- 2. Unique constraint indexes: enforce correctness.
-- 3. Foreign key target indexes: often required for referential integrity.
-- 4. Indexes with high scan counts: clearly earning their keep.
-- 5. Indexes with zero scans and known periodic usage: keep.
-- 6. Indexes with zero scans and no known usage: drop.c. Bloat, fragmentation, and rebuilding
An index is not a static structure. It changes shape as rows are inserted, updated, and deleted. Over time, its physical layout becomes less efficient than its logical content requires. This is bloat, and it degrades performance in ways that are easy to overlook.
How bloat accumulates
Every delete removes an entry from the index logically but leaves it physically until a cleanup process runs. Every update inserts a new entry and marks the old one dead. Every insert that fills a leaf page causes a page split that leaves both halves partially empty. Over time, the index contains many dead entries, and its live entries are spread across more pages than necessary.
-- A leaf page fills at 8 KB with about 200 entries.
-- Insert one more entry: the page splits into two pages,
-- each with about 100 entries.
-- Now the index uses 2 pages for 201 entries.
-- If this pattern repeats across the tree, the index uses
-- roughly twice the space it needs.
-- Delete half the rows.
-- The index still occupies the same pages until vacuum runs.
-- After vacuum, pages with no live entries are reclaimed,
-- but partially-full pages remain partially full.Detecting bloat
PostgreSQL provides the pgstattuple extension, whose pgstattuple function reports the fraction of dead tuples in an index. Third-party tools like pgstattuple and pg_repack provide more detailed analysis. MySQL exposes DATA_FREE in information_schema.tables as a rough indicator of reclaimable space. SQL Server’s sys.dm_db_index_physical_stats reports fragmentation directly.
-- PostgreSQL: install the extension and check a B-tree index.
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('idx_orders_customer');
-- Sample output:
-- table_len | 204800000
-- tuple_count | 850000
-- tuple_len | 153000000
-- dead_tuple_count| 150000
-- dead_tuple_len | 27000000
-- free_space | 24000000
-- Dead tuples are 13% of the index. Bloat is significant.-- SQL Server: check fragmentation.
SELECT
OBJECT_NAME(ips.object_id) AS table_name,
i.name AS index_name,
ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(
DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id
AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 30;Rebuilding an index
Rebuilding recreates the index from scratch, packing live entries into fully-filled pages and discarding dead entries. In PostgreSQL, REINDEX rebuilds in place with a lock; REINDEX CONCURRENTLY rebuilds without blocking writes. In MySQL, OPTIMIZE TABLE rebuilds indexes and reclaims space. In SQL Server, ALTER INDEX ... REBUILD does the same.
-- PostgreSQL: rebuild one index without blocking writes.
REINDEX INDEX CONCURRENTLY idx_orders_customer;
-- PostgreSQL: rebuild all indexes on a table.
REINDEX TABLE CONCURRENTLY orders;
-- MySQL: rebuild table and its indexes.
OPTIMIZE TABLE orders;
-- SQL Server: rebuild one index online.
ALTER INDEX idx_orders_customer ON orders REBUILD WITH (ONLINE = ON);When to rebuild
Rebuilding is expensive. It reads and rewrites the entire index, consuming I/O and CPU. It should be done when bloat is significant, when fragmentation affects query performance, or when the index has been heavily churned. A common threshold in SQL Server is 30% fragmentation; PostgreSQL practitioners typically rebuild when dead tuples exceed 20% or when the index is noticeably larger than the live data warrants.
-- Decision rules:
-- Fragmentation < 10%: no action.
-- Fragmentation 10-30%: REORGANIZE (SQL Server) or leave alone.
-- Fragmentation > 30%: REBUILD.
-- Dead tuples > 20%: REINDEX.
-- Index > 1.5× expected: REINDEX.Autovacuum and automatic maintenance
PostgreSQL’s autovacuum process removes dead entries from indexes automatically, but it does not reclaim space to the operating system and does not fully repack pages. For heavy write workloads, autovacuum keeps bloat bounded but does not eliminate it. Periodic manual REINDEX remains necessary for indexes with high churn. MySQL and SQL Server have similar background processes with similar limitations.
-- PostgreSQL: tune autovacuum for high-churn tables.
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_vacuum_threshold = 1000
);
-- Vacuum runs when 5% of rows change, instead of the default 20%.Complete Example Session
-- ============================================
-- PART 1: THE SCENARIO — A MATURE TABLE
-- ============================================
-- The orders table has been in production for three years.
-- It started with a handful of indexes and accumulated more
-- as features were added and removed.
CREATE TABLE orders (
order_id BIGSERIAL PRIMARY KEY,
tenant_id INT NOT NULL,
customer_id INT NOT NULL,
status VARCHAR(20) NOT NULL,
total DECIMAL(10,2) NOT NULL,
notes TEXT,
legacy_flag BOOLEAN,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
deleted_at TIMESTAMPTZ
);
-- Current indexes:
CREATE INDEX idx_orders_tenant ON orders(tenant_id);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created ON orders(created_at);
CREATE INDEX idx_orders_notes ON orders(notes);
CREATE INDEX idx_orders_legacy_flag ON orders(legacy_flag);
CREATE INDEX idx_orders_deleted ON orders(deleted_at);
-- Plus the primary key.
-- Total: 8 indexes on a table receiving 5,000 inserts per second.
-- ============================================
-- PART 2: MEASURING USAGE
-- ============================================
-- Reset statistics to get a clean measurement.
SELECT pg_stat_reset();
-- Wait two weeks under production load.
SELECT
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS size,
idx_scan AS scans
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan ASC;
-- Output:
-- index_name | size | scans
-- -------------------------+---------+-----------
-- idx_orders_notes | 850 MB | 0
-- idx_orders_legacy_flag | 12 MB | 0
-- idx_orders_deleted | 180 MB | 4200
-- idx_orders_status | 220 MB | 18000
-- idx_orders_tenant | 310 MB | 240000
-- idx_orders_created | 350 MB | 890000
-- idx_orders_customer | 320 MB | 2100000
-- orders_pkey | 380 MB | 4800000
-- Two indexes have zero scans in two weeks.
-- One index (deleted) has minimal scans.
-- ============================================
-- PART 3: IDENTIFYING PURE OVERHEAD
-- ============================================
-- The notes index: 850 MB, zero scans.
-- The legacy_flag index: 12 MB, zero scans.
-- Both are candidates for removal.
-- Why are they unused?
SELECT
schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE indexrelname IN ('idx_orders_notes', 'idx_orders_legacy_flag');
-- The notes column was used by a full-text search feature
-- that was removed two years ago. The legacy_flag column
-- was part of a migration that completed.
-- Neither column appears in any current query.
-- Both indexes tax every insert on the table.
-- ============================================
-- PART 4: ASSESSING THE WRITE COST
-- ============================================
-- Measure insert throughput with and without the unused indexes.
-- With all 8 indexes:
-- INSERT 100,000 rows → 42 seconds (2,380 rows/sec).
-- After dropping the two unused indexes:
DROP INDEX CONCURRENTLY idx_orders_notes;
DROP INDEX CONCURRENTLY idx_orders_legacy_flag;
-- INSERT 100,000 rows → 32 seconds (3,125 rows/sec).
-- Improvement: 31% higher insert throughput.
-- The two indexes were costing nearly a third of the write capacity.
-- ============================================
-- PART 5: STORAGE RECLAIMED
-- ============================================
-- Before dropping:
SELECT pg_size_pretty(pg_indexes_size('orders'));
-- 2602 MB
-- After dropping:
SELECT pg_size_pretty(pg_indexes_size('orders'));
-- 1740 MB
-- 862 MB reclaimed. This space becomes available for the
-- buffer pool to cache more of the remaining indexes and
-- the table's heap pages.
-- ============================================
-- PART 6: THE BLOAT PROBLEM
-- ============================================
-- The customer index has been heavily updated.
SELECT * FROM pgstattuple('idx_orders_customer');
-- Output:
-- table_len | 335544320
-- tuple_count | 1200000
-- tuple_len | 216000000
-- dead_tuple_count | 280000
-- dead_tuple_len | 50400000
-- free_space | 55000000
-- Dead tuples are 23% of the total.
-- The index is 320 MB but only contains 216 MB of live data.
-- 104 MB is dead entries and free space.
-- ============================================
-- PART 7: REBUILDING THE BLOATED INDEX
-- ============================================
-- Rebuild without blocking writes.
REINDEX INDEX CONCURRENTLY idx_orders_customer;
-- Verify the result.
SELECT * FROM pgstattuple('idx_orders_customer');
-- Output:
-- table_len | 228589568
-- tuple_count | 1200000
-- tuple_len | 216000000
-- dead_tuple_count | 0
-- dead_tuple_len | 0
-- free_space | 8000000
-- Index shrank from 335 MB to 228 MB.
-- Lookups now touch 32% fewer pages.
-- ============================================
-- PART 8: STATISTICS FRESHNESS
-- ============================================
-- Check when statistics were last updated.
SELECT
relname,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';
-- If last_analyze is days old on a table with heavy churn,
-- the planner is using stale statistics.
-- Force a statistics refresh.
ANALYZE orders;
-- For high-churn tables, tune autovacuum to run more often.
ALTER TABLE orders SET (
autovacuum_analyze_scale_factor = 0.02,
autovacuum_analyze_threshold = 500
);
-- Analyze after 2% of rows change, instead of the default 10%.
-- ============================================
-- PART 9: DETECTING REDUNDANT INDEXES
-- ============================================
-- Some indexes are prefixes of others and are redundant.
-- Suppose these exist:
-- idx_a ON orders(tenant_id)
-- idx_b ON orders(tenant_id, created_at)
-- idx_a is a prefix of idx_b. Any query that uses idx_a
-- can use idx_b instead, with equal or better performance.
-- Find prefix-redundant indexes:
SELECT
a.indexrelname AS redundant_index,
b.indexrelname AS covering_index
FROM pg_stat_user_indexes a
JOIN pg_stat_user_indexes b
ON a.relname = b.relname
AND a.indexrelname <> b.indexrelname
WHERE a.relname = 'orders'
AND a.indexdef LIKE '%' || b.indexdef || '%'
AND a.indexrelname <> 'orders_pkey';
-- idx_orders_tenant is a prefix of idx_orders_tenant_date,
-- if that composite index exists. Drop the single-column one.
-- ============================================
-- PART 10: THE MAINTENANCE SCHEDULE
-- ============================================
-- A production index maintenance plan:
-- Daily:
-- - Check pg_stat_user_indexes for idx_scan = 0.
-- - Check autovacuum activity for lag.
-- Weekly:
-- - Run pgstattuple on the largest indexes.
-- - Identify bloat above 20%.
-- - REINDEX CONCURRENTLY the worst offenders.
-- Monthly:
-- - Reset statistics.
-- - Re-measure usage over the next period.
-- - Drop any index with zero scans and no known periodic use.
-- - Check for prefix-redundant indexes and drop them.
-- Quarterly:
-- - Review index count per table against write rate.
-- - Audit for indexes created for features that no longer exist.
-- - Verify autovacuum settings on high-churn tables.
-- The final index set:
-- orders_pkey (primary key)
-- idx_orders_customer (high scan count, rebuilt)
-- idx_orders_tenant_date (composite, replaced two singles)
-- idx_orders_created (range queries)
-- idx_orders_active (partial, WHERE deleted_at IS NULL)
-- Five indexes instead of eight, each earning its cost.The ten parts followed a mature production table through its full maintenance lifecycle: measuring usage, identifying pure overhead, quantifying the write cost, reclaiming storage, detecting and fixing bloat, refreshing statistics, finding redundant indexes, and establishing a maintenance schedule. The table went from eight indexes, two of which were pure overhead, to five indexes, each tied to a specific query pattern.
Quick Reference
Index Usage Statistics by Database
| Database | View/Table | Key Column |
|---|---|---|
| PostgreSQL | pg_stat_user_indexes | idx_scan |
| MySQL | sys.schema_unused_indexes | — |
| SQL Server | sys.dm_db_index_usage_stats | user_seeks, user_scans |
| Oracle | v$object_usage | used |
Maintenance Commands by Database
| Database | Rebuild Index | Rebuild Table | Refresh Stats |
|---|---|---|---|
| PostgreSQL | REINDEX CONCURRENTLY | REINDEX TABLE | ANALYZE |
| MySQL | OPTIMIZE TABLE | OPTIMIZE TABLE | ANALYZE TABLE |
| SQL Server | ALTER INDEX REBUILD | ALTER INDEX ALL | UPDATE STATISTICS |
| Oracle | ALTER INDEX REBUILD | ALTER INDEX ALL | DBMS_STATS |
Bloat and Fragmentation Thresholds
| Metric | Action Threshold | Command |
|---|---|---|
| Dead tuples > 20% | Rebuild index | REINDEX CONCURRENTLY |
| Fragmentation > 30% | Rebuild index | ALTER INDEX REBUILD |
| Fragmentation 10-30% | Reorganize or leave | ALTER INDEX REORGANIZE |
| Index > 1.5× expected size | Investigate and rebuild | REINDEX |
Index Removal Decision Rules
| Signal | Action |
|---|---|
| Zero scans over full business cycle | Drop candidate |
| Few scans, no known periodic use | Drop candidate |
| Prefix of another index | Drop the prefix index |
| Backs a primary key or unique constraint | Keep |
| Backs a foreign key target | Keep |
| Used by known monthly/quarterly reports | Keep |
| Zero scans but recent schema change | Wait and re-measure |
Best Practices
✅ Do This:
-- Measure usage before making index decisions.
SELECT indexrelname, idx_scan FROM pg_stat_user_indexes; -- ✅
-- Reset statistics and measure over a full business cycle.
SELECT pg_stat_reset(); -- ✅
-- Drop unused indexes with CONCURRENTLY to avoid locks.
DROP INDEX CONCURRENTLY idx_unused; -- ✅
-- Rebuild bloated indexes without blocking writes.
REINDEX INDEX CONCURRENTLY idx_customer; -- ✅
-- Tune autovacuum on high-churn tables.
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05); -- ✅
-- Refresh statistics after bulk loads.
ANALYZE orders; -- ✅
-- Check index size versus table size periodically.
SELECT pg_size_pretty(pg_indexes_size('orders')); -- ✅
-- Keep a documented inventory of indexes and their purpose.
-- Comment on each index with the query it serves. -- ✅❌ Don’t Do This:
-- Don't create indexes without knowing the query they serve.
CREATE INDEX idx_maybe ON orders(some_column); -- ❌
-- Don't assume statistics will keep themselves fresh.
-- High-churn tables need tuned autovacuum. -- ❌
-- Don't rebuild indexes during peak hours without CONCURRENTLY.
REINDEX INDEX idx_customer; -- ❌ Blocks all writes
-- Don't drop an index based on a single measurement.
-- A monthly report might use it tomorrow. -- ❌
-- Don't keep prefix-redundant indexes.
CREATE INDEX idx_a ON t(col_a);
CREATE INDEX idx_ab ON t(col_a, col_b); -- ❌ idx_a is redundant
-- Don't ignore dead tuples.
-- Bloat grows silently and degrades every lookup. -- ❌
-- Don't let statistics drift on high-churn tables.
-- Stale stats cause bad query plans. -- ❌
-- Don't rebuild everything on a fixed schedule.
-- Measure first; rebuild only what needs it. -- ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Unused indexes accumulate | No usage auditing | Periodic measurement and removal |
| Insert throughput degrades slowly | Write amplification from many indexes | Drop unused indexes |
| Index larger than live data | Bloat from updates and deletes | REINDEX CONCURRENTLY |
| Query plan flips unexpectedly | Stale statistics | ANALYZE or tune autovacuum |
| Vacuum cannot keep up | Autovacuum defaults too conservative | Lower scale factor on churn tables |
| Dropped index breaks a report | Periodic usage not considered | Wait a full business cycle before dropping |
| Rebuild locks the table | REINDEX without CONCURRENTLY | Use CONCURRENTLY where supported |
| Redundant indexes persist | Overlapping composites not audited | Detect prefix matches and drop |
Real-World Examples
1. Find Unused Indexes in PostgreSQL
SELECT indexrelname, idx_scan, pg_size_pretty(
pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;2. Find Unused Indexes in MySQL
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'mydb';3. Find Unused Indexes in SQL Server
SELECT OBJECT_NAME(i.object_id) AS table_name, i.name,
us.user_seeks, us.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats us
ON i.object_id = us.object_id AND i.index_id = us.index_id
WHERE us.user_seeks = 0 AND us.user_updates > 0;4. Drop an Index Concurrently
DROP INDEX CONCURRENTLY idx_orders_notes;
-- No lock on the table; safe during production traffic.5. Rebuild a Bloated Index
REINDEX INDEX CONCURRENTLY idx_orders_customer;
-- Index shrinks, lookups touch fewer pages.6. Rebuild All Indexes on a Table
REINDEX TABLE CONCURRENTLY orders;
-- All indexes rebuilt without blocking writes.7. Refresh Statistics After Bulk Load
ANALYZE orders;
-- Planner now has current row counts and distributions.8. Tune Autovacuum for High-Churn Tables
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_analyze_scale_factor = 0.02
);9. Detect Prefix-Redundant Indexes
SELECT a.indexrelname AS redundant,
b.indexrelname AS covering
FROM pg_indexes a
JOIN pg_indexes b
ON a.tablename = b.tablename
AND a.indexdef LIKE '%' || b.indexdef || '%';10. Document Index Purpose
COMMENT ON INDEX idx_orders_pending IS
'Serves queue worker: WHERE status = pending ORDER BY created_at';
-- Future maintainers know why the index exists.Visual
The Index Lifecycle
┌────────────────────────────────────────────────────────────┐
│ THE INDEX LIFECYCLE │
│ │
│ CREATE │
│ The index is built. Statistics record zero scans. │
│ │ │
│ v │
│ USE │
│ Queries scan the index. Write cost applies. │
│ │ │
│ v │
│ DRIFT │
│ Bloat accumulates. Statistics may grow stale. │
│ Query patterns change. Usage may decline. │
│ │ │
│ v │
│ AUDIT │
│ Measure idx_scan. Check bloat. Check size. │
│ │ │
│ ├───────────────┬──────────────┐ │
│ v v v │
│ STILL USED BLOATED UNUSED │
│ Keep. REINDEX. DROP. │
│ │
│ The audit is periodic. Without it, drift becomes decay. │
│ │
└────────────────────────────────────────────────────────────┘Write Amplification
┌────────────────────────────────────────────────────────────┐
│ WRITE AMPLIFICATION BY INDEX COUNT │
│ │
│ One INSERT into orders (8 indexes): │
│ │
│ ┌─────────┐ │
│ │ INSERT │ │
│ └────┬────┘ │
│ │ │
│ ├──────┬──────┬──────┬──────┬──────┬──────┬─────┐ │
│ │ │ │ │ │ │ │ │ │
│ v v v v v v v v │
│ ┌────┐ ┌────┐ ┌────┐ ┌────┐ ┌────┐ ┌────┐ ┌────┐ ┌──┐│
│ │Heap│ │PK │ │Cust│ │Sts │ │Date│ │Note│ │Flag│ │Del│
│ └────┘ └────┘ └────┘ └────┘ └────┘ └────┘ └────┘ └──┘│
│ │
│ 1 logical INSERT = 9 physical writes. │
│ The Note and Flag indexes serve zero queries, │
│ yet they cost writes on every single insert. │
│ │
│ After dropping Note and Flag: │
│ 1 logical INSERT = 7 physical writes. │
│ 22% reduction in write work. │
│ │
└────────────────────────────────────────────────────────────┘Bloat Accumulation and Rebuild
┌────────────────────────────────────────────────────────────┐
│ INDEX BLOAT: BEFORE AND AFTER REINDEX │
│ │
│ BEFORE REINDEX (23% dead entries) │
│ ┌──────────────────────────────────────────────────┐ │
│ │ Live entries: 216 MB │ │
│ │ Dead entries: 50 MB │ │
│ │ Free space: 55 MB │ │
│ │ Total: 321 MB │ │
│ │ │ │
│ │ Pages: ████████████████████████████████░░░░░░░░ │ │
│ │ ^ live ^ dead ^ free │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ AFTER REINDEX (0% dead) │
│ ┌──────────────────────────────────────────────────┐ │
│ │ Live entries: 216 MB │ │
│ │ Dead entries: 0 MB │ │
│ │ Free space: 8 MB │ │
│ │ Total: 224 MB │ │
│ │ │ │
│ │ Pages: ██████████████████████░░ │ │
│ │ ^ live ^ free │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ Size reduced by 30%. Lookups touch 30% fewer pages. │
│ Memory footprint reduced by 30%. │
│ │
└────────────────────────────────────────────────────────────┘Redundant Index Detection
┌────────────────────────────────────────────────────────────┐
│ PREFIX-REDUNDANT INDEXES │
│ │
│ idx_orders_tenant │
│ ON orders(tenant_id) │
│ │
│ idx_orders_tenant_date │
│ ON orders(tenant_id, created_at) │
│ │
│ A query on tenant_id alone can use either index. │
│ The composite index serves the same seek, and also │
│ serves queries that filter on both columns. │
│ │
│ ┌──────────────────────────────────────────────────┐ │
│ │ Query: WHERE tenant_id = 7 │ │
│ │ idx_orders_tenant: seek, exact │ │
│ │ idx_orders_tenant_date: seek, same rows │ │
│ │ Both work. Composite is strictly more useful. │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ ┌──────────────────────────────────────────────────┐ │
│ │ Query: WHERE tenant_id = 7 AND created_at > ? │ │
│ │ idx_orders_tenant: seek, then filter date │ │
│ │ idx_orders_tenant_date: seek, then range walk │ │
│ │ Only the composite uses the date for the seek. │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ The single-column index adds no capability. │
│ Dropping it removes its write cost. │
│ │
└────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Write amplification | Each index adds one write per DML statement |
| Storage cost | Index size reduces memory available for data |
| Maintenance cost | Vacuum, statistics, and splits scale with index count |
| idx_scan | PostgreSQL per-index scan counter |
| pg_stat_reset | Resets usage statistics for clean measurement |
| Bloat | Dead entries and free space in an index |
| pgstattuple | PostgreSQL extension reporting dead tuple ratio |
| REINDEX CONCURRENTLY | Rebuilds index without blocking writes |
| ANALYZE | Refreshes optimizer statistics |
| Autovacuum | Background process removing dead entries |
| Prefix-redundant | Index whose columns are a prefix of another |
| Usage ratio | Scans divided by writes; higher is better |
Key takeaways:
- Every index is a permanent write tax. An index that is never read still costs every insert, update, and delete. The only way to know whether an index earns its cost is to measure its usage.
- Usage statistics are the foundation of index decisions. PostgreSQL, MySQL, SQL Server, and Oracle all expose scan counts. Reset the statistics, wait a full business cycle, and measure. An index with zero scans over a month is a drop candidate.
- Bloat accumulates silently. Deletes and updates leave dead entries. Over time, the index takes more space and requires more page reads per lookup. Rebuilding restores it to a compact state.
- Rebuild without blocking writes. PostgreSQL’s
REINDEX CONCURRENTLY, MySQL’sOPTIMIZE TABLE, and SQL Server’sONLINE = ONallow index maintenance during production traffic. - Statistics must stay fresh. The optimizer uses statistics to decide whether to use an index. On high-churn tables, default autovacuum settings may be too conservative. Tune them down.
- Redundant indexes hide in plain sight. An index whose columns are a prefix of another composite index adds no capability and only costs writes. Detect prefix redundancy and drop the redundant one.
- Document and audit. An index without a documented purpose becomes forgotten overhead. Record the query each index serves, and revisit the index set periodically as query patterns change.
- Maintenance is a schedule, not an event. Daily usage checks, weekly bloat checks, monthly statistics resets, and quarterly audits keep the index set aligned with the workload.
Remember: Index maintenance is the discipline of keeping the schema’s physical structures aligned with the workload’s actual needs. An index is created for a query, but it lives in a write-heavy environment where it costs time, space, and maintenance effort on every change to the underlying table. Over months and years, indexes outlive the queries they were built for, accumulate dead entries, and drift out of sync with the planner’s statistics. The response is not to stop creating indexes but to treat them as living assets that require measurement, maintenance, and periodic pruning. Measure usage, rebuild what is bloated, refresh what is stale, and remove what is unused. The best index set is not the largest one; it is the smallest one that serves every query the application actually runs.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!