PostgreSQL partial and covering indexes: query plans, write amplification, and production trade-offs

You ship a dashboard endpoint that lists “open” support tickets for the signed-in user. The table is large, the filter is selective, and the default B-tree on (user_id) still scans thousands of rows because most of that user’s history is closed. You add another index on (user_id, status) and latency improves—until nightly bulk updates rewrite half the index and replication lag spikes. The problem was never “not enough indexes”; it was the shape of the index relative to the predicate and the cost of keeping auxiliary structures current.

This article walks through partial indexes (indexes with a WHERE clause) and covering indexes (INCLUDE columns and index-only scans), how they change query plans, and why they are some of the easiest wins—and some of the easiest foot-guns—in production PostgreSQL. The focus is the why: what the planner can prove, what it cannot, and how that maps to predictable tail latency when you are building scalable, production-ready systems.

Partial indexes: indexing a slice of the table

A partial index only contains entries for rows that satisfy a predicate:

CREATE INDEX CONCURRENTLY idx_tickets_open
  ON support_tickets (user_id, updated_at DESC)
  WHERE status = 'open';

What changes in the planner’s world

For queries that match the predicate (and can be proven to match), PostgreSQL can use this index as a narrow structure: fewer pages to traverse, better cache residency, and a smaller working set during vacuum and autovacuum. For queries that need closed tickets too, the partial index is irrelevant; the planner picks a different path.

The critical design rule is alignment between the index predicate and the query filter:

• If the application sometimes queries status IN ('open','pending'), a partial index on status = 'open' is not a drop-in optimization unless you add another index, widen the predicate, or refactor the query.
• If status is mutable and rows frequently transition open → closed, every transition removes or inserts an index tuple for partial structures that include that row. That can be exactly what you want (far fewer entries than a full composite index), or it can be painful if transitions are extremely frequent and the write path is already hot.

When partial indexes shine

Highly selective, stable predicates are the sweet spot: soft-deleted rows excluded from common queries, “active subscription” flags, workflow states that rarely oscillate, or multi-tenant tables where each tenant’s hot set is a small fraction of total rows. In consulting work on SaaS-style Postgres backends, partial indexes often appear in the second wave of performance work: the team already added broad composite indexes, measured bloat and write cost, then replaced them with tighter partial definitions tied to real traffic.

Trade-offs and limitations

• Constraint enforcement: a partial unique index can enforce uniqueness only within the slice (for example “only one PRIMARY email per user among non-deleted rows”). That is powerful, but it shifts complexity to application invariants you must document.
• Prepared statements and generic ORMs: dynamic filters can prevent the planner from recognizing that your query matches the partial predicate. Parameter sniffing and generic plans still apply; verify with EXPLAIN (ANALYZE, BUFFERS) on representative bindings.
• NULL semantics: if your predicate involves nullable columns, revisit SQL’s three-valued logic. A condition like WHERE deleted_at IS NULL is common and safe if consistently applied in queries.

Covering indexes and index-only scans

A covering index is one that contains all columns required to satisfy a query (either as key columns or as INCLUDE payloads), so PostgreSQL can satisfy the read without visiting the heap—when visibility allows an index-only scan.

Key columns vs INCLUDE

CREATE INDEX CONCURRENTLY idx_tickets_list_covering
  ON support_tickets (user_id, updated_at DESC)
  INCLUDE (title, priority, id)
  WHERE status = 'open';

• Key columns participate in ordering and search; keep them minimal and selective.
• INCLUDE columns are stored in the index leaf entries but do not define sort order. They widen the index but can eliminate heap fetches for list views that only need a handful of extra fields.

The visibility map caveat

Index-only scans are not “free reads” of the table: PostgreSQL must consult the visibility map to ensure entries are visible to your snapshot. If the heap page is not all-visible, you still get heap visits (often shown as Heap Fetches in EXPLAIN). Frequent updates to included columns reduce the visibility map’s usefulness and can silently push you back toward heap access.

Write amplification in plain language

Every indexed column participates in the cost of inserts, updates, and deletes that touch indexed values. INCLUDE columns are still indexed data on the leaf level; wide payloads mean wider pages, more I/O, and more WAL. The right mental model is trading read bytes for write bytes. That trade is excellent for read-heavy, append-mostly reporting slices; it is risky on write-heavy counters stored alongside wide INCLUDE blobs.

