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.

PostgreSQL Indexes Best Practices: Choosing the Right Index for Every Query

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 six built-in index access methods. B-tree is the default and handles most workloads, but the others exist for good reasons. Extensions add more, covered in the next section.

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 long values (UUIDs, URLs); not for short keys
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

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. B-tree always supports index-only scans. GiST and SP-GiST support them for some operator classes but not others, and GIN, BRIN, and hash cannot - they never store enough to reconstruct the indexed value, so every match goes back to the heap.

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.

One GIN detail that catches people in production: by default GIN buffers new entries in a pending list (fastupdate, on by default) instead of writing them into the main index structure. The pending list is flushed when it grows past gin_pending_list_limit (default 4 MB) or at VACUUM time. The unlucky write that triggers the flush stalls while the whole list is merged, and every lookup must scan the pending list in addition to the index. If predictable query latency matters more than write throughput, turn it off with CREATE INDEX ... WITH (fastupdate = off), or ALTER INDEX ... SET (fastupdate = off) followed by VACUUM or SELECT gin_clean_pending_list('idx') to flush what is already buffered. For high-throughput ingest or bulk loads, keep it on, tune gin_pending_list_limit per index, and rely on autovacuum for background cleanup. The GIN tips page covers the trade-offs in more detail.

Hash indexes are more niche than their table row suggests. A hash index entry stores only a 4-byte hash of the value, so the index is much smaller than a B-tree on wide values like UUIDs or URLs, and roughly comparable or larger on short integer keys. Cardinality does not drive its size, and scans are lossy - PostgreSQL always rechecks the heap to confirm the match.

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.

Index Types from Extensions

Beyond the six built-in access methods, several extensions ship their own index types. They must be installed per database with CREATE EXTENSION, and not every managed service makes all of them available, so check before designing around one.

Bloom is a contrib module (CREATE EXTENSION bloom) that builds a signature-based index over many columns at once, answering equality filters on any combination of them with a single compact index. It is lossy, so every hit is rechecked against the heap, but it replaces the combinatorial explosion of multicolumn B-trees you would otherwise need. See the bloom documentation.

btree_gin and btree_gist provide GIN and GiST operator classes for scalar types, letting a plain integer or timestamp column join a composite GIN or GiST index alongside JSONB, arrays, or ranges. btree_gist is also what makes exclusion constraints on a range plus a scalar possible - the classic "no overlapping bookings for the same room" constraint.

pg_trgm adds trigram operator classes for GIN and GiST, which is what makes LIKE '%term%', ILIKE, and similarity searches index-backed. Without it, a leading-wildcard pattern always falls back to a sequential scan.

pgvector contributes hnsw and ivfflat access methods for approximate nearest-neighbor search over embeddings. We have written about choosing between HNSW and IVFFlat and about running pgvector in production.

PostGIS registers GiST and SP-GiST operator classes for geometry and geography columns, backing spatial predicates like ST_Intersects and ST_DWithin. A spatial query without one of these indexes is almost always a sequential scan over every shape in the table.

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 iterates through the distinct values of the omitted leading columns - but the planner only chooses it when those leading columns have few distinct values. With many distinct values it would have to walk the whole index, so the planner prefers a sequential scan or a dedicated index. Creating a separate index is still the right answer 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, provided those omitted leading columns have few distinct values - reducing the need for redundant single-column indexes in that specific case. 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, but only when the omitted leading columns have few distinct values.

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