Index Design In Detail
An index in PostgreSQL is a separate, ordered structure that sits alongside a table and gives the planner a faster path to specific rows than reading the whole table.
Designing indexes well is less about memorizing CREATE INDEX syntax and more about understanding what an index actually is internally, how the planner decides whether to use it, and what it costs you every time a row changes.
This page builds that mental model so the more tactical pages in this section, on column ordering, redundancy, and reindexing, make sense as consequences of the same underlying structure rather than as separate rules to memorize.
Summary
- Core Idea: An index is a sorted, separately maintained structure that lets the planner locate matching rows without reading the entire table.
- Why It Matters: Without an index, every filtered query costs a full table scan, and that cost grows linearly with table size.
- Key Concepts: B-tree, index scan, leftmost prefix, heap access, index-only scan, visibility map.
- When to Use: Designing a new index requires knowing the query shapes it must serve, not just which columns "seem important."
- Limitations / Trade-offs: Every index adds write overhead, storage, and vacuum work, and an unused or redundant index is pure cost with no benefit.
- Related Topics: query planner statistics, EXPLAIN plan reading, autovacuum and bloat, access-method internals.
Foundations
The default and most common index type in PostgreSQL is the B-tree, a balanced tree structure that keeps its keys in sorted order and lets the planner find any key, or a contiguous range of keys, in a small, bounded number of page reads.
Think of it the way you'd think of a phone book sorted by last name: finding "Smith" doesn't require reading every page, because the sorted structure lets you jump directly to the right neighborhood and scan a small local range.
That analogy also explains the single most important rule of composite B-tree indexes: they only help you efficiently locate values by a leftmost prefix of their key columns, in order.
An index on (last_name, first_name) serves a lookup on last_name alone perfectly well, the same way a phone book sorted by last name still lets you find all the Smiths, but it cannot efficiently serve a lookup on first_name alone, because first names are scattered throughout the sorted order.
A plain index entry stores the indexed column values plus a pointer, called a TID, back to the row's physical location in the table's heap.
Because of that pointer, most index scans are actually two-step operations: the planner walks the index to find matching TIDs, then follows each pointer back into the heap to fetch the rest of the row.
That second step, called heap access, is where a lot of an index scan's real cost hides, because heap pages are not necessarily stored in the same order as the index, so following many pointers can mean many scattered, non-sequential reads.
PostgreSQL also supports non-B-tree index types built for different data shapes: GIN for containment queries over JSONB or arrays, GiST and its relatives for geometric and nearest-neighbor search, and BRIN for extremely large, naturally ordered tables like time-series data.
Each of those exists because a B-tree's sorted, prefix-matching model simply doesn't fit every query shape, not because they're strictly "better" indexes.
Mechanics & Interactions
Creating an index does not force the planner to use it; it only adds a new candidate to the set of access paths the planner will cost against every other option, including a plain sequential scan.
Whether the planner actually chooses your new index comes down entirely to the cost model discussed in this site's planner pages: the estimated selectivity of your query's predicate, combined with random_page_cost and the table's size, determines whether the scattered heap access of an index scan beats simply reading the table straight through.
This is why an index on a low-selectivity column, one that matches a large fraction of rows, is frequently ignored by the planner even though it technically "could" be used, because a sequential scan is genuinely cheaper once you'd otherwise be following pointers back to most of the table anyway.
The visibility map introduces a second major optimization path: if every row on a heap page is known to be visible to all transactions, PostgreSQL can sometimes skip heap access entirely and answer a query using only the index, provided every column the query needs is present in the index itself.
That optimization is called an index-only scan, and it's the reason the INCLUDE clause exists, letting you carry extra payload columns in the index leaf pages purely to satisfy this shortcut without making those columns part of the sortable key.
Partial indexes interact with this same cost model from a different angle: by adding a WHERE clause to the index definition itself, you shrink the index to only the rows that actually matter for a specific recurring query pattern, which lowers both its size and the cost the planner assigns to scanning it.
Every one of these benefits has a mirrored cost on the write side, because an index is not a read-only artifact; it is a live structure that must be kept correct on every INSERT, UPDATE, and DELETE that touches an indexed column.
An UPDATE that changes an indexed column typically requires PostgreSQL to insert a new index entry, and depending on whether the update qualifies for the heap-only-tuple optimization, that write amplification can multiply across every index defined on the table.
Over time, as rows are updated and deleted, B-tree pages accumulate dead entries, a condition generally called index bloat, which grows the index's on-disk size and slows scans until vacuum reclaims the space or a rebuild compacts it.
-- Two indexes, two very different jobs from the same B-tree structure:
-- one satisfies WHERE + ORDER BY together, the other narrows to a hot subset.
CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at DESC);
CREATE INDEX orders_open_idx
ON orders (customer_id) WHERE status = 'open';Advanced Considerations & Applications
At scale, index design becomes a portfolio problem rather than a per-query decision, because every index you add to serve one query pattern adds ongoing write and vacuum cost to every write against that table, whether or not that query ever runs again.
Teams that add an index per ad-hoc slow query without periodically auditing usage tend to accumulate redundant or overlapping indexes, where a three-column composite index makes a separate single-column index on its leading column entirely superfluous.
CREATE INDEX CONCURRENTLY exists specifically for production systems, building the index without holding the exclusive lock a plain CREATE INDEX would take, at the cost of a slower build and a small risk of needing a retry if it's interrupted mid-build.
High-write, insert-only tables with monotonically increasing keys, like time-series or event-log tables, often favor BRIN indexes over B-tree, because BRIN stores only coarse per-block ranges instead of one entry per row, trading precision for a dramatically smaller structure that costs far less to maintain.
Specialized workloads push index design further still: pgvector's approximate nearest-neighbor indexes trade exact recall for query speed on similarity search, and PostGIS's spatial indexes rely on GiST to make bounding-box containment queries tractable at all, neither of which a B-tree could serve.
Index maintenance is not a one-time decision either, since REINDEX CONCURRENTLY exists precisely because bloat and page-split fragmentation accumulate over the life of a busy index, and periodically rebuilding a heavily-churned index can shrink it and restore predictable scan performance.
| Index Type | Strength | Weakness | Best Fit |
|---|---|---|---|
| B-tree | Equality, range, sort, leftmost-prefix composite lookups | Only useful via key prefix; large on high-cardinality wide keys | The default choice for almost all scalar filter/sort columns |
| GIN | Fast containment on JSONB, arrays, full-text | Slower, larger writes than B-tree | @>, array membership, full-text search columns |
| GiST/SP-GiST | Geometric, range-overlap, nearest-neighbor queries | Slower than B-tree for simple equality | PostGIS geometries, range types, similarity search |
| BRIN | Tiny footprint on huge, naturally ordered tables | Useless if data isn't physically ordered by the key | Append-only time-series or log tables |
Common Misconceptions
- "Adding an index guarantees the planner will use it." - The planner only chooses an index scan when its estimated cost beats every other candidate plan for that specific query.
- "More indexes always make queries faster." - Every index adds write and vacuum overhead, and an index that never gets scanned is pure cost.
- "Column order in a composite index doesn't matter, only which columns are included." - B-tree composite indexes only serve efficient lookups on a leftmost prefix, so order is often the whole design decision.
- "An index scan never touches the table itself." - Most index scans still follow a pointer back into the heap; only index-only scans, and only when the visibility map cooperates, avoid that step.
- "A unique index and a UNIQUE constraint are different mechanisms." - A UNIQUE constraint is implemented using a unique index under the hood; they are the same structure with a constraint declared on top.
- "Once built, an index stays optimally efficient forever." - Ongoing updates and deletes fragment and bloat B-tree pages, which is why maintenance operations like REINDEX exist as a routine, not a rescue.
FAQs
What does a B-tree index actually store?
It stores the indexed column values in sorted order, plus a pointer (TID) back to each matching row's location in the table's heap.
That sorted structure is what lets PostgreSQL locate a value, or a range of values, without scanning every row.
Why doesn't a composite index on (a, b) help a query that only filters on b?
A B-tree composite index is sorted primarily by its first column, so values of the second column are scattered throughout, not grouped together.
Only a query that filters on a leftmost prefix of the key columns can use the index efficiently for that purpose.
Does creating an index force PostgreSQL to use it?
No, the index only becomes one more candidate access path that the planner costs against alternatives like a sequential scan.
Low-selectivity predicates in particular are often served more cheaply by a sequential scan even when a matching index exists.
What is an index-only scan and why is it faster?
It's a scan that answers a query using only the index's own data, skipping the second step of following pointers back into the heap.
It requires every column the query needs to already be present in the index, and depends on the visibility map confirming the relevant heap pages don't need a fresh visibility check.
Why would I add columns to an index using INCLUDE instead of just adding them to the key?
INCLUDE columns ride along in the index's leaf pages to enable index-only scans without making them part of the sortable, searchable key.
Keeping the key itself narrow keeps the index smaller and the leftmost-prefix matching rules simpler to reason about.
Why does every index slow down writes?
Any INSERT, UPDATE, or DELETE that touches an indexed column must also update that index's own B-tree structure to stay correct.
A table with many indexes multiplies this cost across every one of them on every relevant write.
What is index bloat and where does it come from?
Bloat is the accumulation of dead or half-empty space inside a B-tree's pages as rows are updated and deleted over time.
It grows the index's on-disk size and can slow scans until vacuum reclaims space or a rebuild compacts the structure.
When should I reach for a partial index instead of a full one?
When a query pattern consistently filters to a stable, well-known subset of rows, such as status = 'open'.
A partial index only indexes that subset, which shrinks both its size and its maintenance cost relative to indexing the whole table.
Why would I ever choose BRIN over a B-tree?
BRIN stores coarse summary ranges per block instead of one entry per row, making it far smaller and cheaper to maintain on very large tables.
It only works well when the indexed column is naturally correlated with physical row order, such as an append-only timestamp.
Is a UNIQUE constraint a different mechanism from a unique index?
No, declaring a UNIQUE constraint creates a unique index behind the scenes, and both enforce and serve lookups through the same structure.
There is no separate constraint-checking mechanism apart from that index.
Why would two indexes on the same table be considered redundant?
If one index's key columns are a strict leftmost prefix of another's, the shorter index rarely earns its own maintenance cost.
The planner can generally satisfy the shorter index's use cases using the longer composite index instead.
Does building an index always block writes to the table?
A plain CREATE INDEX takes a lock that blocks concurrent writes for its duration.
CREATE INDEX CONCURRENTLY avoids that exclusive lock at the cost of a slower build and a small chance of needing a retry if interrupted.
Related
- Index Design Basics - the hands-on recipe for matching indexes to query shapes
- Multicolumn Index Order - the leftmost-prefix rule applied in practice
- Duplicate & Redundant Indexes - auditing the portfolio-cost problem described here
- REINDEX & REINDEX CONCURRENTLY - repairing bloat once it accumulates
- Seq Scan vs Index Scan - how the planner actually chooses between these access paths
Stack versions: This page was written for PostgreSQL 18.4 (stable line 18, maintenance line 17), with GIN/GiST/BRIN behavior and index-only scan mechanics stable across recent major versions; pgvector 0.8+ and PostGIS 3.5+ are referenced for their specialized index types.