A practical guide to PostgreSQL index types - B-tree, GIN, GiST, BRIN, and more - covering when to use each, common pitfalls, and optimization techniques for production workloads.

A PostgreSQL index is a separate data structure that maintains a sorted or structured reference to rows in a table, allowing the query planner to locate matching rows without scanning the entire table. Choosing the wrong index type - or indexing the wrong columns - is one of the most common causes of slow queries in production PostgreSQL deployments.

This post covers each index type PostgreSQL offers, when to pick one over another, and the mistakes we see most often in client engagements.

Index Types at a Glance

PostgreSQL ships with seven index types. B-tree is the default and handles most workloads, but the others exist for good reasons.

Index Type Best For Supported Operators Size Relative to B-tree
B-tree Equality, range, sorting =, <, >, <=, >=, BETWEEN, IN, IS NULL Baseline
Hash Equality-only lookups = Smaller for high-cardinality columns
GIN Multi-valued columns (arrays, JSONB, full-text) @>, <@, &&, @@ Larger (inverted structure)
GiST Geometric, range types, full-text <<, >>, @>, <@, &&, <-> Varies
SP-GiST Non-balanced structures (IP ranges, phone routing) Same as GiST family Varies
BRIN Large tables with physically ordered data =, <, >, <=, >= 100-1000x smaller
Bloom Multi-column equality filters (extension) = Compact, lossy

B-tree handles the vast majority of workloads. If your query uses =, <, >, BETWEEN, ORDER BY, or LIKE 'prefix%', a B-tree index is almost certainly what you want. It is the only type that supports index-only scans natively across all supported operators.

GIN indexes are the right choice for JSONB queries, array containment checks, and full-text search with tsvector. They are slower to build and update than B-tree, but significantly faster for reads on multi-valued data. If your application is read-heavy with JSONB columns, GIN is usually the answer.

BRIN indexes store only the minimum and maximum values for each block range in the table, making them extremely small. A BRIN index on a timestamp column in a 500 million row append-only table might be only a few hundred kilobytes, compared to gigabytes for a B-tree. The catch is that BRIN only works well when the physical order of rows on disk correlates with the column values - typical for time-series or log data that is inserted sequentially.

Partial, Expression, and Covering Indexes

Three features let you build indexes that are smaller, faster, or both.

Partial indexes include only rows matching a WHERE clause. If you have an orders table where 95% of rows have status = 'completed' and your application only queries active orders, a partial index shrinks the index dramatically:

CREATE INDEX idx_orders_active ON orders (created_at)
    WHERE status != 'completed';

The query planner will use this index only when the query's WHERE clause implies the index predicate. You must match the condition exactly - WHERE status = 'pending' works (it implies status != 'completed'), but WHERE status IS NOT NULL does not.

Expression indexes let you index the result of a function or calculation. A common use case is case-insensitive search:

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

The query must use the same expression for the index to apply: WHERE lower(email) = 'user@example.com'. Writing WHERE email = 'User@example.com' will not use this index.

Covering indexes use the INCLUDE clause (available since PostgreSQL 11) to add non-searchable payload columns to an index, enabling index-only scans without touching the heap:

CREATE INDEX idx_orders_customer ON orders (customer_id)
    INCLUDE (total_amount, order_date);

A query like SELECT total_amount, order_date FROM orders WHERE customer_id = 42 can be answered entirely from the index. Be conservative with INCLUDE columns - every additional column increases index size and maintenance overhead. This pays off when the table has a low update rate and the query pattern is stable.

Multicolumn Indexes and Column Ordering

PostgreSQL can use a multicolumn B-tree index for queries that filter on any leading subset of the indexed columns. Column order matters more than most developers realize.

Consider a composite index:

CREATE INDEX idx_events_lookup ON events (tenant_id, event_type, created_at);

This index efficiently supports:

  • WHERE tenant_id = 1
  • WHERE tenant_id = 1 AND event_type = 'login'
  • WHERE tenant_id = 1 AND event_type = 'login' AND created_at > '2026-01-01'

