Overview
Every index you add speeds up specific queries by letting the database jump straight to matching rows instead of scanning the whole table. But each one is a second (or third, or thirteenth) structure that must be updated on every insert, update, and delete, so piling on indexes steadily erodes write speed. The right balance depends on whether your workload is dominated by reads or writes.
Comparison Diagram
Comparison Table
| Aspect | Many Indexes | Few Indexes |
|---|---|---|
| Write path | Every INSERT/UPDATE/DELETE also updates each index’s structure | Writes mostly touch just the base table (or 1-2 indexes) |
| Structures updated per write | One update per index plus the table (N+1 operations) | Minimal: table plus a small, fixed set of index updates |
| Disk I/O per write | Extra page writes and WAL/journal entries for each index B-tree | Fewer page writes, smaller transaction log footprint |
| Write throughput & latency | Lower sustained throughput; each write costs more | Higher sustained throughput; commits return faster |
| Read/query performance | Fast lookups and filtering across many indexed columns | Slower queries on unindexed columns; more full scans |
| Storage footprint | Larger on-disk size from redundant index copies of data | Smaller footprint, closer to raw table size |
| Maintenance cost | Rebuilds, vacuums, and statistics updates scale with index count | Cheaper, faster maintenance windows |
| Best-fit workload | Read-heavy, query-diverse systems (reporting, OLAP) | Write-heavy, ingest-heavy systems (logging, OLTP, ETL) |
Key Differences
- Every extra index adds a corresponding update on each write operation, not just at read time.
- Many indexes shrink query latency but inflate insert cost on the same table.
- Fewer indexes cut WAL volume and lock contention during heavy write bursts.
- Index count is a direct trade between read performance and write throughput, not a free win.
- Rebuild and vacuum overhead grows with every index the database has to maintain.
When to Use Each
Many Indexes
- Read-Heavy Reporting: Dashboards and BI tools filter and sort on many different columns, so multiple indexes keep those queries fast.
- Ad-hoc Analytics: Analysts query unpredictable column combinations, and broad index coverage avoids costly full table scans.
- OLAP Star Schemas: Fact tables with many foreign-key joins rely on indexed keys to keep join performance acceptable.
Few Indexes
- High-Volume Ingestion: Event or log pipelines need to sustain thousands of inserts per second, and each index would slow every one down.
- Bulk Load Windows: ETL jobs often drop non-essential indexes before a load and rebuild them after to maximize insert speed.
- Write-Heavy OLTP: Transactional systems prioritize fast commit latency over supporting every possible query shape.