Reading query plans with intent

When evaluating a new index, do not stop at “it uses an index now.” Compare:

1. Access method: Index Scan vs Bitmap Index Scan vs Index Only Scan.
2. Filters applied: Index Cond vs Filter—conditions evaluated after the index range scan can still read many entries.
3. Buffers: EXPLAIN (ANALYZE, BUFFERS)—shared hit/read counts expose cache behavior that wall time alone hides.
4. Row estimates: persistent mis-estimates on skewed tenants often mean extended statistics or better constraints—not another index.

Partial indexes sometimes produce bitmap AND plans combining multiple indexes; that can be ideal for ad hoc analytics and awkward for latency-sensitive APIs where a single precise btree is preferable. The fix is not always “more indexes,” but query shape and statistics.

Practical example: a ticket inbox API

Imagine support_tickets(id, user_id, status, priority, title, updated_at, deleted_at) with heavy historical volume per user. The API returns open tickets sorted by updated_at for the authenticated user.

Baseline schema and query

-- Hot path used by the API
SELECT id, title, priority, updated_at
FROM support_tickets
WHERE user_id = $1
  AND status = 'open'
  AND deleted_at IS NULL
ORDER BY updated_at DESC
LIMIT 50;

Step 1: partial index aligned to the filter

CREATE INDEX CONCURRENTLY idx_inbox_open
  ON support_tickets (user_id, updated_at DESC)
  WHERE status = 'open' AND deleted_at IS NULL;

This keeps the index small if most rows are closed or deleted.

Step 2: cover the projection to target index-only scans

DROP INDEX CONCURRENTLY idx_inbox_open;

CREATE INDEX CONCURRENTLY idx_inbox_open_covering
  ON support_tickets (user_id, updated_at DESC)
  INCLUDE (id, title, priority)
  WHERE status = 'open' AND deleted_at IS NULL;

Step 3: validate under production-like snapshots

Run EXPLAIN (ANALYZE, BUFFERS) with realistic user_id values, including a large tenant and a small tenant. Compare Heap Fetches before and after autovacuum has marked pages all-visible. If fetches remain high due to churn on title or priority, reconsider what you include—maybe fetch wide text from the heap for 50 rows is acceptable if it stabilizes the write path.

Operational notes

• Prefer CREATE INDEX CONCURRENTLY on live systems to avoid long ACCESS EXCLUSIVE locks; still schedule during safer windows because builds are not free.
• Pair index changes with monitoring: replication lag, autovacuum backlog, index bloat (pgstatindex extensions or periodic checks), and p95/p99 for the route.

Common mistakes and pitfalls

• Predicate drift: ORMs generate slightly different SQL ((deleted_at IS NULL) = true, casts, or status = ANY(ARRAY[...])) that no longer matches the partial predicate. The index appears “unused” in pg_stat_user_indexes while latency regresses.
• Over-covering: stuffing large text or JSON blobs into INCLUDE to avoid joins increases WAL and vacuum cost, often negating read gains on write-heavy tables.
• Duplicate indexes: adding (user_id) and (user_id) WHERE open without dropping the broader index doubles maintenance work.
• Migration order: creating a huge covering index before backfilling data or pruning history can prolong builds and hold resources; sometimes partitioning or archival is the real fix.
• Ignoring the heap for correctness: index-only scans are still subject to MVCC visibility; if your code assumes “no heap read means no row lock interaction,” revisit transaction isolation semantics when coordinating with explicit locks.

Conclusion

Partial indexes let you index the working set, not the entire table history; covering indexes let you serve narrow projections from btree leaves when visibility maps cooperate. Together they are some of the highest-leverage tools in PostgreSQL performance work because they attack both CPU and I/O where queries are repetitive and well-structured.

The durable skill is not memorizing syntax—it is matching index definitions to real predicates, measuring write amplification, and validating plans under skewed tenants and churn. Those habits are what separate a quick index from a system that stays fast after the next feature wave—something worth investing in whether you are tuning your own platform or helping a team harden a production data model.