---

🟢 Junior Level

Indexes are not free: they accelerate read operations (SELECT) at the expense of slowing down data modifications (INSERT, UPDATE, DELETE) and consuming substantial disk storage and RAM.

Simple Analogy

Imagine an encyclopedia with multiple specialized back-of-the-book indexes: an alphabetical index by topic, an index by geographic location, an index by chronological date, and an index by author. Whenever an editor updates or adds a single article, they cannot simply write the text; they must also locate, update, and cross-reference entries across every single index. The more indexes maintained, the slower and more burdensome every edit becomes.

The 3 Core Disadvantages

1. Write Performance Penalty: Every INSERT, UPDATE, and DELETE must synchronize changes across every index on the table.
2. Disk Storage Consumption: In production databases, it is common for the total size of indexes to exceed the size of the raw table data by 2x–5x.
3. Maintenance and Bloat Overhead: Over time, B-tree indexes suffer from page fragmentation and bloat, requiring periodic online maintenance (REINDEX CONCURRENTLY).

-- On a table with 6 indexes:
-- Executing 1 simple INSERT statement:
INSERT INTO orders (customer_id, status, total_amount, created_at) 
VALUES (101, 'PENDING', 250.00, NOW());

-- What the database engine must physically write:
-- 1. Write the new row tuple into the table heap page
-- 2. Insert into Index #1 (Primary Key: order_id)
-- 3. Insert into Index #2 (customer_id)
-- 4. Insert into Index #3 (status)
-- 5. Insert into Index #4 (created_at)
-- 6. Insert into Index #5 (composite: customer_id, created_at)
-- 7. Insert into Index #6 (total_amount)
-- Result: 7 distinct disk block operations and 7 WAL records instead of 1!

---

🟡 Middle Level

1. Write Amplification & WAL Generation

Every secondary index multiplies the physical disk I/O and Write-Ahead Log (WAL) records generated by data modifications:

\[\text{1 Row Modification} \longrightarrow \text{Heap Block Write} + \sum_{i=1}^{N} \text{Index Tree Traversal Writes} + \text{WAL Logging}\]

Number of Secondary Indexes	Typical INSERT Latency	WAL Volume Multiplier
1–2	~0.5–1.0 ms	1.0x (Baseline)
5–7	~3.0–8.0 ms	3.0x–5.0x
10–15+	~15.0–40.0+ ms	8.0x–12.0x

2. Destruction of HOT (Heap-Only Tuple) Optimization

In PostgreSQL MVCC, an UPDATE physically writes a brand new version of the row into the table heap.
HOT (Heap-Only Tuple) is a critical optimization that allows PostgreSQL to link the old and new row versions directly inside the same 8 KB heap page without modifying index pointers.

-- Assume an index exists on 'status'
CREATE INDEX idx_orders_status ON orders(status);

-- ❌ Updating an indexed column DESTROYS HOT optimization!
UPDATE orders SET status = 'COMPLETED' WHERE order_id = 42;

-- ✅ Updating an unindexed column preserves HOT optimization:
UPDATE orders SET notes = 'Deliver after 5 PM' WHERE order_id = 42;

What Happens When HOT is Broken?

If an UPDATE modifies even one column that is indexed, HOT optimization is disabled. Because PostgreSQL indexes point to physical heap row addresses (TIDs), the new row version receives a new TID, forcing every single index on the table to receive a new index pointer, even if those other indexes do not reference the modified column!

3. RAM Cache Competition (shared_buffers)

Database servers allocate memory to buffer pools (shared_buffers in PostgreSQL, innodb_buffer_pool_size in MySQL) to keep hot data in RAM.

• If raw table data occupies 20 GB, but 12 indexes add another 50 GB, the working set totals 70 GB.
• On a server with 32 GB of RAM, indexes aggressively evict hot table heap pages from cache.
• The storage subsystem falls into continuous, erratic random disk I/O seeks, degrading overall system throughput.

4. Increased Query Planning Latency

PostgreSQL uses a cost-based query optimizer. Before executing any query, the planner generates execution paths for every viable index candidate and calculates prospective disk/CPU costs. Having dozens of indexes on a table noticeably increases query compilation and planning time (Planning Time in EXPLAIN).

---

🔴 Senior Level

Write Amplification & Streaming Replication Lag

In high-throughput architectures utilizing PostgreSQL streaming replication, excessive indexing is the leading cause of Physical Replication Lag:

1. Every index insertion, deletion, and B-tree page split generates detailed physical WAL records.
2. Read replicas (standby nodes) process WAL streams sequentially using a single recovery apply thread.
3. When batch updates or high-concurrency writes hit an over-indexed table, the primary floods the replication stream with gigabytes of index WAL records per minute.
4. The standby’s single apply worker becomes CPU- and I/O-bound, falling minutes or hours behind the primary and breaking read-after-write consistency.

Measuring HOT Update Efficiency in Production

SELECT 
    schemaname,
    relname AS table_name,
    n_tup_hot_update AS hot_updates,
    n_tup_upd AS total_updates,
    ROUND(100.0 * n_tup_hot_update / NULLIF(n_tup_upd, 0), 2) AS hot_update_ratio_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 1000
ORDER BY hot_update_ratio_pct ASC;

-- Operational Benchmarks:
-- > 80%: Excellent. The table is well-tuned with appropriate fillfactor and minimal indexes.
-- < 25%: Critical. High write amplification; indexed columns are frequently updated.

