PostgreSQL Index Types: B-tree, GIN, GiST, BRIN and When to Use Them

The short answer

Choose an index by the operator in your WHERE clause, not by the column type:

There are also Hash (equality only) and SP-GiST (non-balanced partitioning, for example for IP addresses or points), but the four above cover most real cases.

B-tree: the default for a reason

A B-tree keeps keys sorted, so it answers both “find exactly” and “find a range” and can return rows already ordered, skipping a sort.

• In a multi-column index the column order matters: (customer_id, created_at) serves WHERE customer_id = ? ORDER BY created_at, but not a filter on created_at alone.
• LIKE 'abc%' uses a B-tree only with the C collation or the text_pattern_ops operator class. LIKE '%abc%' never does.
• Low-selectivity columns (a boolean flag) rarely benefit from a B-tree on their own.

GIN: for “what is inside”

GIN splits each value into elements — jsonb keys and values, array items, words of a document — and stores a list of rows for each element.

CREATE INDEX ON orders USING gin (attributes jsonb_path_ops);
-- serves: WHERE attributes @> '{"delivery": "express"}'

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ON products USING gin (name gin_trgm_ops);
-- serves: WHERE name ILIKE '%telefon%'

• jsonb_path_ops is smaller and faster for @> but does not support key-existence operators like ?. The default jsonb_ops supports more operators.
• GIN reads are fast, writes are more expensive: one row updates many index entries. The fastupdate pending list softens this, but makes some searches slower until it is merged.

GiST: overlaps, geometry and “nearest”

GiST is a framework: each node stores a predicate that covers its children (for example, a bounding box). It answers “what overlaps” and “what is closest”.

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE bookings (
  room_id int,
  during  tstzrange,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

This exclusion constraint makes double booking of a room impossible at the database level. GiST also powers PostGIS geometry and ORDER BY location <-> point LIMIT 10 nearest-neighbour searches. For full text, GiST is smaller and cheaper to update than GIN, but usually slower to search.

BRIN: tiny indexes for huge tables

BRIN stores only the minimum and maximum value for each range of table pages. If rows are physically written in the order of the column — created_at in an append-only events table — PostgreSQL skips all ranges that cannot contain matching rows.

CREATE INDEX ON events USING brin (created_at);

A BRIN index can be orders of magnitude smaller than a B-tree on the same column. It is useless when the column is not correlated with physical order, for example after many updates or with random UUIDs.

Partial, expression and covering indexes

• Partial: index only the rows you query. CREATE INDEX ON orders (created_at) WHERE status = 'pending'; stays small while most orders are completed.
• Expression: index the result of a function. CREATE INDEX ON users (lower(email)); works only if the query uses exactly lower(email).
• Covering: CREATE INDEX ON orders (customer_id) INCLUDE (total); lets PostgreSQL answer from the index alone (index-only scan), provided the visibility map is up to date — which depends on vacuum.

Finding unused indexes

Every index slows writes and takes space. Check statistics:

SELECT relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan, pg_relation_size(indexrelid) DESC;

Before dropping an index with idx_scan = 0:

• check all replicas — statistics are kept per server;
• find out when statistics were last reset;
• do not drop indexes behind primary keys and unique constraints;
• look for duplicates: an index on (a) is usually redundant next to (a, b).

Always confirm with EXPLAIN (ANALYZE, BUFFERS) that the planner really uses the new index.