It does not efficiently support WHERE event_type = 'login' alone (the leading column is missing). PostgreSQL 18 introduced skip scan to partially address this - it scans through distinct values of unfiltered leading columns - but creating a separate index is still faster for high-frequency queries on non-leading columns.

The general rule: place equality filters first, range filters last, and the highest-cardinality equality column at the front. Verify with EXPLAIN (ANALYZE, BUFFERS):

EXPLAIN (ANALYZE, BUFFERS)
  SELECT * FROM events
  WHERE tenant_id = 1 AND event_type = 'login' AND created_at > '2026-01-01';

Look for Index Scan or Index Only Scan in the output. If you see Seq Scan on a large table, your index is not being used - either the predicate does not match or the planner estimates a sequential scan is cheaper (often correct for low-selectivity queries).

Common Indexing Mistakes

Years of consulting on PostgreSQL workloads have taught us a few recurring patterns.

Over-indexing. Every index must be maintained on every INSERT, UPDATE, and DELETE. A table with ten indexes turns a single insert into eleven disk writes. We regularly see tables with duplicate or near-duplicate indexes that were added reactively without auditing existing ones. Use pg_stat_user_indexes to find indexes with zero scans:

SELECT schemaname, relname, indexrelname, idx_scan
  FROM pg_stat_user_indexes
  WHERE idx_scan = 0
  ORDER BY pg_relation_size(indexrelid) DESC;

If an index has zero scans after weeks of production traffic, it is safe to drop (unless it backs a unique constraint).

Indexing low-cardinality columns. A boolean column or a status field with three distinct values is a poor candidate for a standalone B-tree index. The planner will often skip it in favor of a sequential scan because the index would return too large a fraction of the table. Partial indexes solve this when you only care about the minority value.

Neglecting index bloat. Heavy UPDATE and DELETE workloads cause dead tuples to accumulate in indexes. Even with autovacuum running, B-tree indexes can bloat to several times their optimal size. Monitor with pgstattuple:

SELECT * FROM pgstattuple('idx_orders_customer');

When avg_leaf_density drops below 50-60%, consider running REINDEX CONCURRENTLY to rebuild without locking.

Wrong column order in composite indexes. Placing a range column before equality columns forces PostgreSQL to scan broader portions of the index. This is one of the easiest performance wins we see - simply reordering columns in a composite index can cut query time by 10x.

Recent PostgreSQL Improvements Worth Knowing

PostgreSQL 17 improved B-tree performance for queries with large IN lists. Previously, each value in an IN clause triggered a separate index lookup. PG 17 batches these into a single scan, which matters for queries like WHERE id IN (1, 2, 3, ..., 1000).

PostgreSQL 17 also added parallel BRIN index builds, cutting build time on large tables significantly.

PostgreSQL 18 (released September 2025) introduced two major index features. Skip scan on multicolumn B-tree indexes allows efficient lookups even when leading columns are not filtered - reducing the need for redundant single-column indexes. Parallel GIN index builds joined B-tree and BRIN in supporting this capability, making full-text search index creation substantially faster on multi-core hardware.

Summary

Pick the index type that matches your query pattern. B-tree covers equality, range, and sort. GIN handles JSONB, arrays, and full-text. BRIN works for large, physically ordered tables. Use partial indexes to shrink index size, expression indexes for function-based queries, and INCLUDE for index-only scans.

Audit your existing indexes regularly. Drop unused ones, fix column ordering in composites, and monitor for bloat. The goal is not more indexes - it is the right indexes.

Key takeaways:

  • Default to B-tree unless your data or query pattern specifically needs another type.
  • Use EXPLAIN (ANALYZE, BUFFERS) to verify index usage - do not guess.
  • Partial indexes are underused and can reduce index size by 90%+ for skewed data.
  • Column order in multicolumn indexes directly impacts whether the index is usable.
  • Monitor with pg_stat_user_indexes and drop indexes that are never scanned.
  • PostgreSQL 18's skip scan reduces the need for redundant indexes on multicolumn B-trees.

For further reading, the PostgreSQL documentation on indexes remains the authoritative reference.