Table & Index Fillfactor Tuning

By default, PostgreSQL tables have a fillfactor of 100 (pages are filled to 100% capacity during inserts). When an UPDATE occurs, the page has zero free space, forcing the new row version to another page and immediately breaking HOT optimization.

-- 1. Reserve 20% free space per heap page to enable in-page HOT updates
ALTER TABLE orders SET (fillfactor = 80);

-- 2. Reserve 20% free space in B-tree leaf pages to absorb random inserts without splits
CREATE INDEX idx_orders_status ON orders(status) WITH (fillfactor = 80);

-- 3. Repack table to apply new fillfactor without table downtime (via pg_repack)
-- Shell: pg_repack -t orders -d production_db

Automated Audit for Unused and Redundant Indexes

-- Identify indexes that have zero scans and are safe to drop
SELECT 
    s.schemaname,
    s.relname AS table_name,
    s.indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(s.indexrelid)) AS wasted_size,
    s.idx_scan AS scan_count
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique     -- Protect unique constraints
  AND NOT i.indisprimary    -- Protect primary keys
ORDER BY pg_relation_size(s.indexrelid) DESC;

---

4 Tricky Questions

1. If an UPDATE statement modifies only 1 column, but that column has an index, how many indexes on that table get updated?

Answer:
ALL of them!
In PostgreSQL, index entries store the physical address (TID) of the heap row, rather than a logical primary key (as InnoDB does in MySQL).
When an UPDATE touches an indexed column, HOT optimization is completely disabled. The new row version must be written with a new physical TID. Because the row has moved to a new physical address, every single index on the table must insert a new pointer referencing the new TID, regardless of whether those other indexes reference the modified column or not. This is why adding an index on a frequently updated column causes catastrophic write degradation across the entire table.

2. Can having too many indexes actually slow down SELECT queries?

Answer:
Yes, through two distinct mechanisms:

1. Buffer Cache Eviction: Secondary indexes consume space in shared_buffers and the operating system page cache. When large bloated indexes displace table heap pages from memory, queries that would have executed via fast memory hits are forced to perform high-latency random disk reads.
2. Query Planning Overhead: PostgreSQL’s cost-based optimizer must evaluate combinations of potential index paths. On tables with 15–20+ indexes, query compilation and planning time (Planning Time in EXPLAIN) can exceed actual execution time, especially for fast OLTP point lookups.

3. What is the fundamental difference between table fillfactor and index fillfactor?

Answer:

• Table fillfactor (default 100): Specifies the percentage of an 8 KB heap page to fill during INSERT. Lowering table fillfactor (e.g., to 75–85) reserves free space on the heap page specifically to allow future UPDATE operations to place the new row version on the same page, enabling HOT optimization.
• Index fillfactor (default 90 for B-Tree): Specifies the percentage of an 8 KB index leaf page to fill during initial creation or REINDEX. Lowering index fillfactor reserves space on index leaf pages to absorb random out-of-order INSERTs without triggering immediate, costly B-tree page splits.

4. Why does standard VACUUM fail to resolve physical index bloat on disk?

Answer:
When rows are updated or deleted, standard VACUUM removes dead index pointers and marks those internal line pointer slots as reusable for future incoming keys within that same value range. However, standard VACUUM cannot merge underpopulated leaf pages back together, nor can it truncate disk space in the middle of a physical file. As a result, B-tree pages remain 30–50% empty, and the index file size on disk never shrinks.
To physically compress the index and return unused disk blocks to the operating system, you must run REINDEX INDEX CONCURRENTLY <index_name> or rebuild the relation via pg_repack.

---

🎯 Interview Cheat Sheet

Core Liabilities of Indexes

• Write Amplification: Every INSERT, UPDATE, and DELETE incurs extra I/O across every index and bloats the Write-Ahead Log (WAL).
• HOT Breakdown: Updating an indexed column disables Heap-Only Tuple optimization, forcing all indexes on the table to write new pointers.
• Cache Pollution: Bloated indexes compete with table data for shared_buffers, causing high disk read rates.
• Replication Lag: Saturated WAL streams overwhelm the single apply worker on streaming replication read replicas.

Essential Mitigation Strategies

• Consolidation: Replace separate single-column indexes (a) and (b) with a single composite index (a, b) where applicable.
• Partial Indexes: Use WHERE clauses (e.g., WHERE status = 'PENDING') to index only the 5% active working set.
• Fillfactor Tuning: Set table fillfactor = 80 on update-heavy tables to keep HOT ratios > 80%.
• Lifecycle Audits: Regularly inspect pg_stat_user_indexes and drop zero-scan indexes using DROP INDEX CONCURRENTLY.

Red Flags (What NOT to Say)

• ❌ “Storage is cheap, so having 15 indexes on a table has no real downside.” (Storage is cheap, but RAM cache, random I/O, and replication bandwidth are critical bottlenecks).
• ❌ “Postgres updates only the modified column’s index during an UPDATE.” (If HOT is disabled, every single index on the table must be updated with the new TID).
• ❌ “Running VACUUM reclaims unused index disk space back to the operating system.” (Only REINDEX CONCURRENTLY or VACUUM FULL compacts physical index files).

Related Topics

• What are indexes and why are they needed — Index fundamentals and trade-offs
• How does B-tree index work — Page splits, deduplication, and fragmentation
• When should you create an index — Strategic criteria and workload telemetry
• How does MVCC work in PostgreSQL — Multi-version concurrency control and tuple versions