Master database indexing beyond the basics. Covers B-tree, hash, GIN, GiST, partial indexes, covering indexes, and composite index design for production workloads.
Orientation In high‑traffic database applications, indexes are the primary lever the query planner uses to avoid full table scans. When traffic spikes, poorly designed or missing indexes become the bottleneck that propagates through connection pools, replication lag, and tail latency. This reference page maps the practical decisions you face when indexing for volume: how to match an index to a query shape, how to add one without taking the system offline, how to keep them lean as data grows, and what breaks when you get the pattern wrong. The following sections are organized by the task you’re trying to complete, with concrete commands, flags, and the failure modes that catch teams off guard.
Task: Speed up a specific query that appears in the hot path of your application.
Concrete diagnosis: run EXPLAIN ANALYZE SELECT id, total FROM orders WHERE customer_id = 123; in psql. If the output includes Seq Scan on orders with Actual total time notably higher than Planned total time, the planner is not using an index for this query.
Create a B‑tree index matching the equality column:
CREATE INDEX idx_orders_cust ON orders (customer_id);Composite index for equality + range:
CREATE INDEX idx_orders_cust_dt ON orders (customer_id, created_at);Leftmost prefix rule: this index also accelerates queries that filter on customer_id alone, or on both columns in order. A query filtering only on created_at will not use this index; a separate index on (created_at) is needed.
Expression index for computed ranges:
CREATE INDEX idx_orders_yr ON orders ((DATE_TRUNC('year', created_at)));The planner can scan this for WHERE DATE_TRUNC('year', created_at) = '2024-01-01'.
Partial index for filtered subsets:
CREATE INDEX idx_active_orders ON orders (id) WHERE status = 'active';Only queries containing WHERE status = 'active' (exact predicate) will consider this index. A query using WHERE LOWER(status) = 'active' will not match the partial condition and the planner falls back to a sequential scan.
Task: Select the structural index type that matches your data access pattern without introducing unnecessary write amplification.
B‑tree (the default): supports equality (=) and range predicates (<, >, BETWEEN, LIKE with a leading substring). CREATE INDEX idx ON t (c); — no vendor‑specific flag; this is the native B‑tree implementation exposed through psql and most RDBMS clients.
Hash: supports exact equality only; disallows range scans and ORDER BY. In systems that expose it via psql‑compatible clients, the syntax is CREATE INDEX idx ON t (c) USING HASH;. Use case: primary‑key lookups where the workload is strictly key‑value.
GIN (inverted‑index pattern): for arrays, JSONB containment, and full‑text. CREATE INDEX idx_tags ON t USING GIN (tags); supports the @> operator: SELECT * FROM t WHERE tags @> ARRAY['foo']. Does not accelerate scalar comparisons.
Covering index pattern: include non‑key columns at the leaf level so the query is satisfied entirely from the index without touching the heap. CREATE INDEX idx_covering ON orders (customer_id) INCLUDE (total, shipping_address); — available in servers that support the INCLUDE clause (exposed through psql). The listed columns are stored at the leaf level. Query SELECT total FROM orders WHERE customer_id = 42 then reads only the index.
Task: Add a new index or rebuild an existing one in a live, high‑traffic production environment with zero downtime.
Concurrent build syntax:
CREATE INDEX CONCURRENTLY idx_name ON table_name (column_list);The CONCURRENTLY keyword requests that the index construction proceed without acquiring an exclusive lock on the target table. Reads and writes continue uninterrupted.
Exact error if a concurrent transaction holds a reference:
ERROR: could not create index concurrently because there are existing concurrent transactions that reference the tableExact error if disk/WAL space is insufficient:
ERROR: could not create index concurrently: could not extend indexDuration: concurrent builds typically require considerably longer than a non‑concurrent build, because they must scan the table multiple times and avoid locking the relation.
Rebuilding an existing index:
REINDEX CONCURRENTLY idx_name;Creates a new index version alongside the old one, then atomically swaps the catalog reference. Same lock‑free guarantee; fails with the same concurrent‑transaction error if long‑running statements are in flight.
In RDBMS that do not support concurrent DDL natively, teams layer online‑schema‑migration tools. The underlying pattern is: spawn a new table, stream changes, rename. The SQL‑level equivalent varies by vendor, but the CONCURRENTLY flag where available is the lightweight in‑server option.
Task: Keep indexes compact and fast as data volume grows and update patterns evolve.
Monitoring query:
SELECT idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE relname = 'orders';idx_scan counts how many times the index was used; idx_tup_read is rows fetched from the index; idx_tup_fetch is rows fetched from the heap after index lookup. A high ratio idx_tup_read / idx_tup_fetch suggests the index is being scanned broadly (possible bloat or poor selectivity) or the index is being used for full table scans.
Reindex a single index:
REINDEX INDEX idx_name;Builds a fresh copy of the index, replacing the old one. Takes an exclusive lock on the index itself; concurrent writes to the table that touch the index are blocked only for the duration of the reindex, which is typically shorter than a table‑level lock.
Reindex the entire table:
REINDEX TABLE table_name;Takes an exclusive lock on the whole table, blocking all reads and writes. Must be scheduled during a maintenance window or low‑traffic period.
Concurrent reindex:
REINDEX CONCURRENTLY idx_name;Available in servers that support online DDL. Constructs a new index version in the background without an exclusive lock, then atomically replaces the catalog entry. Fails with the same concurrent‑transaction error if long‑running statements reference the table.
VERBOSE flag:
REINDEX INDEX VERBOSE idx_name;Adds progress logging to stderr or the psql output. Does not change locking behavior; the operation still runs to completion.
Index size check:
SELECT pg_index_size('idx_name');Returns the disk space currently occupied by the index. Compare against relation_size('table_name') to gauge bloat.
Fillfactor:
CREATE INDEX idx ON t (c) WITH (FILLFACTOR);Reserves a portion of each page for future updates, reducing the frequency of page splits when rows are updated in place. Without a fillfactor, every in‑place update can split a page, gradually increasing index depth and bloat.
Planner cardinality misestimation: when statistics_target is low, EXPLAIN may show Estimated rows far from Actual rows. Fix: ALTER TABLE SET statistics_target = 1000; followed by ANALYZE. Without this, the planner may choose a nested loop join that spills to disk, adding considerably higher latency per row.
Expression index on a volatile function: CREATE INDEX idx_rnd ON t ((random())); the planner will not consider this index for any query because the expression result cannot be materialized consistently across scan tuples. If you need a generated column, add a persisted column and index that instead.
Partial index condition mismatch: index CREATE INDEX idx_active ON t (id) WHERE status = 'active'. A query WHERE UPPER(status) = 'ACTIVE' does not satisfy the partial predicate; the planner ignores the index and performs a sequential scan. Always mirror the exact WHERE clause text when creating a partial index.
Write amplification from excessive indexes: each INSERT, UPDATE, or DELETE modifies every index defined on the table. A table with many indexes can see write latency increase substantially under load, offsetting read‑speed gains. After any schema change, monitor pg_stat_user_indexes.idx_tup_read trends; if write latency climbs without corresponding read improvement, prune the least‑used index.
Composite index column order anti‑pattern: CREATE INDEX idx_b_a ON t (b, a). A query WHERE a = 5 does not use this index because the leading column b is not constrained. The planner follows the leftmost prefix rule: the first column in the definition must be the one most frequently filtered in your query workload. Swap the order to CREATE INDEX idx_a_b ON t (a, b); and the planner will use it for WHERE a = 5 alone, or WHERE a = 5 AND b = 7.
UUID primary key without temporal ordering: inserts of random UUIDs cause page splits at the leaf level, driving index depth growth and random I/O. In high‑traffic workloads this shows as increasing idx_tup_read and deeper B‑tree traversal. Pattern: prefer time‑sorted identifiers (e.g., uuid_generate_v7()), or append a sequential serial, or use INSERT … ON CONFLICT DO NOTHING with an ORDER BY that batches inserts. If you must use random UUIDs, set an appropriate fillfactor on the index to mitigate split frequency.
Silent index corruption: if an index page is torn (power loss, hardware fault), the planner may return rows that do not satisfy the predicate, or skip rows entirely. Usually this manifests as ERROR: corrupted index tuple during REINDEX, but partial corruption can persist for many requests, producing incorrect query results without any error. After major write bursts or hardware events, run REINDEX on critical indexes and verify with EXPLAIN ANALYZE.
Task: When an index exists but the planner still chooses a suboptimal plan.
Primary diagnostic:
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;The output includes Actual time, Actual rows, Actual loops, Planned time, Planned rows. If Planned rows = 10 and Actual rows = 10000, the cost model is miscalibrated.
Stale statistics: run ANALYZE orders; then re‑issue EXPLAIN ANALYZE. If the numbers align, the issue was outdated statistics. If they remain diverge, consider ALTER TABLE SET (statistics_target = 5000); and ANALYZE again.
Bitmap index scan: SET enable_bitmapscan = on; (psql session flag) activates bitmap‑based index scanning, which collects row identifiers from multiple index scans into a bitmap before fetching heap tuples. Effective when a query hits multiple indexes or a high‑selectivity range across many rows. Default is on in most recent server versions, but toggling it off can confirm whether the plan improves when disabled.
Parallel index scan workers: max_parallel_index_scan_workers controls how many worker processes the server uses to scan an index in parallel. On a many‑core machine, increasing this from the default (typically 2) can reduce query latency for wide‑range scans. Set via ALTER SYSTEM SET max_parallel_index_scan_workers = 4; and reload with SELECT pg_reload_conf();.
Covering index vs. additional index: if a query fetches only columns present in an existing index, the planner may choose an index‑only scan. Verify with EXPLAIN ANALYZE; you’ll see Index Only Scan instead of Index Scan. If the query references columns not in the index, the planner will revert to a standard scan plus heap fetch.
Never rely on EXPLAIN without ANALYZE; the former shows the planner’s estimated cost, not actual runtime behavior. Always pair with ANALYZE to see real row counts and timing.
This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.
We use cookies for analytics (Google Analytics) and advertising (Google AdSense) to improve your experience and support free content. Privacy Policy