PostgreSQL Index Types and Strategies

Database Indexing•Permalink

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

View

Standard Detailed Compact

Export

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

Download

`D` dense toggle · `C` copy all

PostgreSQL index types

Choose the right index method for the workload.

B-tree index

Default choice for equality, ranges, and ordering.

sqlANYpostgresqlbtree

↗#

sql

CREATE INDEX idx_products_sku ON products (sku);

Notes

B-tree is the default and best starting point for many OLTP queries.

GIN index on JSONB

Speed containment queries on JSONB data.

sqlANYpostgresqlginjsonb

↗#

sql

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

Notes

GIN is often used for composite or document-like structures such as JSONB and arrays.

BRIN index for large append-heavy tables

Use BRIN when physical order correlates with query ranges.

sqlANYpostgresqlbrintime-series

↗#

sql

CREATE INDEX idx_logs_created_at_brin ON logs USING BRIN (created_at);

Notes

BRIN can be compact and effective for very large time-series or append-only tables.

Expression index

Index the result of an expression used in predicates.

sqlANYpostgresqlexpression-index

↗#

sql

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

Notes

Expression indexes help when the query applies a function such as `lower(email)`.

Partial index

Index only the rows a hot query actually needs.

sqlANYpostgresqlpartial-index

↗#

sql

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

Notes

Partial indexes reduce size and write cost when the workload focuses on a subset of rows.

Create index concurrently

Reduce blocking on busy tables.

sqlANYpostgresqlconcurrentlymigrations

↗#

sql

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

Notes

Concurrent index builds are useful in production migrations when minimizing write blocking matters.

Plan inspection

Verify that the planner uses the index the way you expect.

Explain with runtime stats

Inspect actual execution behavior.

sqlANYpostgresqlexplainanalyze

↗#

sql

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

Notes

This is one of the most useful PostgreSQL performance tools for validating index choices.

More in Database Indexing

Indexing Anti-Patterns and Maintenance

Avoid over-indexing, duplicate indexes, low-value indexes, stale statistics, and maintenance mistakes.

EXPLAIN, ANALYZE, and Verify Index Usage

Use EXPLAIN tools across PostgreSQL, MySQL, SQLite, and MongoDB to prove whether an index actually helps.

Partial, Expression, and Functional Indexes

High-leverage index patterns for subsets of data and computed predicates across PostgreSQL, MySQL, and SQLite.

MongoDB Index Types and Query Patterns

Single-field, compound, multikey, text, wildcard, partial, TTL, and unique index patterns for MongoDB.

SQLite Indexes and Query Planner

SQLite CREATE INDEX patterns, expression and partial indexes, EXPLAIN QUERY PLAN, and lightweight performance tuning.

MySQL Index Strategies and Verification

Composite indexes, invisible indexes, descending indexes, generated-column indexing, and EXPLAIN workflows in MySQL.

Recommended next

No recommendations yet.

Browse all sheets →