PostgreSQL Indexing for Application Developers: A Practical Guide

How a B-tree index works and what it can answer

A PostgreSQL table stores rows in the heap, in no particular order. Without an index, finding rows that match a condition means a sequential scan that reads every page. A B-tree index is a separate structure that holds the indexed column values as sorted keys, each paired with a tuple identifier (TID) that points to the row's location in the heap. Internal pages route the search, and leaf pages hold the keys and are linked to their neighbours in sort order.

That model explains which questions a B-tree can answer:

• Equality (=): descend the tree to the matching key and follow its TIDs.
• Ranges (<, <=, >, >=, BETWEEN): descend to the lower bound, then walk the linked leaves until the upper bound is passed.
• IN (...): a set of equality lookups against the same sorted structure.
• IS NULL / IS NOT NULL: B-tree stores NULLs, sorted at one end of the key order.
• Left-anchored LIKE 'abc%': this is a range of strings that begin with abc. The planner can only use it when the index ordering is plain byte order, which means the C collation or an index built with text_pattern_ops or varchar_pattern_ops. A pattern like '%abc' has no fixed starting point in the sort order, so a B-tree cannot answer it.
• ORDER BY: the keys are already sorted, so reading the leaves forward or backward can return rows in order and skip a separate sort step.

An index is therefore part of schema design. It reflects the queries your application actually runs, and the developers who write those queries are in the best position to choose and maintain it.

Copy-- Example table: rows live in the heap in no particular order.
CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint      NOT NULL,
    shipped_at  timestamptz,           -- NULL until the order ships
    created_at  timestamptz NOT NULL,
    email       text        NOT NULL
);

-- Default B-tree: sorted keys with TIDs pointing into the heap.
CREATE INDEX orders_created_at_idx ON orders (created_at);
CREATE INDEX orders_shipped_at_idx ON orders (shipped_at);

-- Byte-order opclass so left-anchored LIKE can use the index
-- even when the database collation is not C.
CREATE INDEX orders_email_pattern_idx ON orders (email text_pattern_ops);

-- Range: descend to the lower bound, walk leaves to the upper bound.
EXPLAIN SELECT id FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';

-- ORDER BY on the indexed column: rows come out pre-sorted, no Sort node needed.
EXPLAIN SELECT id, created_at FROM orders
ORDER BY created_at DESC
LIMIT 20;

-- IS NULL: B-tree stores NULLs at one end of the key order.
EXPLAIN SELECT id FROM orders WHERE shipped_at IS NULL;

-- Left-anchored pattern: a range of strings starting with 'alice'.
EXPLAIN SELECT id FROM orders WHERE email LIKE 'alice%';

-- No fixed starting point in the sort order: B-tree cannot help here.
EXPLAIN SELECT id FROM orders WHERE email LIKE '%@example.com';

-- Note: on small or freshly created tables the planner may still pick a
-- sequential scan; run ANALYZE on realistic data before reading plans.

Creates B-tree indexes on an orders table and uses EXPLAIN to check range, ORDER BY, IS NULL and left-anchored LIKE queries that the sorted key structure can serve, alongside a suffix LIKE pattern that a B-tree cannot answer.

Reading EXPLAIN and EXPLAIN (ANALYZE, BUFFERS)

EXPLAIN is how you check whether an index does what you designed it to do. Plain EXPLAIN shows the planner's chosen plan with estimated costs and row counts. EXPLAIN (ANALYZE, BUFFERS) also runs the query and reports actual row counts, timings, loops, and buffer usage.

Start by comparing estimated rows with actual rows at each node. A gap of an order of magnitude or more usually means the planner is working from stale or insufficient statistics, and the plan was chosen for a query that isn't the one you ran. Actual rows are averaged per loop, so multiply by loops before deciding whether a nested-loop inner side is cheap. In the buffer counts, shared hit means the page was already in PostgreSQL's buffer cache. read means the page came from outside that cache, either the OS page cache or disk. A plan that looks fast on hits can be slow on reads.

ANALYZE executes the statement for real. Run any INSERT, UPDATE, or DELETE you are testing inside BEGIN and end it with ROLLBACK.

Don't trust plans from a laptop. With a small table or a warm cache, a sequential scan can look as good as an index scan, or the planner may skip the index entirely. Plan choice depends on data volume, value distribution, and cache state, so test on data that is close to production in size and skew.

Copy-- Read-only query: plain EXPLAIN shows estimates only and does not run it
EXPLAIN
SELECT id, status FROM orders WHERE customer_id = 4821;

-- ANALYZE runs the query; BUFFERS adds shared hit/read counts per node
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status FROM orders WHERE customer_id = 4821;
-- Check on each node:
--   rows=<estimate> vs actual rows=<n>: a 10x or larger gap points to stale stats
--   loops=<n>: actual rows is per loop, so multiply by loops for the total
--   Buffers: shared hit=<cached> read=<from OS cache or disk>
--   Index Only Scan: a high Heap Fetches count means the visibility map is stale

-- If estimates are far off, refresh statistics and check the plan again
ANALYZE orders;

