Quick answer
A composite index stores multiple key columns in a defined order. Design that order from representative equality predicates, range predicates, joins, and required ordering—not from a generic “most selective first” rule. A covering PostgreSQL index adds non-key payload columns with INCLUDE, allowing an index-only scan when the access method can return the values and heap pages are marked all-visible. A partial index stores only rows satisfying a predicate and helps only when the planner can prove the query predicate implies that condition.
For an order-history query filtering one tenant and status, constraining recent creation time, and ordering newest first, an index such as (tenant_bucket, status, created_at DESC, id DESC) can align equality keys with the ordered range and its stable tie-breaker. Adding small projected values through INCLUDE may reduce heap access. A partial index for a stable, common business subset can be useful, but a moving-time predicate, parameter shape, or status mismatch may make it unusable.
Every index is a workload trade. It consumes storage and cache, generates WAL, increases write and maintenance work, and can prevent heap-only tuple updates. Validate plans and user outcomes for dominant, typical, and rare cases; measure concurrent writes; examine overlapping indexes; and deploy through a bounded change procedure. A plan selecting the index once is not sufficient acceptance evidence.
Shared order-history incident stage
The shared order-history API uses tenant_bucket = ?, status = ?, a lower bound on created_at, descending order, and a limit. Production currently has separate indexes on tenant and creation time. After flash-sale skew and a backfill, the planner underestimates the popular tenant/status combination. It chooses work that visits or sorts many more rows than expected.
The team’s first reaction is “add an index,” but the incident demands a precise access contract. A tenant-only index finds too many rows and filters status and time later. A time-only index can walk recent entries but discard many other tenants. An index with an unfortunate column order might not efficiently satisfy the leading predicates or desired ordering. A very wide covering index could improve this read while materially increasing order-write cost.
The long reporting transaction also delays visibility cleanup. Even a correctly covering index may perform heap fetches because index-only execution must verify MVCC visibility unless the visibility map says the heap page is all-visible. This connects indexing to MVCC, VACUUM, and Table Bloat, rather than treating the plan node name as a guarantee.
Any production build must account for lock and resource behavior. CREATE INDEX CONCURRENTLY permits ordinary writes during much of the operation, but it performs multiple phases, waits for transactions, consumes I/O and WAL, and can leave an invalid index after failure. The incident remains open until write health, pool wait, maintenance, and the user SLI recover.
Core mechanism and evidence boundary
PostgreSQL B-tree multicolumn indexes are ordered lexicographically. Equality constraints on leading columns plus an inequality on the first following column can bound the scanned portion most directly. Constraints on later columns may be checked within the index and can reduce heap visits even when they do not reduce the entire scanned range. PostgreSQL may use skip scan in suitable cases, but that cost-based behavior does not erase the need to validate the actual workload.
Column order serves more than selectivity. It determines which query prefixes can navigate the structure, whether requested ordering is available, how ranges behave, and whether other workload families can reuse the index. Put stable equality dimensions such as tenant and status before the ordered time range when that matches the approved query family. Validate variants; a query without the leading tenant boundary has a different safety and access profile.
INCLUDE columns are payload, not search keys. They can let the executor return projected values from the index. PostgreSQL index-only scans still require MVCC visibility checks. When a heap page is not all-visible, the executor visits it, and Heap Fetches exposes the effect. Wide payloads enlarge the index and can exceed index tuple limits. Payload changes also increase maintenance and may inhibit HOT updates if indexed values change.
A partial index has a fixed predicate, such as rows with a stable finite status. PostgreSQL can use it when the query condition implies that predicate at planning time. It does not behave like a runtime filter with arbitrary parameters. Parameterized clauses and logically similar but syntactically unprovable conditions may not use it. Avoid time-relative predicates such as “last 30 days” in a fixed index definition; immutability and moving membership make that model unsuitable.
Ordering details belong in the contract. PostgreSQL B-tree indexes can be scanned forward or backward, and multicolumn direction choices matter when requested columns mix ascending and descending order. Null placement can also affect whether an index supplies the required order. Record the exact application ORDER BY, including its deterministic tie-breaker, before encoding directions. A fast first page is not enough if duplicate timestamps cause unstable pagination.
Operator classes and collations determine which comparisons an index supports. Text search under one collation, pattern matching, JSON access, and specialized data types can need different access methods or operator classes. Do not generalize the B-tree example to every predicate. An index that exists but cannot support the query operator is not evidence of a planner defect.
Constraints change removal decisions. A unique index may enforce a business invariant or back a primary/unique constraint even if scan counters appear low. Included columns are not uniqueness keys in a unique covering index. Inventory dependencies and validate duplicate behavior before replacing or dropping anything.
This article describes PostgreSQL 18 behavior. INCLUDE, partial-predicate implication, concurrent-build phases, and visibility-map details are PostgreSQL-specific, not a SQL standard or universal database interface. shared_blks_read, heap fetches, index sizes, and write latency are evidence fields, not a stable OpenTelemetry semantic convention. Keep query text, tenant values, order IDs, and query identifiers out of metric labels.
Minimal reproducible PostgreSQL 18 example
This focused index experiment reuses the synthetic orders fixture defined in Database Query Performance for Backend Systems. Use a disposable database with skewed synthetic orders. Begin with the representative query and a non-executing plan:
EXPLAIN (COSTS, VERBOSE, FORMAT JSON)
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_bucket = 7
AND status = 'PAID'
AND created_at >= TIMESTAMPTZ '2026-08-01 00:00:00+00'
ORDER BY created_at DESC, id DESC
LIMIT 100;Create a workload-shaped candidate in staging:
CREATE INDEX orders_tenant_status_created_idx
ON orders (tenant_bucket, status, created_at DESC, id DESC)
INCLUDE (total_cents);
ANALYZE orders;Compare plans and executed read evidence under a bounded read-only transaction. Confirm the ordered limit, actual rows, loops, buffers, and heap fetches. Then test tenant-only, status-only, wider date, rare status, and no-match cases. An index optimized for one case must not silently authorize an unbounded query variant.
For a stable subset, a partial candidate might be:
CREATE INDEX orders_active_tenant_created_idx
ON orders (tenant_bucket, created_at DESC, id DESC)
INCLUDE (total_cents)
WHERE status IN ('PENDING', 'PAID');Test the exact application statement, including prepared execution. A query using status = $2 may not allow the planner to prove the partial predicate for every possible parameter. Do not rewrite application semantics merely to force index use.
Production construction needs a reviewed runbook:
SET lock_timeout = '500ms';
SET statement_timeout = '30min';
CREATE INDEX CONCURRENTLY orders_tenant_status_created_idx
ON orders (tenant_bucket, status, created_at DESC, id DESC)
INCLUDE (total_cents);Timeouts are examples, not universal values. Monitor pg_stat_progress_create_index, locks, WAL, replicas, storage, CPU, I/O, write tails, and index validity. A timed-out or failed build needs explicit inspection and cleanup; never repeatedly retry it blindly.
Failure modes and dangerous misconceptions
Using “most selective first” as the complete design. It ignores equality prefixes, ranges, ordering, joins, projections, and reusable workload shapes. Derive order from queries and verify plans.
Adding every projected column with INCLUDE. Wide indexes consume cache and storage, increase WAL and write cost, and may exceed tuple-size limits. Include only stable, valuable payload after measurement.
Assuming index-only means heap-free. Visibility checks can cause heap fetches. Vacuum health and update patterns influence whether the index-only opportunity materializes.
Creating a partial index for a moving window. A fixed predicate does not continuously redefine “recent.” Use partition lifecycle, query design, or another reviewed mechanism.
Assuming a parameter implies a partial predicate. The planner must prove implication for the planned statement. Test the application’s prepared path.
Keeping redundant indexes. Overlapping indexes multiply writes and maintenance. Compare constraints, prefixes, ordering, operator classes, predicates, payload, and actual usage before removal.
Calling concurrent construction harmless. It reduces some write blocking, but still waits, scans, writes, and can fail. Budget capacity and inspect invalid results.
Dropping an index after a short quiet window. Usage counters reset and seasonal jobs may be absent. Combine long-enough observation, code/search ownership, constraints, and rollback planning.
Security, privacy, capacity, and cost implications
Index definitions reveal access patterns and sometimes sensitive business categories. Partial predicates can expose statuses or authorization partitions. Plans may include literal PII or personal data. Restrict catalog and plan access, use synthetic examples, sanitize artifacts, and audit DDL privileges.
Never put tenant, user, order, query, request, trace, PID, or raw predicate values in metric labels. Aggregate index-build and query health by bounded environment, database role, operation, and approved index class. Preserve detailed object names and query fingerprints only in restricted operational logs where access and retention are controlled.
Capacity costs include index bytes, cache displacement, WAL, replica transfer and replay, checkpoints, vacuum work, CPU, I/O, build scratch space, and longer backup or restore. Unique and exclusion semantics add correctness constraints that cannot be dropped for speed. Measure steady-state writes as well as build-time impact.
Operational complexity is also cost. Every specialized index needs ownership, reason, supported query family, creation procedure, failure cleanup, observation window, and removal criteria. Prefer the smallest set that protects validated journeys.
Testing and production validation
Build a literal workload matrix with dominant, typical, rare, empty, and broad predicates. Validate returned rows, ordering, pagination ties, authorization scope, and status semantics. Capture plan estimates, actual rows, loops, buffers, heap fetches, sort/spill, duration, planning time, and index size.
Run concurrent read/write load. Compare insert, update, delete, and vacuum behavior; WAL volume; replica lag; CPU; I/O; cache; pool wait; and user tails. Change included columns in fixtures to see whether HOT opportunities and update cost differ. Test after vacuum and after write churn rather than relying on a freshly built index.
For partial indexes, test statement preparation exactly as the application sends it. Verify matching and nonmatching status classes. For concurrent builds, rehearse success, timeout, cancellation, uniqueness failure where applicable, invalid-index detection, and cleanup in staging.
After rollout, confirm the intended query family uses bounded work without requiring a fixed plan shape. Watch other workloads for regressions. Keep the previous index until evidence and storage budget justify removal, then use a separate reviewed change. Close only when the user SLI, writes, maintenance, replicas, and capacity remain healthy.
Test data growth by appending newer rows and changing status skew. An index can be ideal while a range is narrow and become costly when callers widen it. Re-run the workload matrix after maintenance and after churn so the validation includes realistic visibility-map coverage and page distribution. Define a future review trigger based on user or resource evidence, not an arbitrary promise that the index is permanent.
Operations checklist
- Inventory representative predicates, joins, ordering, projection, constraints, and write rates.
- Confirm cardinality and plan evidence before proposing an index.
- Design leading equality keys, the first useful range/order key, and minimal payload.
- Test query variants that omit or broaden leading predicates.
- Verify index-only heap fetches under realistic update and vacuum conditions.
- Prove partial-predicate implication for the actual prepared statement.
- Compare existing indexes for overlap, constraints, usage, and seasonal ownership.
- Measure index bytes, WAL, cache, writes, checkpoints, vacuum, backups, and replicas.
- Rehearse concurrent-build progress, lock timeout, statement timeout, failure, invalid state, and cleanup.
- Keep sensitive literals and identifiers out of plans, exports, and metric labels.
- Validate production user tails and write health before considering old-index removal.
- Document ownership, supported query family, review date, and removal criteria.
Official sources
- PostgreSQL 18: Multicolumn Indexes — column constraints and skip-scan behavior; accessed 2026-08-04.
- PostgreSQL 18: Index-Only Scans and Covering Indexes —
INCLUDE, visibility, and heap access; accessed 2026-08-04. - PostgreSQL 18: Partial Indexes — predicate implication and parameter limitations; accessed 2026-08-04.
- PostgreSQL 18: Building Indexes Concurrently — phases, waits, restrictions, and invalid indexes; accessed 2026-08-04.
- PostgreSQL 18: Monitoring CREATE INDEX Progress — operational progress fields; accessed 2026-08-04.
Continue the learning path
Use PostgreSQL EXPLAIN and Query Plans to verify access work and Query Planner Statistics and Cardinality Estimation to correct the model that chooses it. MVCC, VACUUM, and Table Bloat explains why index-only behavior changes under churn. Review the foundation in Database Indexes, then continue through System Design or Topics.