---
title: "Postgres: Indexes"
slug: "postgres-indexes"
category: "backend"
tags: ["backend", "postgres", "indexing", "performance", "sql"]
status: "stable"
last_updated: 2026-08-14
summary: "Pick the index type for the access pattern: B-tree by default, GIN for JSONB and full-text, GiST for ranges, BRIN for huge append-only tables."
related:
  [
    "[[backend/postgres]]",
    "[[backend/postgres-explain]]",
    "[[backend/postgres-jsonb]]",
    "[[backend/postgres-full-text-search]]",
    "[[backend/migrations]]",
  ]
---

## Overview

Pick the index for the access pattern, not the column type. Postgres 18 ships B-tree, hash, GIN, GiST, SP-GiST, and BRIN; each wins a different shape of query. This page covers the five that matter, plus partial, expression, and covering indexes, and the production rules for adding them safely. The umbrella rules live in [[backend/postgres]].

## Use B-tree by default for equality, range, and order

B-tree handles `=`, `<`, `>`, `BETWEEN`, `IN`, `IS NULL`, and `ORDER BY` on scalar columns. It is the implicit type when you write `CREATE INDEX` with no `USING` clause.

```sql
CREATE INDEX orders_user_id_created_at_idx
  ON orders (user_id, created_at DESC);
```

Order multi-column B-trees by selectivity and access pattern: the column you filter equality on first, then the range or sort column. A B-tree on `(a, b)` serves queries on `a` alone, but not on `b` alone; build the prefix you actually filter.

## Reach for GIN on JSONB, full-text, and arrays

GIN indexes are inverted lists. Use them when a single row has many searchable values inside one column.

- JSONB containment: `CREATE INDEX events_payload_gin ON events USING GIN (payload jsonb_path_ops);`. The `jsonb_path_ops` operator class is smaller and faster than the default when you only need `@>`.
- Full-text search: index `to_tsvector('english', body)`; see [[backend/postgres-full-text-search]].
- Arrays: `CREATE INDEX tags_gin ON posts USING GIN (tags);` supports `&&` and `@>` on `text[]` and `int[]`.

GIN is slow to build and slow to update. Bulk loads run faster with the index dropped, then recreated.

## Reach for GiST for ranges and spatial data

GiST is a balanced tree that supports overlap and nearest-neighbor queries. Use it when the column is a range, geometry, or `tsrange` constraint.

```sql
CREATE INDEX bookings_during_gist
  ON bookings USING GiST (during);

ALTER TABLE bookings
  ADD CONSTRAINT no_overlap EXCLUDE USING GiST (room_id WITH =, during WITH &&);
```

GiST also powers `pg_trgm` similarity search and PostGIS geometry indexes. See [[backend/postgres-full-text-search]] for the trigram pattern.

## Reach for BRIN on huge append-only tables

BRIN stores summaries per block range. Indexes are tiny (kilobytes for terabyte tables) and only useful when physical row order correlates with the indexed column. The classic case is time-series append.

```sql
CREATE INDEX events_created_at_brin
  ON events USING BRIN (created_at) WITH (pages_per_range = 32);
```

Match BRIN with `CLUSTER` or with a partitioning scheme that keeps inserts in order; see [[backend/postgres-partitioning]]. On randomly inserted data, BRIN degrades to a sequential scan.

## Add partial indexes for skewed predicates

When a predicate covers most rows, exclude them from the index. The index gets smaller and writes get cheaper.

```sql
CREATE INDEX users_active_email_idx
  ON users (email) WHERE deleted_at IS NULL;
```

Partial indexes are the standard answer to soft-delete patterns, feature flags, and status enums where one value dominates. Match the `WHERE` clause exactly; the planner only uses the partial index when the query predicate is a subset.

## Add expression indexes for derived lookups

Index the expression the query filters on, not the raw column.

```sql
CREATE INDEX users_lower_email_idx ON users (lower(email));
-- matches: WHERE lower(email) = lower($1)
```

Use expression indexes for case-insensitive lookups, type casts, and JSON path extracts. The expression in the `WHERE` clause must match the indexed expression byte-for-byte for the planner to pick it.

## Use covering indexes (INCLUDE) for index-only scans

Add non-key columns with `INCLUDE` to enable index-only scans. The planner skips the heap when every projected column lives in the index.

```sql
CREATE INDEX orders_user_id_created_at_idx
  ON orders (user_id, created_at DESC) INCLUDE (total_cents, status);
```

`INCLUDE` columns do not affect ordering or uniqueness. Use them for hot read paths; do not add every column to every index.

## Build indexes CONCURRENTLY in production

Every `CREATE INDEX` takes an `ACCESS EXCLUSIVE` lock unless you use `CONCURRENTLY`. On a hot table that blocks every reader and writer until the build finishes.

```sql
CREATE INDEX CONCURRENTLY orders_user_id_idx ON orders (user_id);
```

`CONCURRENTLY` cannot run inside a transaction. Disable the per-migration transaction wrapper; see [[backend/migrations]] for the framework flags. A failed concurrent build leaves an `INVALID` index; drop it before retrying.

## Do not index every WHERE clause

Indexes cost write amplification, vacuum work, and storage. The anti-pattern: a table with twenty single-column indexes added one per slow query.

- Prefer composite indexes that serve several queries. `(user_id, created_at)` covers filters on `user_id` and on `user_id` plus a date range.
- Drop indexes with zero scans in `pg_stat_user_indexes` after a release cycle.
- Validate every new index against [[backend/postgres-explain]] before merging. For the end-to-end slow-query debug loop, see [[howto/debug-postgres-slow-query]].

## Related

- [[backend/postgres]]
- [[backend/postgres-explain]]
- [[backend/postgres-jsonb]]
- [[backend/postgres-full-text-search]]
- [[backend/postgres-partitioning]]
- [[backend/migrations]]