Choosing PostgreSQL indexes: B-tree, GIN and BRIN

When a query is slow, the first reflex is usually to add an index on the relevant column. That is often the right move, but the index type matters as much as having one. The wrong type slows down writes and wastes disk space.

B-tree: the default, and usually enough

If you run CREATE INDEX without specifying a type, PostgreSQL creates a B-tree. It works well for equality and range queries (=, <, >, BETWEEN) and for ORDER BY.

CREATE INDEX idx_orders_created_at ON orders (created_at);

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE created_at >= now() - interval '7 days';

If the plan shows Index Scan using idx_orders_created_at, the index is being used. If you see Seq Scan, either selectivity is low or statistics are stale. In the second case, running ANALYZE orders; is usually enough.

To see why a B-tree lookup is so cheap, step through a search below. It starts at the root page and reads exactly one page per level on its way down to a leaf. A range query then follows the sibling links between leaf pages.

Searching a B-tree index. A real index page holds hundreds of keys; to keep the figure readable each page here holds at most 2 keys.

GIN: arrays, JSONB and full-text search

A B-tree is not suited to searching for elements inside a column. For a query like “rows whose tags array contains this tag”, use GIN:

CREATE INDEX idx_posts_tags ON posts USING gin (tags);

SELECT id FROM posts WHERE tags @> ARRAY['postgresql'];

GIN indexes are fast to read but expensive to write. On tables with frequent updates, measure the effect of the fastupdate setting.¹

Warning

Creating an index on a large table without CONCURRENTLY blocks writes to the table for the duration. In production, always use CREATE INDEX CONCURRENTLY.

BRIN: large, naturally ordered tables

On tables where the physical insert order tracks a timestamp, such as logs or events, BRIN gives a big win with a tiny index.

Index	Size for 100M rows	Range query
B-tree	~2.1 GB	12 ms
BRIN	~120 KB	38 ms

BRIN stores only a summary (minimum and maximum value) for every block of pages_per_range pages, so its size can be estimated roughly as:

\text{BRIN size} \approx \frac{N_{\text{pages}}}{\texttt{pages\_per\_range}} \times b_{\text{summary}}

With the default pages_per_range of $128$ , the index stays tiny relative to the table.

BRIN is a bit slower, but the size difference is three orders of magnitude. Its effectiveness drops quickly once the ordering breaks, for example after backfilling old data.

Summary

If in doubt, start with a B-tree and verify with EXPLAIN ANALYZE.
Use GIN for searching inside arrays and JSONB.
Try BRIN on very large tables ordered by insertion.

Footnotes

With fastupdate enabled, new entries are first written to a pending list and added to the index in batches. Writes get faster, but read queries can slow down as the list grows. The list size is capped by gin_pending_list_limit. ↩