Postgres Indexes Explained, Visually: btree, GIN, BRIN, GiST, Hash

Postgres ships seven index types out of the box. You will use five of them. Picking the right one is one of the few performance decisions where a small amount of knowledge produces a 100x speedup.

The five index types you need to know#

btree, hash, GIN, GiST, BRIN. Plus the bloom and SP-GiST extensions, which are situational.

btree: the default#

Balanced binary tree. Default for CREATE INDEX. Sorted traversal, equality and range queries, prefix matching for strings (with the right opclass). The right answer 80% of the time.

CREATE INDEX users_email_idx ON users (email);
-- Supports: WHERE email = ?, WHERE email > ?, ORDER BY email

Composite indexes: column order matters. The first column is queryable on its own; later columns help only when the earlier ones are also in the WHERE clause.

CREATE INDEX posts_tenant_status_idx ON posts (tenant_id, status);
-- Helps: WHERE tenant_id = ?
-- Helps: WHERE tenant_id = ? AND status = ?
-- Does NOT help much: WHERE status = ? (alone)

hash: rarely useful#

Faster than btree for exact-equality lookups; doesn't support ranges or sorts. In practice almost never the right pick because btree is already fast for equality and supports more. Skip unless you have a measured reason.

GIN: for arrays, jsonb, full-text#

Generalised Inverted Index. Indexes the "tokens" inside a composite value. Three big use cases:

• Array columns: WHERE tags @> ARRAY['rls'].
• jsonb columns: WHERE payload @> '{"status":"paid"}'.
• Full-text search: WHERE tsv @@ to_tsquery(...).

CREATE INDEX orders_payload_gin ON orders USING gin (payload jsonb_path_ops);
-- jsonb_path_ops is smaller and faster than the default; covers @> only

GIN indexes are slower to build and slower to update than btree. Don't put one on a high-write column unless you really need it.

GiST: for ranges and geometry#

Generalised Search Tree. Used for any type with an "is-within" or "overlaps" operator. Common applications:

• PostGIS geometry: WHERE location && bounding_box.
• Range types: WHERE valid_during && tstzrange(...).
• Trigram fuzzy matching: WHERE name % 'jonh' (yes, fuzzy).

BRIN: for time-series at scale#

Block Range INdex. Stores summary statistics per range of pages instead of per row. Tiny on disk; perfect for naturally-ordered columns like created_at on append-only tables.

CREATE INDEX events_created_brin ON events USING brin (created_at);
-- 1000x smaller than btree on a 100M-row table
-- Almost as fast for range queries on a column that's naturally sorted

BRIN is the right answer for time-series tables where the data is naturally clustered in insertion order (which Postgres preserves on append-only writes). On a randomly-ordered column, BRIN is useless.

Covering indexes#

Add an INCLUDE clause to put extra columns in the index leaf so the planner can avoid a heap fetch.

CREATE INDEX posts_author_idx
  ON posts (author_id)
  INCLUDE (title, status);
-- WHERE author_id = ? AND SELECT title, status
-- can be answered without touching the table heap

The trade-off: bigger index, slower writes (more bytes to update). Use sparingly for hot read queries.

Partial indexes#

Index only the rows that match a predicate. Smaller, faster, more targeted.

-- Indexes only the unarchived posts; archived ones aren't in the index
CREATE INDEX posts_active_idx
  ON posts (tenant_id, created_at DESC)
  WHERE archived = false;

Hugely effective when a large fraction of rows match a single predicate. Saves disk and write overhead.

Once you can read EXPLAIN ANALYZE fluently and pick the right index type, most performance problems in Postgres go from "hours of investigation" to "minutes of analysis followed by a single DDL".