PostgreSQL Index Types: B-tree, GIN, GiST, BRIN & More

Q: When should I use a GIN index?

When a single row holds multiple searchable values that you query into: JSONB columns (containment @, key lookups), arrays (@, &&), and full-text tsvector (@@). GIN indexes those internal elements so the query doesn't scan every row. It's the go-to for JSONB and full-text search; a B-tree can't serve those queries usefully.

Q: What are partial and covering indexes?

A partial index (... WHERE condition) indexes only the rows matching a condition — smaller and faster when you always query a subset (e.g. WHERE status = 'open'). A covering index (... INCLUDE (cols)) adds extra columns so a query can be answered entirely from the index (an index-only scan) without reading the table. Both are high-value modifiers layered on a base type, often more impactful than the type choice itself.

PostgreSQL Index Types

PostgreSQL ships far more than one kind of index. Where many databases give you essentially a B-tree, Postgres offers a toolbox — B-tree, GIN, GiST, BRIN, Hash, and SP-GiST — plus modifiers like partial, covering, and expression indexes. Each is built for a different data shape and query pattern, and matching the right index to the query is one of the highest-leverage things you can do for performance.

This is the Postgres-specific companion to general database indexing: the concepts (indexes trade write cost and storage for read speed) carry over, but which index to reach for is a Postgres decision. Most of the time the answer is a B-tree — but knowing when it isn't (JSONB, full-text, geospatial, huge append-only tables) is what separates a fast schema from a slow one.

TL;DR

B-tree is the default and right choice for the vast majority of cases — equality and range on ordered, comparable values.
GIN indexes "many values in one row": JSONB, arrays, and full-text tsvector.
GiST / SP-GiST handle geometric, spatial (PostGIS), range types, and nearest-neighbor queries.
BRIN is tiny and fast for very large tables where rows are naturally ordered by the column (append-only time-series).
Hash serves equality-only lookups; rarely worth choosing over B-tree today.
Modifiers matter as much as the type: partial (index a subset), covering (INCLUDE for index-only scans), and expression indexes (index a computed value).

Quick Example

The same table benefits from different index types depending on the column and query:

Each line targets a specific query shape — the skill is recognizing which one a query needs.

Core Concepts

The Index Types

B-tree is the workhorse — it supports =, <, >, BETWEEN, IN, sorting, and more, and is what you want unless your data is a special shape. GIN is the one you reach for with JSONB, arrays, and full-text search. BRIN is a specialist that shines on massive, naturally-ordered tables where a B-tree would be huge.

BRIN: Small Index, Big Tables

BRIN (Block Range INdex) stores only the min/max value per block range of the table, not per row — so it's orders of magnitude smaller than a B-tree. The tradeoff: it only helps when the column's values correlate with physical row order (e.g. an ever-increasing created_at on an append-only table). For a billion-row time-series table, a BRIN index on the timestamp is tiny and prunes huge swaths of the table; a B-tree on the same column would be enormous. When ordering doesn't correlate, BRIN is useless — so it's a targeted tool.

Index Modifiers

The modifiers are often more impactful than the base type:

Partial index — ... WHERE condition. Indexes only rows matching the condition, so it's smaller, cheaper to maintain, and faster. Perfect when you always query a subset (WHERE status = 'open', WHERE deleted_at IS NULL).
Covering index — ... INCLUDE (cols). Adds non-key columns to the index so a query can be answered from the index alone (an index-only scan), skipping the trip to the table (heap). Great when a query reads a few columns filtered by an indexed one.
Expression index — index a computed value: lower(email), (data->>'id'), date_trunc('day', ts). The query must use the same expression to benefit. Essential for case-insensitive lookups and querying into JSONB.
Multicolumn index — index several columns; column order matters (leftmost-prefix rule). (customer_id, created_at) serves WHERE customer_id = $1 [AND created_at ...] but not created_at alone.

Choosing the Right Index

A practical decision path:

