B-tree vs Hash vs GIN vs GiST
B-tree is the default for equality + range + ORDER BY. Hash is equality-only (niche). GIN shines for arrays/JSONB/full-text (inverted). GiST powers geometry, ranges, and trigram similarity. Pick the access method by operator class, not vibes.
- 1Gist
- 2Maps
- 3Q&A
- 4Sandbox
Voice readout needs Web Speech Synthesis in this browser.
Overview
Postgres index types are different access methods (AMs). They are not flavors of the same B-tree. The planner can only use an index if some operator class supports the operator in your WHERE (=, contained-by, &&, @@, %).
By the end you should be able to:
- Match a predicate to B-tree, hash, GIN, or GiST
- Know what hash cannot do
- Say why GIN likes
@>and&&on arrays - Pick GiST for overlap / KNN, GIN for inverted membership
- Mention BRIN as the huge-append-only cousin
JSONB contains vs btree on a text column
Prefer
GIN on jsonb (jsonb_ops or jsonb_path_ops)
`WHERE data @> '{"sku":"A"}'` is an inverted lookup. A B-tree on `data::text` cannot seek that containment.
- GIN: many keys per row (keys, array elems, lexemes).
- jsonb_path_ops is smaller but `@>`-centric. jsonb_ops supports more operators.
- Writes pay a pending list / fastupdate — measure ingest.
Alternative
Default B-tree on the JSON blob, or hash on sku extracted in app
B-tree compares whole values. Hash is equality on one scalar. Neither answers `@>` or `?` on nested JSON without an expression index on the extracted field.
- Extracted B-tree on `(data->>'sku')` is valid — that is an [expression index](/studies/partial-expression-indexes).
- Hash still cannot range the extracted timestamp.
- Wrong AM ⇒ Seq Scan with a filter that looks indexed.
Pick the access method
Operator first. Selectivity second. Writes third.
- 1
= / range / ORDER BY / UNIQUE on a scalar
B-tree. This is the default and the interview default. - 2
Equality only on a huge scalar, measured win
Hash. Crash-safe since PG 10. Still cannot sort. - 3
Arrays, JSONB containment, FTS tsvector
GIN. Inverted: row → many keys. - 4
Geometry, range overlap, trigram similarity, KNN
GiST (or SP-GiST). Tree of predicates, not inverted posting lists. - 5
CREATE INDEX and hope
No USING clause means B-tree. Your @> query will not magically GIN.
B-tree (the default)
Balanced multi-way tree. Leaf level sorted and linked. Supports:
=,<,>,BETWEEN,IS NULLORDER BY(often no Sort)- Unique / PK / FK backing
- Prefix
LIKE 'abc%'with C collation /pattern_ops
Cannot: leading-wildcard LIKE '%x' (use pg_trgm GiST/GIN), JSONB @>, array &&.
Fan-out is large; height is small. I/O is leaf + heap unless covering.
Hash
Stores a 32-bit hash, not the value.
- Only
=. No range, noORDER BY, no unique constraints. - Single-column.
- Crash-safe since Postgres 10.
- Consider when the indexed value is huge (long tokens) and you only ever equality-lookup.
Interview default: still B-tree unless you measured hash.
GIN (Generalized Inverted Index)
Posting lists: key → row CTIDs. One row produces many keys (array elements, JSON keys/values, lexemes).
| Workload | Operator examples |
|---|---|
integer[] / text[] | &&, @>, contained-by |
| JSONB | @>, ?, jsonb_path_ops |
| Full text | tsvector @@ tsquery |
pg_trgm GIN | LIKE '%foo%', % similarity |
Writes: fastupdate pending list batches inserts; queries must merge. Heavy ingest + GIN = vacuum/gin_pending_list_limit tuning. Not a B-tree with extra operators.
GiST (Generalized Search Tree)
Balanced tree whose keys are predicates (bounding boxes, ranges). Good at overlap, nearest neighbor (ORDER BY col <-> point), exclusion constraints on ranges.
| Workload | Why GiST |
|---|---|
geometry / geography | PostGIS |
tsrange / daterange | && overlap, EXCLUDE USING gist |
pg_trgm | similarity / % (GiST or GIN; GiST for KNN) |
| Cube / ltree | Hierarchical / vector-ish |
GIN vs GiST for trigram: GIN often faster exact %; GiST supports KNN. Say which query you have.
BRIN (bonus): tiny, block-range min/max. Append-only time-series physically ordered by the indexed column. Wrong on shuffled heap.
CREATE INDEX ON orders (user_id); -- btree (default)
CREATE INDEX ON sessions USING hash (session_token); -- = only
CREATE INDEX ON products USING gin (tags); -- array overlap
CREATE INDEX ON events USING gist (during); -- range overlapDecisions
- 1
Predicate
- nextScalar = / range / sort?
- nextMany keys per row?
- nextOverlap / KNN / geometry?
- ?
Scalar = / range / sort?
- yesB-tree
- huge = onlyHash
- 3
B-tree
- 4
Hash
- ?
Many keys per row?
- array JSON FTSGIN
- 6
GIN
- ?
Overlap / KNN / geometry?
- yesGiST
- 8
GiST
Lesson map
B-tree vs Hash vs GIN vs GiST
B-tree is the default for equality + range + ORDER BY. Hash is equality-only (niche). GIN shines for arrays/JSONB/full-text (inverted). GiST powers geometry, ranges, and trigram similarity. Pick the access method by operator class, not vibes.
Architecture. Architecture
Select a node to see why it exists, or an edge to see the protocol, direction, effect, and consequence.
Mermaid export
flowchart TB q["Predicate"] b["Scalar = / range / sort?"] bt["B-tree"] h["Hash"] q -->|Predicate to Scalar = / range / sort?| b b -->|yes| bt b -->|huge = only| h
Match operators (run this)
Press Run. Snippets must be self-contained — no network, files, or native modules.
Press Run. Snippets must be self-contained — no network, files, or native modules.
Interview Q&A
Default index type in Postgres?
Answer
B-tree. Equality, ranges, ORDER BY, uniqueness. Say USING only when you need another AM.
When hash?
Answer
Pure = on a large value where B-tree leaves would be huge, and you measured it. No ranges, no unique, no sort.
GIN vs B-tree for an array column?
Answer
B-tree indexes the whole array as one value (=). GIN indexes elements so && / @> can seek. Containment needs GIN (or a denormalized table).
GIN vs GiST in one sentence each?
Answer
GIN: inverted posting lists, many keys per row, great at membership/contains. GiST: tree of predicates, overlap, KNN, exclusion constraints.
JSONB — jsonb_ops vs jsonb_path_ops?
Answer
jsonb_ops (default) supports more operators, larger. jsonb_path_ops is smaller and optimized for @>. Pick from the query, not the name.
LIKE '%foo%' — which AM?
Answer
Not B-tree. pg_trgm GIN or GiST. Leading prefix foo% can use B-tree with the right opclass.
Why can GIN ingest be slow?
Answer
Each row updates many posting lists. Pending list (fastupdate) batches that; queries merge it. Tune or disable for bulk load then VACUUM.
Can hash enforce UNIQUE?
Answer
No. Unique/PK are B-tree in Postgres.
BRIN vs B-tree?
Answer
BRIN stores min/max per block range. Tiny. Only if heap order correlates with the column (time-series append). Random user_id → useless BRIN.
How do you confirm the AM was used?
Answer
EXPLAIN (ANALYZE, BUFFERS) — Index Scan vs Seq Scan vs Bitmap with using gin / using gist in the node. EXPLAIN page.
Pitfalls
user_id =, during && range, data @> sku, title LIKE '%x%'. Write CREATE INDEX … USING. Then run the Python picker. If hash got the range, start over.
Go Deeper
Cluster: hub · next composites