-- DML under test: ANALYZE really executes it, so wrap it and roll back
BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders
SET status = 'shipped'
WHERE customer_id = 4821 AND status = 'paid';
ROLLBACK;  -- undoes the update; the timings and plan are still valid

-- Run these against a production-sized copy: on a small or warm-cache table
-- a Seq Scan may win, or every page may show as a hit, which hides real I/O.

This example checks an index with EXPLAIN and EXPLAIN (ANALYZE, BUFFERS), shows what to read at each node, and runs a test UPDATE inside BEGIN/ROLLBACK so ANALYZE doesn't change any data.

Why the planner ignores your index

When an index you expected is missing from the plan, the cause usually falls into one of five categories. The planner does not refuse indexes arbitrarily. It estimates the cost of each path, so every cause below is either a case where the index really is more expensive or a case where the index cannot answer the predicate as written.

Low selectivity matters because each index match costs a random heap fetch. Once a predicate matches a large share of rows, reading the table sequentially wins.

Stale statistics deserve the most attention because they cause the worst plans. Bulk loads and large deletes can outpace autovacuum's analyze step. The planner then picks a plan for a table that no longer exists.

A B-tree stores values of one type, ordered under one collation. Anything that changes the type or collation of the column at query time makes the stored ordering unusable for that predicate.

Copy-- Function on the column: a plain index on email cannot answer lower(email).
CREATE INDEX users_email_idx ON users (email);
EXPLAIN SELECT * FROM users WHERE lower(email) = 'ada@example.com';
--   Seq Scan on users  Filter: (lower(email) = 'ada@example.com'::text)

-- Fix: index the same expression the query uses.
CREATE INDEX users_email_lower_idx ON users (lower(email));

-- Type mismatch: an integer column compared to a numeric literal gets cast.
EXPLAIN SELECT * FROM orders WHERE customer_id = 42.0;
--   Filter: ((customer_id)::numeric = 42.0)
-- Fix: pass a value of the column's type so the B-tree ordering applies.
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- Collation: under a non-C collation, LIKE 'abc%' cannot use a default B-tree.
-- text_pattern_ops compares character by character, which supports prefix matching.
CREATE INDEX users_email_prefix_idx ON users (email text_pattern_ops);
EXPLAIN SELECT * FROM users WHERE email LIKE 'ada%';

-- Stale statistics: compare estimated rows with actual rows.
EXPLAIN ANALYZE SELECT * FROM events WHERE status = 'failed';
--   ... (cost=... rows=12 ...) (actual time=... rows=480000 ...)
-- Fix: refresh statistics after bulk loads or large deletes.
ANALYZE events;

-- For skewed columns, sample more values, then re-analyze.
ALTER TABLE events ALTER COLUMN status SET STATISTICS 1000;
ANALYZE events;

-- Low selectivity: index only the rare values the query actually targets.
CREATE INDEX events_failed_idx ON events (created_at) WHERE status = 'failed';

Each cause of an ignored index shown with the EXPLAIN pattern to look for and its fix: expression indexes, matching parameter types, text_pattern_ops for prefix LIKE, ANALYZE with a higher statistics target, and a partial index for rare values.

Designing indexes around real queries: composite, expression, partial, and covering

Composite indexes

Order columns by how queries filter: equality predicates first, then the one range or sort column. An index on (tenant_id, status, created_at) serves tenant_id = ?, tenant_id = ? AND status = ?, and tenant_id = ? AND status = ? ORDER BY created_at DESC LIMIT 20 through its leftmost columns. Reverse the order and the range on created_at comes first. Every later column then has to be checked as a filter instead of narrowing the scan. The planner often skips that index, but every insert and update still pays to maintain it.

Expression and partial indexes

An index on lower(email) is used only when the query contains the same expression. Plain email = ? or email ILIKE ? will not match it. A partial index such as WHERE deleted_at IS NULL or WHERE status IN ('pending', 'retrying') stays small, but the planner must prove that the query's WHERE clause implies the index predicate. ORM upgrades break this quietly. A predicate that becomes a bind parameter (status = $1) cannot be proven under a generic plan, and neither can a rewritten condition. Pin these queries in tests that check the plan.

Covering indexes

INCLUDE adds payload columns so the planner can choose an index-only scan. The heap is skipped only for pages marked all-visible in the visibility map, which VACUUM maintains. Watch Heap Fetches in EXPLAIN ANALYZE. On tables with heavy updates and lagging autovacuum, the covering index saves little.

Foreign keys

PostgreSQL indexes the referenced key, not the referencing column. Without an index on orders.customer_id, joins on that column are slower. Each delete or key update on customers also scans orders to check for referencing rows.

Copy-- Composite index: equality columns first, then the sort/range column.
-- Serves tenant_id = ?, tenant_id + status, and the paginated query below.
CREATE INDEX orders_tenant_status_created_idx
  ON orders (tenant_id, status, created_at DESC);

SELECT id, total_cents FROM orders
WHERE tenant_id = 7 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;

-- Expression index: the query must use the same expression, lower(email).
CREATE INDEX users_email_lower_idx ON users (lower(email));
SELECT id FROM users WHERE lower(email) = lower('ana@example.com');

