PostgreSQL Index Types — B-tree, GIN, GiST, BRIN Selection Guide

Why Index Selection Matters

The default CREATE INDEX creates a B-tree index, which is optimal for most cases. But PostgreSQL offers six index types, each designed for specific data patterns. Choosing the wrong type means either slow queries or unnecessarily large, slow-to-update indexes. This guide shows exactly when to use each.

B-tree — The Default (Most Common)

B-tree indexes maintain data in sorted order and support equality, range, and ordering operations. They work on any sortable data type.

-- Standard B-tree (default)
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_created ON orders(created_at DESC);

-- Composite B-tree (column order matters!)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Supports these operators:
-- =, <, <=, >, >=, BETWEEN, IN, LIKE 'foo%' (prefix only)
-- ORDER BY, GROUP BY

Use when: equality lookups, range queries, sorting, foreign keys. This is 90% of your indexes.

Composite index column order rule: Put the most selective (highest cardinality) column first, unless you need to support range queries on the second column, in which case equality columns go first.

GIN — Generalized Inverted Index

GIN is optimized for types that contain multiple values: arrays, JSONB, full-text search vectors, and tsvectors. It builds an inverted index mapping each element to the rows containing it.

-- Array column — find rows containing a value
CREATE INDEX idx_products_tags ON products USING GIN(tags);
-- Query: SELECT * FROM products WHERE tags @> ARRAY['electronics'];

-- JSONB — query inside JSON documents
CREATE INDEX idx_events_data ON events USING GIN(data);
-- Query: SELECT * FROM events WHERE data @> '{"type": "click"}';

-- Full-text search
CREATE INDEX idx_articles_fts ON articles
  USING GIN(to_tsvector('english', title || ' ' || body));
-- Query:
SELECT * FROM articles
WHERE to_tsvector('english', title || ' ' || body)
  @@ to_tsquery('english', 'postgresql & index');

Use when: JSONB containment queries (@>), array containment, full-text search, range types with overlap queries.

Trade-off: Large size and slow updates (GIN must update many posting lists). Use gin_pending_list_limit for high write loads.

GiST — Generalized Search Tree

GiST is a framework for building custom index types. It's used for geometric data, range types, nearest-neighbor searches, and full-text search (as an alternative to GIN).

-- Geometric data — find points within a polygon
CREATE INDEX idx_locations_point ON locations USING GIST(coordinates);
-- Query: SELECT * FROM locations WHERE coordinates && ST_MakeEnvelope(...);

-- Range types — overlapping ranges
CREATE INDEX idx_bookings_period ON bookings USING GIST(period);
-- Query: SELECT * FROM bookings WHERE period && '[2026-01-01, 2026-06-01)';

-- PostGIS — geospatial queries
CREATE INDEX idx_places_geom ON places USING GIST(geom);
SELECT * FROM places
WHERE ST_DWithin(geom, ST_MakePoint(-74.006, 40.712)::geography, 1000);

-- Full-text search (alternative to GIN)
CREATE INDEX idx_articles_fts ON articles
  USING GIST(to_tsvector('english', title));
-- GiST is smaller than GIN but slower for text search reads

Use when: geometric/spatial data, range type overlap, nearest-neighbor KNN queries (<-> distance operator).

BRIN — Block Range Index

BRIN (Block Range INdex) stores summary information (min/max) for ranges of physical disk blocks. Extremely small and cheap to update, but only effective when the data is naturally correlated with physical storage order.

-- Event log with monotonically increasing timestamps
-- (naturally correlated with insertion order = physical order)
CREATE INDEX idx_events_ts ON events USING BRIN(created_at);

-- Large time-series tables (millions/billions of rows)
CREATE INDEX idx_metrics_time ON metrics
  USING BRIN(recorded_at) WITH (pages_per_range = 128);

-- Query: PostgreSQL skips entire block ranges where
-- the range's max timestamp < query start date
SELECT * FROM events
WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';

Use when: Very large tables (100M+ rows) with naturally ordered data (timestamps, auto-increment IDs, log data). A BRIN index might be 1000x smaller than a B-tree on the same column.

Not suitable for: Random data, frequently updated old rows, high-selectivity point queries.

Hash Index

Hash indexes are only useful for equality comparisons (=). Since PostgreSQL 10, they are WAL-safe and crash-recoverable.

CREATE INDEX idx_sessions_token ON sessions USING HASH(session_token);
-- Faster than B-tree for pure equality: WHERE session_token = 'abc123'
-- Cannot be used for range queries or sorting

Use when: Very high-cardinality column used exclusively for equality lookups (session tokens, UUIDs, hashes). Typically only marginally faster than B-tree and less versatile, so it's rarely worth the trade-off.

Partial Indexes

Partial indexes only include rows matching a WHERE condition. Smaller, faster, and more targeted than full-table indexes:

-- Index only active users (ignores deleted/inactive)
CREATE INDEX idx_users_active_email
  ON users(email)
  WHERE deleted_at IS NULL AND active = true;

-- Index only unprocessed orders
CREATE INDEX idx_orders_unprocessed
  ON orders(created_at, user_id)
  WHERE status = 'pending';

-- Unique constraint only for non-null values
CREATE UNIQUE INDEX idx_users_unique_email
  ON users(email)
  WHERE email IS NOT NULL;

Expression Indexes

Index the result of a function or expression, not just a column value:

-- Case-insensitive email search
CREATE INDEX idx_users_lower_email ON users(lower(email));
-- Query must use same expression: WHERE lower(email) = '[email protected]'

-- Date extracted from timestamp
CREATE INDEX idx_orders_year ON orders(EXTRACT(year FROM created_at));

-- JSONB field extraction (avoid scanning full JSON)
CREATE INDEX idx_users_country ON users((data->>'country'));
SELECT * FROM users WHERE data->>'country' = 'US';

EXPLAIN ANALYZE — Verify Index Usage

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 42 AND created_at > NOW() - INTERVAL '30 days';

-- Look for:
-- "Index Scan using idx_orders_user_date" — good, using index
-- "Seq Scan" — not using index (may need to create one, or stats are stale)
-- "Bitmap Index Scan" — combines multiple indexes (good for low selectivity)

-- Update statistics if seq scan despite having an index
ANALYZE orders;

Index Type Selection Summary

Index Type	Best For
B-tree	Equality, ranges, sorting (default choice)
GIN	JSONB, arrays, full-text search
GiST	Geometry, ranges, KNN nearest-neighbor
BRIN	Huge naturally-ordered tables
Hash	Equality-only on high-cardinality columns

Frequently Asked Questions

How many indexes are too many?

Every index slows down INSERT, UPDATE, and DELETE because PostgreSQL must maintain all indexes on every write. A table with 10+ indexes on a write-heavy table will see significant write overhead. Audit unused indexes with pg_stat_user_indexes WHERE idx_scan = 0.

Should I use CONCURRENTLY when creating indexes?

Yes for production tables. CREATE INDEX CONCURRENTLY builds the index without locking out writes. It takes longer and uses more I/O, but doesn't block your application. Never create indexes without CONCURRENTLY on tables with active write traffic.

How do I format JSONB data for testing?

Use the DevKits JSON Formatter to validate and format your JSONB payloads before inserting them into PostgreSQL. Well-formatted JSON is easier to write GIN index queries for.