Indexes in PostgreSQL: B-tree, GIN, and GiST

An index is a separate data structure that lets PostgreSQL find rows matching a condition without scanning the entire table. Choosing the right index type for the right kind of query and data is one of the highest-leverage skills in database work — the wrong index (or no index) can turn a millisecond query into one that takes seconds.

PostgreSQL supports several index types, each optimized for a different access pattern. The three you will use most often are B-tree (the default, for equality and ranges), GIN (for values containing multiple elements, like JSONB and arrays), and GiST (for geometric, range, and full-text search scenarios).

Why it matters

PostgreSQL has several kinds of index, each built for a different sort of question. A B-tree is useless for searching inside a JSON document, and a GIN index is useless for sorting by date. Picking the right type is the difference between an index that helps and one that is never used.

B-tree: The Default, General-Purpose Index

CREATE INDEX idx_name ON table(column); creates a B-tree index by default. B-tree indexes excel at equality (=), range comparisons (<, >, BETWEEN), and sorting (ORDER BY), which covers the vast majority of everyday query filtering. Primary keys and unique constraints are automatically backed by a B-tree index.

GIN: Indexing "Contains Multiple Values" Data

A Generalized Inverted Index (GIN) is built for columns where each value effectively contains many searchable "elements" — JSONB documents, arrays, and full-text search vectors (tsvector). GIN builds an index entry per element rather than per row, which is exactly what makes containment queries like @> on JSONB or && (overlap) on arrays fast. GIN indexes are slower to update than B-tree but much faster for these containment-style lookups.

GiST: Indexing Geometric and Range-Like Data

A Generalized Search Tree (GiST) index supports more exotic notions of "nearest" or "overlapping" that a strict ordering (like B-tree) cannot express well — geometric data (points, boxes), range types (tsrange, int4range), and full-text search are common use cases. GiST is also the index type behind PostgreSQL's exclusion constraints, which can enforce, for example, that no two date ranges in a bookings table overlap.

Choosing the Right Index

As a practical rule: use B-tree by default for ordinary WHERE/ORDER BY columns; reach for GIN when the column is JSONB, an array, or a full-text tsvector and you filter with containment operators; reach for GiST for geometric/range data or exclusion constraints. Always verify an index is actually being used with EXPLAIN — an unused index still costs write performance and disk space.

Examples

CREATE INDEX idx_products_price ON products(price);

EXPLAIN SELECT * FROM products WHERE price < 500;

                        QUERY PLAN
------------------------------------------------------------
 Index Scan using idx_products_price on products
   Index Cond: (price < '500'::numeric)

CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

EXPLAIN SELECT * FROM products WHERE attributes @> '{"ram_gb": 16}';

                          QUERY PLAN
---------------------------------------------------------------
 Bitmap Heap Scan on products
   Recheck Cond: (attributes @> '{"ram_gb": 16}'::jsonb)
   ->  Bitmap Index Scan on idx_products_attrs

Exercise

This exercise uses PostgreSQL index types, so it needs a PostgreSQL server. For the events table, create three indexes: a B-tree index for filtering and sorting by created_at; a GIN index so that containment queries on the JSONB column payload are fast; and a partial index on user_id that covers only unprocessed events.

Expected result: the query planner can use the first for date ranges, the second for payload @> queries and the third for finding a user's unprocessed events.

CREATE TABLE events (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id BIGINT NOT NULL,
  payload JSONB NOT NULL,
  processed BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- TODO: B-tree index on created_at

-- TODO: GIN index on payload

-- TODO: partial index on user_id for rows where processed is false

CREATE TABLE events (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id BIGINT NOT NULL,
  payload JSONB NOT NULL,
  processed BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_events_created_at ON events (created_at);

CREATE INDEX idx_events_payload ON events USING GIN (payload);

CREATE INDEX idx_events_unprocessed ON events (user_id) WHERE processed = false;

Quick quiz

Interview questions