Production engineering guide for database indexing strategies covering patterns, implementation strategies, and operational best practices.
Database indexing is the practice of structuring data in a database to accelerate query performance. It matters when a system’s response time degrades under load, when queries return more data than expected, or when a single table grows beyond 100 million rows. This page details how to design, implement, and maintain effective indexing strategies across diverse workloads, with focus on real decisions, real trade-offs, and real failures.
Adding an index is not a one-time act; it’s a continuous process tied to query patterns and data growth. The decision to index should be driven by observed query latency, execution plan cost, and storage overhead. A query that runs in 120ms with a full table scan on a 50M-row table is a candidate for indexing when the index can reduce the time to 15ms. The trigger is a Seq Scan in the EXPLAIN output, especially when the cost exceeds 100 and the rows estimate is more than 10% of the table size.
To find these candidates, run:
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT *
FROM orders
WHERE customer_id = 12345
AND created_at >= '2023-01-01'
AND status = 'shipped';If the output shows a Seq Scan on orders with rows: 123456, cost: 1234.56, and actual time: 123.45, then a composite index on (customer_id, created_at, status) is likely to improve performance.
Indexing should also be triggered when a query becomes a bottleneck in the system’s profiling data — for example, when a dashboard page takes 2.1 seconds to load and the database query for customer orders accounts for 1.6 seconds.
Different index types serve different access patterns. The choice is not just about performance but about storage cost, write overhead, and query shape.
The default index in most relational databases, B-Tree indexes support equality, range, and partial match queries. They are ideal for queries on WHERE, JOIN, and ORDER BY.
Create a B-Tree index with:
CREATE INDEX idx_orders_customer_status
ON orders (customer_id, status)
WHERE status != 'draft';This index supports queries like:
SELECT * FROM orders
WHERE customer_id = 12345
AND status = 'shipped'
ORDER BY created_at DESC;B-Tree indexes are most efficient when the query’s leading column is highly selective (e.g., customer_id has 100,000 distinct values over 5 million rows).
Hash indexes are optimal for equality lookups, especially when the query filters on a single column with high cardinality.
CREATE INDEX idx_sessions_user_id
ON sessions USING HASH (user_id);They shine when the query is:
SELECT * FROM sessions
WHERE user_id = 12345;But they fail silently when used for range queries (user_id > 12345) or partial matches (LIKE 'user1%'). The query planner often falls back to a full index scan or a sequential scan, and the performance gain from the hash index is lost.
Partial indexes apply only to a subset of rows, reducing storage and improving query speed for frequently accessed data. Use them when a table has many rows but only a small fraction are active.
CREATE INDEX idx_active_orders
ON orders (created_at)
WHERE status IN ('shipped', 'delivered', 'processing');This index is used efficiently in queries filtering on status and created_at, but it is ignored when filtering on customer_id alone. The key failure mode: a query on orders with status = 'draft' does not use the partial index, even if it’s the most selective column.
A covering index includes all columns needed for a query, eliminating the need to fetch the actual table rows after index lookup.
CREATE INDEX idx_orders_covering
ON orders (customer_id, status, created_at, total_amount)
INCLUDE (id, shipping_address, tracking_number);This index covers queries like:
SELECT id, customer_id, total_amount, shipping_address
FROM orders
WHERE customer_id = 12345
AND status = 'shipped'
ORDER BY created_at DESC;The INCLUDE clause ensures that all needed columns are stored in the index, avoiding a Heap Fetch step. The trade-off: larger index size and more write cost during INSERT and UPDATE.
Composite indexes are not merely a list of columns. The order of columns determines their effectiveness and determines how much of the index is used.
For a query with filters on customer_id, status, and created_at, the index should be ordered by the most selective column first.
CREATE INDEX idx_orders_composite
ON orders (customer_id, status, created_at);This index supports:
WHERE customer_id = ? — uses the first column only.WHERE customer_id = ? AND status = ? — uses the first two columns.WHERE customer_id = ? AND status = ? AND created_at BETWEEN ? AND ? — uses all three.But if the query is:
SELECT * FROM orders
WHERE status = 'shipped'
AND created_at >= '2023-01-01';the index is not used efficiently — it does a Index Scan on status and created_at, but not customer_id, even if customer_id is indexed. The planner chooses the composite index, but the index’s leading column (customer_id) is not used.
The solution: reorder the index to match the query’s filter order.
CREATE INDEX idx_orders_status_created
ON orders (status, created_at, customer_id);Now, the query uses the index fully, and the WHERE and ORDER BY clauses are satisfied without additional table accesses.
Indexes consume storage and slow down INSERT, UPDATE, and DELETE. The cost of index maintenance must be balanced against query performance.
Over time, indexes grow larger than their ideal size due to page splits, deletions, and updates. This bloat increases I/O and reduces cache efficiency.
Check index bloat with:
SELECT
schemaname,
tablename,
indexname,
idx_scan,
idx_tup_read,
idx_tup_fetch,
pg_size_pretty(pg_relation_size(indexname)) AS size,
(1.0 - (idx_tup_read::double precision / idx_tup_fetch)) * 100 AS hit_ratio
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY size DESC;A hit_ratio below 70% indicates high bloat. The idx_tup_read is the number of index entries read, and idx_tup_fetch is the number of actual tuples fetched. A low ratio means many index entries were read but only a fraction were needed.
Rebuild indexes to reduce bloat:
REINDEX INDEX idx_orders_customer_status;For regular maintenance, schedule a VACUUM with FULL and ANALYZE:
pg_dump -h db.prod.example.com -U app -F c -v -f /backups/db.dump
pg_dump -h db.prod.example.com -U app -F c -v -s -f /backups/schema.dumpThen run:
VACUUM (FULL, ANALYZE, VERBOSE) orders;FULL rebuilds the table and index, and ANALYZE updates statistics. The VERBOSE flag provides detailed output, showing how many pages were processed and how long each step took.
In systems with frequent INSERT and UPDATE, index maintenance can dominate write time.
To reduce index overhead, consider:
INCLUDE columns to avoid table reads.Use CREATE INDEX CONCURRENTLY to avoid locking during index creation:
CREATE INDEX CONCURRENTLY idx_orders_user_time
ON orders (user_id, created_at)
WHERE status IN ('shipped', 'delivered');This command runs without locking the orders table, allowing reads and writes during the build. However, it fails silently if the table is dropped or altered during the build. The index is not usable until the CREATE INDEX completes, and the CONCURRENTLY flag does not guarantee immediate availability.
If the index build fails, the system continues using the old index, but the new one remains in a partially built state. The failure mode: a query uses the new index, but it is missing some data because the build process was interrupted.
A common failure is creating an index but forgetting to add it to the migration script or deployment pipeline. The index exists in dev, but not in staging or prod. The query is fast in development, but slow in production.
The fix: version the index creation in SQL or YAML migration files, and run pg_dump to verify the schema across environments.
Adding an index for every query leads to excessive storage and write cost. A table with 20 indexes may have a total size of 200GB, but only 40GB is used in practice.
The telltale sign: EXPLAIN shows Index Scan on multiple indexes, but the query planner chooses the wrong one.
Use pg_stat_user_indexes and pg_statio_user_indexes to track index usage:
SELECT
schemaname,
tablename,
indexname,
idx_scan,
idx_tup_read,
idx_tup_fetch,
idx_blks_read,
idx_blks_hit,
(idx_blks_hit::double precision / (idx_blks_read + idx_blks_hit)) * 100 AS hit_ratio
FROM pg_statio_user_indexes
ORDER BY idx_scan DESC;An index with idx_scan = 0 but idx_tup_read > 0 is being used but not scanned — a sign of underutilization.
The query planner often chooses an index that is not optimal. The most common cause: ANALYZE has not been run recently, so statistics are stale.
Run ANALYZE after bulk data loads or schema changes:
ANALYZE orders;
ANALYZE customers, orders, payments;The ANALYZE command collects statistics on column distribution, nulls, and most common values. Without it, the planner may choose a full scan over an index, even when the index would be more efficient.
For JOIN queries, the join condition column should be indexed on both tables. But the failure mode: one table has an index on customer_id, but the other does not.
-- This query benefits from customer_id index on both tables
SELECT o.id, o.total_amount, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'US';The planner uses the customer_id index on orders, but not on customers, resulting in a full scan on customers or a nested loop join.
The solution: create the index on customers:
CREATE INDEX idx_customers_country
ON customers (country);And ensure it is used by running EXPLAIN and checking for Index Scan on customers.
Database indexing is not just about creating indexes — it is about aligning them with query patterns, managing their cost, and catching silent failures. Use B-Tree indexes for general-purpose queries, hash indexes for equality lookups, and partial and covering indexes to reduce I/O. Order composite indexes to match query patterns. Maintain them with VACUUM, ANALYZE, and REINDEX. Watch for over-indexing, bloat, and stale statistics. Every index should have a story — a query it was built for, a cost it pays, and a failure mode it can hide.
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