Covering, Partial, Expression and Unique Indexes, and Index Cost

In one line

Covering indexes (INCLUDE) let queries be answered from the index alone (index-only scans). Partial indexes cover just the rows queries care about. Expression indexes index a computed value. Unique indexes enforce rules. Every index costs write amplification, storage and cache, so unused and duplicate indexes should be dropped.

0/5 · 0%

Think of it like this

A cheat sheet. If the index card already has the phone number next to the name, you never open the big book (covering). A card for only VIP customers is far smaller than one for everyone (partial).

Key ideas

01
Index-only scan: if all selected columns are in the index and the visibility map says the page is all-visible, PostgreSQL skips the heap. CREATE INDEX ON orders (customer_id, created_at) INCLUDE (total, status). It needs a well-vacuumed table.
02
Partial index: CREATE INDEX ON job (run_at) WHERE status = 'queued'. It's tiny when most jobs are done, and the query's WHERE must imply the predicate. Also used for partial uniqueness.
03
Expression index: ON users (lower(email)), ON events ((payload->>'type')). The planner uses it only for the same expression, and statistics are collected on it after ANALYZE.
04
Cost: every INSERT writes to every index; UPDATEs of indexed columns (non-HOT) write to all indexes. A table with 15 indexes can have 10× the write cost of one with 2. Indexes also compete for cache.
05
Find waste: pg_stat_user_indexes.idx_scan = 0 over a full business cycle (check replicas too), duplicates (same leading columns), and indexes that are prefixes of others. Build new ones with CREATE INDEX CONCURRENTLY.

Code & diagrams

advanced-indexes.sqlsql

-- covering index -> index-only scan
CREATE INDEX CONCURRENTLY orders_cust_created_cov
  ON orders (customer_id, created_at DESC) INCLUDE (total, status);
EXPLAIN (ANALYZE, BUFFERS)
SELECT created_at, total, status FROM orders WHERE customer_id = 42
ORDER BY created_at DESC LIMIT 20;
-- Index Only Scan using orders_cust_created_cov ... Heap Fetches: 0

-- partial index for a queue
CREATE INDEX job_queued ON job (run_at) WHERE status = 'queued';

-- unused indexes (reset stats deliberately; check replicas too)
SELECT s.relname, s.indexrelname, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC;

Interview problem

The problem

Write-heavy table with 18 indexes

An events table ingests 30K rows/sec and has 18 indexes added over the years; insert latency and WAL volume are high. Plan a safe index cleanup.

When it breaks

CREATE INDEX (without CONCURRENTLY) on a busy table

What you see

It takes a SHARE lock that blocks all writes for the duration of the build, which can be many minutes.

Fix & prevent

Use CREATE INDEX CONCURRENTLY (slower, doesn't block writes); if it fails it leaves an INVALID index to drop and retry.

Explain it without notes

What does an index-only scan need to avoid heap access?

Practice

Create a unique index enforcing one username per tenant, case-insensitively.

Trade-offs

↔
Covering and partial indexes make reads cheap; each index taxes every write and consumes cache.

Done when you can

I can design covering, partial and expression indexes and prune unused ones safely.

Command Palette