Postgres Index Types: B-Tree vs GIN vs GiST

PostgreSQL supports several index types, and picking the right one matters because they’re built for fundamentally different kinds of queries — a B-tree can’t efficiently answer “does this array contain this value,” and a GIN index is the wrong tool for sorting a range of numbers. Understanding what each index structure is actually good at is the difference between an index that gets used and one that silently sits unused while your query does a sequential scan anyway.

B-tree: the default, and usually the right call

CREATE INDEX without specifying a type creates a B-tree, and for good reason: it handles equality and range comparisons (=, <, >, BETWEEN) efficiently, supports sorted output for ORDER BY without an extra sort step, and works on nearly any comparable data type — integers, text, timestamps, UUIDs. If you’re indexing a primary key, a foreign key, or a column you filter and sort on, a B-tree is almost always correct.

Where B-trees fall short is anything that isn’t a simple ordering comparison: searching inside an array, matching a substring anywhere in a text column, checking whether a JSONB document contains a key, or finding rows whose geometric region overlaps a point. For those, Postgres has purpose-built index types.

GIN: generalized inverted index

A GIN (Generalized Inverted Index) is built for columns that contain multiple values per row — arrays, JSONB documents, and full-text search vectors — where the query asks “which rows contain this value” rather than “which rows come before this value.” Instead of one entry per row like a B-tree, GIN stores one entry per distinct value, each pointing to every row that contains it — an inverted index, the same structure a search engine uses.

This makes GIN the right choice for:

• Full-text search over a tsvector column, where each document decomposes into many searchable lexemes.
• JSONB containment queries (@>), checking whether a document contains a given key or key-value pair — see what a document/JSON-style query looks like in practice.
• Array membership (@>, &&), checking overlap or containment between arrays.
• Trigram matching for fuzzy or substring text search via the pg_trgm extension, when you need LIKE '%term%'-style queries to actually use an index.

The tradeoff is write cost: GIN indexes are more expensive to update than B-trees because inserting or updating a row with many distinct component values (a long array, a big JSON document) means touching many index entries, not one. GIN is optimized for read-heavy, write-light tables — it’s a poor fit for a column that’s updated constantly.

GiST: generalized search tree

A GiST (Generalized Search Tree) index is a more flexible, extensible tree structure that supports a broader and looser family of queries than a B-tree’s strict ordering — most notably overlap, containment, and nearest-neighbor queries where “less than” and “greater than” don’t cleanly apply.

GiST is the standard choice for:

• Geometric and spatial data — points, boxes, polygons — where queries ask about overlap or containment rather than strict ordering. This is the foundation PostGIS builds on for geographic queries.
• Range types (int4range, tsrange, and similar), for questions like “which reservations overlap this time window.”
• Nearest-neighbor search, using the <-> distance operator to find the closest rows to a point — a relative of the approximate nearest-neighbor search problem that vector databases solve at larger scale, though GiST (and its relative, SP-GiST) handle it natively inside Postgres for many practical workloads.
• Exclusion constraints, enforcing that no two rows can have overlapping ranges — useful for booking systems that must prevent double-booked time slots.

GiST indexes are lossy in some configurations, meaning they can return a superset of matching rows that Postgres then double-checks against the actual row data — a reasonable tradeoff for the flexibility they provide.

Comparison

	B-tree	GIN	GiST
Best for	Equality, ranges, sorting	Multi-valued columns: arrays, JSONB, full-text	Overlap, containment, nearest-neighbor, ranges
Typical use case	Primary keys, foreign keys, WHERE/ORDER BY	Full-text search, @> on JSONB/arrays, trigram search	Geometric/spatial data, range types, exclusion constraints
Read performance	Excellent	Excellent for its query shapes	Good, sometimes lossy (requires recheck)
Write/update cost	Low	Higher — many entries per row	Moderate
Supports sorted output	Yes	No	No (typically)

How to know which one you actually need

The query shape tells you the index type, not the data type. A jsonb column filtered with ->>'status' = 'active' (extracting one scalar field) can use a plain B-tree on an expression index; the same column queried with @> '{"status": "active"}' (containment) needs GIN. A timestamp column is almost always B-tree, unless you’re checking whether one time range overlaps another, in which case a range type with GiST fits better than manual start_time/end_time comparisons.

When in doubt, check what Postgres is actually doing: EXPLAIN ANALYZE shows whether a query uses an index at all and which one, which is the fastest way to confirm a hunch about index selection rather than guessing — a habit worth building alongside understanding how query optimizers make these decisions in the first place. It’s also worth checking whether a query can be satisfied by a covering index that includes every column the query needs, avoiding a second lookup into the table entirely — a technique that applies to B-tree indexes specifically.

The takeaway

B-tree is the right default for equality, range, and sort queries and should be your first choice unless a query shape clearly doesn’t fit it. GIN earns its higher write cost on multi-valued columns — arrays, JSONB, and full-text search — where you’re asking “which rows contain this,” while GiST handles the looser world of overlap, containment, and nearest-neighbor queries that B-tree’s strict ordering can’t express. Match the index to the query pattern, not just the column’s data type, and verify with EXPLAIN ANALYZE rather than assuming an index is being used.