-- Partial index: the literal predicate lets the planner prove the index applies.
-- A bind parameter such as deleted_at IS NULL hidden behind $1 logic would not.
CREATE INDEX accounts_live_slug_idx ON accounts (slug)
  WHERE deleted_at IS NULL;
SELECT id FROM accounts WHERE slug = 'acme' AND deleted_at IS NULL;

-- Foreign key column: PostgreSQL does not index orders.customer_id for you.
-- INCLUDE makes it covering, so this lookup can be an index-only scan.
CREATE INDEX orders_customer_id_idx ON orders (customer_id)
  INCLUDE (total_cents, created_at);

-- Index-only scans skip the heap only for all-visible pages.
-- VACUUM updates the visibility map; then check Heap Fetches in the plan.
VACUUM (ANALYZE) orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, total_cents, created_at
FROM orders
WHERE customer_id = 42;

-- On a live production table, prefer CREATE INDEX CONCURRENTLY
-- (it cannot run inside a transaction block).

Composite, expression, partial, and covering indexes written to match specific queries, including an index on the foreign key column and an EXPLAIN check for heap fetches after VACUUM.

When to avoid adding an index

Every index is a write-path cost paid on each change to the table. An INSERT must add an entry to every index on the table. An UPDATE usually does the same. A DELETE leaves dead index entries for vacuum to clean up. Each of those index modifications is also WAL-logged, so more indexes mean more WAL to write, archive, and ship to replicas.

Updates carry an extra cost. PostgreSQL can perform a HOT (heap-only tuple) update, which skips index maintenance entirely, but only when no indexed column changes and the new row version fits on the same heap page. Indexing a column that changes often, such as updated_at, status, or a counter, blocks HOT for those updates. Every update then writes new entries into every index, and both the table and its indexes bloat faster.

Indexes also take up memory. Their pages compete with table data for shared_buffers and the OS page cache, so a large index that is rarely useful can push out hot table pages and slow queries that never touch it.

Index-only scans depend on the visibility map. On write-heavy tables where autovacuum falls behind, fewer pages are marked all-visible, so the scan goes back to the heap for most rows and loses its advantage.

Under sustained update and delete churn, indexes bloat. Track their size over time and schedule REINDEX CONCURRENTLY (available since PostgreSQL 12), which rebuilds an index without blocking writes for the duration of the rebuild.

Changing indexes safely in production and pruning the unused ones

Plain CREATE INDEX takes a lock that blocks inserts, updates, and deletes on the table for the whole build. On a large, busy table that can turn into an outage. CREATE INDEX CONCURRENTLY avoids the long lock but costs more. It scans the table twice and waits for every transaction that could still see the old state of the table before it finishes. One long-running transaction can stall the build indefinitely. DROP INDEX CONCURRENTLY likewise waits for in-flight queries to finish instead of taking an exclusive lock.

Neither command can run inside a transaction block. Many migration tools wrap each migration in BEGIN/COMMIT by default, so you need to turn that off for these migrations. Put each concurrent statement in its own non-transactional migration so that a failure does not leave the other steps partly applied.

Recovering from a failed build

If a concurrent build fails, for example because of a deadlock or a duplicate key in a unique index, PostgreSQL leaves the index behind marked INVALID. The planner ignores it, but every write still has to maintain it. To find these indexes, query pg_index for rows where indisvalid is false. Remove each one with DROP INDEX CONCURRENTLY, then run the build again.

Pruning

Indexes with idx_scan = 0 in pg_stat_user_indexes are candidates for removal. Check three things before dropping one:

• Unique and primary-key indexes: they enforce constraints even if no query ever scans them.
• Statistics resets: counters go back to zero after pg_stat_reset() or a crash. Confirm the counts cover a full business cycle, including month-end jobs.
• Replicas: each node keeps its own counters, so check every replica before dropping anything.

An index is also redundant when its columns are a leftmost prefix of another index. For example, (a) is usually covered by (a, b). Keep it anyway if it is unique or partial, because the wider index does not provide those properties.

Alternatives to B-tree and the impact on cost and engineering time

B-tree is the default because it handles equality, ranges, sorting and prefix matching. Some data shapes fit other access methods better, because each one stores a different kind of summary.

Every index you keep adds work on each write, adds storage, adds vacuum work, and needs maintenance. Designing indexes alongside the schema and checking them with EXPLAIN (ANALYZE, BUFFERS) before release replaces repeated tuning during incidents with one review.

Illustrative estimate (assumed numbers, not a benchmark):

Extra index writes per day = row writes/sec × 86,400 × unused indexes

Assume 500 row writes/sec and 3 unused indexes. That gives 500 × 86,400 × 3 = 129,600,000 index-entry writes per day that no query benefits from. Plug in your own write rate from pg_stat_user_tables and your unused-index count from pg_stat_user_indexes.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgresql indexing, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Adding or removing a column goes through one review with the inheriting services listed, instead of ad-hoc messages to each team.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• Services that depend on a column get the chance to object before it disappears, avoiding emergency fixes.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.