Scalar column, equality/range, sorting → B-tree (default; stop here most of the time).
JSONB, array, or full-text column → GIN.
Geospatial (PostGIS), range types, nearest-neighbor → GiST.
Enormous table, column correlates with insert order → BRIN.
You always filter to a subset → add partial (WHERE ...).
A hot query reads a few columns by an indexed key → make it covering (INCLUDE).
You filter on a transformed value (lower(), JSON path) → expression index.

💡 Tip: Don't guess — verify. Create the index, run EXPLAIN ANALYZE, and confirm the planner actually uses it (and ideally does an index-only scan for covering indexes). An index the planner ignores is pure write overhead. See PostgreSQL Performance Tuning.

Best Practices

Default to B-tree; Specialize Deliberately

Reach for a non-B-tree index only when the data shape demands it (JSONB/array/FTS → GIN, spatial → GiST, giant ordered table → BRIN). Choosing an exotic index type without that need is a common misstep. Most performance wins are a well-placed B-tree, partial, or covering index.

Use Partial Indexes for Skewed Queries

When queries consistently target a slice of the table (active records, a single status, non-null values), a partial index is smaller, faster, and cheaper to maintain than a full index. It's one of the most underused, high-value Postgres features.

Order Multicolumn Indexes by Query Shape

Put the column used for equality first, then the range/sort column: (customer_id, created_at). The leftmost-prefix rule means the index serves queries filtering on customer_id (with or without created_at) but not created_at alone. Design the column order around your actual WHERE/ORDER BY.

Aim for Index-Only Scans on Hot Paths

For a frequently-run query that reads a few columns filtered by an indexed one, a covering index (INCLUDE) lets Postgres answer from the index without touching the table — a real speedup. Confirm the index-only scan in EXPLAIN.

Build Indexes Concurrently in Production

CREATE INDEX takes a lock that blocks writes; on a live table use CREATE INDEX CONCURRENTLY to build without blocking writes (it's slower and has caveats, but avoids downtime). Also drop unused indexes — they slow every write and waste space.

Common Mistakes

Reaching for GIN/BRIN Where B-tree Fits

Expression-Index Mismatch

Indexing Everything

Adding indexes on every column "just in case" slows every INSERT/UPDATE/DELETE (each index must be maintained) and wastes storage, while unused indexes never pay back. Index the columns your queries actually filter, join, and sort on — and remove ones the planner never uses.

FAQ

Which index type should I use by default?

B-tree. It handles equality, ranges, sorting, and most everyday query patterns on ordinary comparable columns, and it's what Postgres creates unless you specify otherwise. Only switch to a specialized type when your data shape requires it — GIN for JSONB/arrays/full-text, GiST for spatial and ranges, BRIN for very large naturally-ordered tables. When unsure, B-tree is almost always right.

When should I use a GIN index?

When a single row holds multiple searchable values that you query into: JSONB columns (containment @>, key lookups), arrays (@>, &&), and full-text tsvector (@@). GIN indexes those internal elements so the query doesn't scan every row. It's the go-to for JSONB and full-text search; a B-tree can't serve those queries usefully.

What's a BRIN index good for?

Very large tables where the column's values line up with physical row order — classically an append-only time-series with an ever-increasing timestamp. BRIN stores just min/max per block range, making it tiny compared to a B-tree while still pruning large portions of the table for range queries. If the column doesn't correlate with row order, BRIN won't help; it's a specialist for big, ordered data.

What are partial and covering indexes?

A partial index (... WHERE condition) indexes only the rows matching a condition — smaller and faster when you always query a subset (e.g. WHERE status = 'open'). A covering index (... INCLUDE (cols)) adds extra columns so a query can be answered entirely from the index (an index-only scan) without reading the table. Both are high-value modifiers layered on a base type, often more impactful than the type choice itself.

Why isn't Postgres using my index?

Common causes: the query filters on a different expression than the index (e.g. index on lower(email) but query on email); the column order in a multicolumn index doesn't match the query (leftmost-prefix rule); the table is small enough that a scan is cheaper; or statistics are stale. Run EXPLAIN ANALYZE to see the plan, and align the query with the index (same expression, right column order). See PostgreSQL Performance Tuning.