PostgreSQL Index Types and Strategies

Use PostgreSQL B-tree, GIN, GiST, BRIN, partial indexes, expression indexes, and concurrent builds effectively.

View

Export

Copy the compact sheet, download it, or print it.

Download

`D` dense toggle · `C` copy all

## PostgreSQL index types

B-tree index

CREATE INDEX idx_products_sku ON products (sku);

# Default choice for equality, ranges, and ordering.

GIN index on JSONB

CREATE INDEX idx_events_payload_gin ON events USING GIN (payload jsonb_path_ops);

# Speed containment queries on JSONB data.

BRIN index for large append-heavy tables

CREATE INDEX idx_logs_created_at_brin ON logs USING BRIN (created_at);

# Use BRIN when physical order correlates with query ranges.

Expression index

CREATE INDEX idx_users_lower_email ON users ((lower(email)));

# Index the result of an expression used in predicates.

Partial index

CREATE INDEX idx_orders_unshipped ON orders (created_at) WHERE shipped_at IS NULL;

# Index only the rows a hot query actually needs.

Create index concurrently

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

# Reduce blocking on busy tables.

## Plan inspection

Explain with runtime stats

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE shipped_at IS NULL
ORDER BY created_at DESC
LIMIT 100;

# Inspect actual execution behavior.

More in Database Indexing

No recommendations yet.