PostgreSQL Indexes — B-Tree, GIN, GiST, BRIN, Hash
Fast recall
Quick revision
Core answer
Whether you can match the right index type to a query pattern (equality, range, containment, geometric, nearest-neighbour, time-series) instead of reflexively reaching for B-tree, and whether you understand partial, covering, and expression indexes.
Creating a B-Tree index on a JSONB column, a tsvector column, or a geometry column — the planner cannot use it for the containment, text-match, or geometric operators those types support.
Explain `jsonb_path_ops` for GIN, the INCLUDE clause for covering indexes, partial indexes for soft-deleted rows, BRIN multi-minmax in PG 14+, and `CREATE INDEX CONCURRENTLY` as the production-safe build.
Interview-ready guide
Interview answer
PostgreSQL has five main index types and the right answer depends on what operator the query uses.
B-Tree is the default and handles equality, ranges, ORDER BY, and LIKE-prefix queries. It covers most queries you write.
GIN is an inverted index — it stores one entry per token and maps tokens to rowids. That makes it the only index that can serve JSONB containment with @>, array containment, and full-text search with @@. For JSONB, the jsonb_path_ops opclass is smaller and faster when you only use @>.
GiST is a generalized balanced tree that supports overlap and nearest-neighbour operators. It is what PostGIS uses for spatial queries and what range types use for && overlap.
BRIN stores a summary per block range instead of per row. On naturally ordered tables — time-series, append-only logs — a BRIN index can be a thousand times smaller than a B-Tree and perform comparably for range queries.
Hash is equality-only and since PostgreSQL 10 it is crash-safe and replicated. It wins over B-Tree for strict equality on long string keys like URLs because the fixed hash size is smaller than the full string value.
Three power tools on top of these
- Partial indexes index only rows matching a predicate — hot subsets like active rows
- Covering indexes via INCLUDE let you return columns straight from the index without touching the heap
- Expression indexes index computed values like
LOWER(email)
And in production every index must be built with CREATE INDEX CONCURRENTLY or you hold an ACCESS EXCLUSIVE lock that blocks all reads and writes.
PostgreSQL Indexes — B-Tree, GIN, GiST, BRIN, Hash
Direct Answer
PostgreSQL ships five primary index types. B-Tree is the default for equality, range, and ordering. GIN is an inverted index for JSONB, arrays, and full-text search. GiST is a generalized tree for geometric, range, and nearest-neighbour queries. BRIN stores one summary per block range — tiny indexes on naturally ordered tables like time-series. Hash is equality-only and now crash-safe and replicated. Wrong index type means the index exists but the planner never uses it, so matching the data structure to the access pattern is the core skill.
Code Example
-- 1. B-TREE — equality + range + ordering
CREATE INDEX idx_orders_user_created
ON orders (user_id, created_at DESC);
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42
AND created_at > now() - interval '30 days'
ORDER BY created_at DESC LIMIT 20;
-- Index Scan using idx_orders_user_created on orders
-- Index Cond: (user_id = 42 AND created_at > ...)
-- (actual time=0.03..0.42 rows=20 loops=1)
-- 2. GIN — JSONB containment and full-text search
CREATE INDEX idx_products_attrs_gin
ON products USING GIN (attributes jsonb_path_ops);
EXPLAIN ANALYZE
SELECT id FROM products
WHERE attributes @> '{"brand":"Acme","in_stock":true}'::jsonb;
-- Bitmap Heap Scan on products
-- Recheck Cond: (attributes @> ...)
-- -> Bitmap Index Scan on idx_products_attrs_gin
-- Index Cond: (attributes @> ...)
CREATE INDEX idx_articles_fts
ON articles USING GIN (to_tsvector('english', body));
-- 3. GiST — geometric overlap + nearest-neighbour
CREATE EXTENSION postgis;
CREATE INDEX idx_stores_geom
ON stores USING GIST (geom);
EXPLAIN ANALYZE
SELECT id, name
FROM stores
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(-122.4, 37.8), 4326)
LIMIT 5;
-- Index Scan using idx_stores_geom (kNN)
-- Order By: geom <-> ...
-- 4. BRIN — naturally ordered large tables (time-series)
CREATE INDEX idx_readings_time_brin
ON sensor_readings USING BRIN (recorded_at)
WITH (pages_per_range = 32);
-- PG 14+ multi-minmax for data with occasional outliers
CREATE INDEX idx_readings_time_brin_multi
ON sensor_readings USING BRIN (recorded_at timestamptz_minmax_multi_ops);
EXPLAIN ANALYZE
SELECT count(*) FROM sensor_readings
WHERE recorded_at BETWEEN '2026-04-01' AND '2026-04-07';
-- Bitmap Heap Scan (recheck)
-- -> Bitmap Index Scan on idx_readings_time_brin
-- Index Cond: (recorded_at BETWEEN ... AND ...)
-- Index size ~5 MB for a 100 GB table
-- 5. HASH — equality on long string keys (crash-safe since PG 10)
CREATE INDEX idx_pages_url_hash
ON web_pages USING HASH (url);
EXPLAIN ANALYZE
SELECT id FROM web_pages WHERE url = 'https://example.com/very/long/path/...';
-- Index Scan using idx_pages_url_hash
-- (Hash index is smaller than the equivalent B-Tree on long strings;
-- cannot serve range, ORDER BY, or LIKE prefix)
-- BONUS 1: PARTIAL INDEX — index only rows that matter
CREATE INDEX idx_orders_active
ON orders (user_id)
WHERE deleted_at IS NULL AND status IN ('pending','processing');
-- BONUS 2: COVERING INDEX via INCLUDE — Index Only Scan
CREATE INDEX idx_orders_covering
ON orders (user_id, created_at DESC)
INCLUDE (status, total)
WHERE deleted_at IS NULL;
EXPLAIN ANALYZE
SELECT created_at, status, total
FROM orders
WHERE user_id = 42 AND deleted_at IS NULL
ORDER BY created_at DESC LIMIT 10;
-- Index Only Scan using idx_orders_covering
-- Heap Fetches: 0 <- touches only the index, never the heap
-- BONUS 3: EXPRESSION INDEX
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
-- Enables WHERE LOWER(email) = 'user@example.com'
-- PRODUCTION-SAFE BUILD
CREATE INDEX CONCURRENTLY idx_orders_covering
ON orders (user_id, created_at DESC)
INCLUDE (status, total);
-- Does not block writes; check pg_indexes for INVALID indexes afterwards
Common Mistakes & Pitfalls
Creating a B-Tree index on a JSONB column, a tsvector column, or a geometry column — the planner cannot use it for the containment, text-match, or geometric operators those types support.
Check your understanding
Answer these without looking back at the article.
- What is the difference between the default GIN opclass and `jsonb_path_ops` and when does each win?
- When is BRIN preferable to B-Tree, and why is it usually unsuitable for random-access tables?
- What does `CREATE INDEX ... INCLUDE (...)` enable and how does it change the plan?
- Why must `CREATE INDEX CONCURRENTLY` be used in production and how do you recover from a failed concurrent build?
Related interview prep categories
1Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.