PostgreSQL Indexes Explained — B-Tree, GIN, BRIN, and GiST

What Is a PostgreSQL Index?

A PostgreSQL index is a separate on-disk structure that stores a sorted or otherwise organized copy of one or more column values, plus a pointer back to the matching table row. Instead of reading every row in sequence, called a sequential scan, the query planner can walk the much smaller index structure and jump directly to matching rows.

Unlike MySQL's InnoDB, PostgreSQL doesn't store table rows inside the primary key structure. Every PostgreSQL index, including one on the primary key, is a separate structure that points back into the table's heap. That's an architectural difference worth knowing if you're moving between the two engines — see the MySQL vs PostgreSQL comparison for more.

PostgreSQL's own documentation lists six built-in index access methods: B-tree, Hash, GiST, SP-GiST, GIN, and BRIN. Each targets a different indexable clause — equality, range, containment, proximity, or physical correlation — and picking the wrong one either wastes storage or leaves the planner unable to use the index at all.

Index Types: B-Tree, GIN, GiST, and BRIN

CREATE INDEX without a USING clause defaults to B-tree, and that default covers most schemas. The other three types below solve specific problems B-tree can't handle efficiently.

GIN stores one index entry per element rather than one per row — a JSONB document with 10 keys produces roughly 10 index entries. That's what makes containment queries like WHERE data @> '{"status": "active"}' fast, but it also means every write has to update potentially many entries, which is why GIN indexes are noticeably slower to maintain than B-tree.

BRIN takes the opposite approach: instead of indexing every row, it stores only a min/max summary for each range of physical table pages (128 pages by default). A query can skip entire page ranges whose summary doesn't overlap the search condition, without ever pointing to an individual row. That's why BRIN indexes are tiny compared to B-tree, but only useful when the indexed column's values roughly track the order rows were inserted — a created_at timestamp on an append-only log table is the textbook case.

The size gap looks like a pure win for BRIN, but it comes with a real trade-off: BRIN only stores page-range summaries, so a lookup still has to re-check every row inside a matching range. On data that isn't naturally ordered, that recheck cost erases the benefit entirely — BRIN is a bet on physical correlation, not a universal replacement for B-tree.

Creating and Managing Indexes

The base syntax is the same across all six index types — only the USING clause changes.

Default B-tree:

CREATE INDEX idx_orders_user ON orders (user_id);

GIN, for a JSONB column:

CREATE INDEX idx_orders_metadata ON orders USING GIN (metadata);

BRIN, for an append-only timestamp column:

CREATE INDEX idx_events_created_at ON events USING BRIN (created_at);

Unique index:

CREATE UNIQUE INDEX idx_users_email ON users (email);

On a live production table, always add CONCURRENTLY:

CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

A plain CREATE INDEX takes a lock that blocks writes for the entire build. CONCURRENTLY builds the index without that lock, at the cost of roughly double the build time and a requirement that it run outside a transaction block. If the build fails partway through, it can leave behind an invalid index — check pg_index.indisvalid and drop it before retrying.

PostgreSQL indexes - B-Tree, GIN, BRIN. What's the difference? (YouTube, 2025)

Composite, Partial, and Covering Indexes

A composite index spans multiple columns and follows the same leftmost-prefix rule as MySQL: an index on (user_id, status, created_at) serves queries filtering on user_id alone, on user_id and status, or on all three — but not on status or created_at alone, since neither is a leftmost prefix of the sorted structure.

CREATE INDEX idx_orders_lookup ON orders (user_id, status, created_at);

PostgreSQL adds two refinements MySQL doesn't have natively: partial indexes and covering indexes via INCLUDE. A partial index only indexes rows matching a WHERE condition, which keeps the index small when you only ever query a subset of rows:

CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

A covering index adds extra columns purely for retrieval, without making them part of the sort key, letting the planner satisfy a query entirely from the index without touching the table heap (an index-only scan):

CREATE INDEX idx_orders_covering ON orders (user_id)
INCLUDE (status, total);

Partial indexes are consistently underused in production schemas. A queue table where 95% of rows are already completed and only the pending rows are ever queried is exactly the case where a partial index cuts both the index size and the write overhead dramatically, since completed rows never enter the index at all.

When PostgreSQL Chooses a Sequential Scan

Having an index doesn't guarantee the planner uses it. PostgreSQL's cost-based optimizer estimates the cost of a sequential scan against the cost of an index scan for each query and picks whichever is cheaper — and for a large fraction of matching rows, a sequential scan usually wins.

An index scan is fastest when a query needs only a small fraction of a table's rows, since it avoids reading the rest of the table entirely. Once a query is estimated to return roughly 10% or more of a table's rows, the sequential scan's simpler, more predictable I/O pattern typically outperforms the random-access pattern of following an index across that many matches.

Small tables see the same effect for a different reason: if a table fits in a handful of pages, PostgreSQL just reads all of them, since the overhead of consulting an index isn't worth it. Why keep an index at all if the optimizer routinely ignores it? Because the row estimate that triggers a sequential scan today changes as the table grows — an index that looks unnecessary on a 500-row table becomes essential once that table hits 5 million rows.

Reading EXPLAIN ANALYZE Output

EXPLAIN shows the planner's chosen execution plan without running the query; EXPLAIN ANALYZE actually executes it and reports real elapsed time per plan node alongside the planner's estimate.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 AND status = 'shipped';

Look for Seq Scan versus Index Scan (or Bitmap Index Scan, used when the planner expects a moderate number of matches) in the plan's top line. A large gap between the estimated row count and the actual row count usually means the table's statistics are stale — run ANALYZE table_name; to refresh them.

To find indexes nobody's querying against, check pg_stat_user_indexes:

SELECT relname AS table_name, indexrelname AS index_name, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

An idx_scan of zero means the index has never been used by the planner since the statistics were last reset. As with MySQL's sys.schema_unused_indexes, confirm the observation window is long enough — check stats_reset in pg_stat_database — before dropping anything a monthly report query might still depend on.

Common Mistakes

• Using B-tree on a JSONB column you query for containment. A default B-tree index on a JSONB column can only support equality on the whole value. Use GIN for @>, ?, and similar containment operators.
• Skipping CONCURRENTLY on a production table. A plain CREATE INDEX blocks all writes to the table for the duration of the build, which on a large table can mean minutes of blocked INSERT and UPDATE statements.
• Choosing BRIN for shuffled data. BRIN only pays off when the indexed column correlates with physical row order. Applying it to a randomly-ordered column, like a UUID primary key, produces an index that can't meaningfully narrow a search.
• Wrong column order in a composite index. Just like MySQL, the leftmost column should match your most common filter condition. An index on (status, user_id) gives little benefit to queries that mostly filter on user_id.
• Never checking for unused indexes. Every index adds write overhead with zero benefit if the planner never selects it. Review pg_stat_user_indexes periodically, the same discipline covered in the MySQL indexes guide for sys.schema_unused_indexes.

Visualise Indexes Before Writing DDL

Index type decisions get easier once your schema's actual query patterns are visible. Marking which columns are foreign keys, which hold JSONB, and which are append-only timestamps is a design-time decision that's far clearer on a diagram than buried in a migration file.

You can design your schema visually and export the correct PostgreSQL DDL, including primary keys and foreign key indexes, straight from the diagram. See the PostgreSQL data types guide for choosing the right column type before you decide which index method fits it, and the database schema examples post for complete indexed schemas across common application types.