Postgres has four index types. Most teams use one. Here's what the others unlock.

When a query slows down and an engineer adds an index, the index is almost always a B-tree. That is not wrong. B-tree is the right default. But defaulting to B-tree for every column is the kind of correct that hides an entire vocabulary.

Postgres ships four general-purpose index types: B-tree, BRIN, Hash, and GIN. A fifth type, GiST, covers geometric data and ranked full-text search. Most teams know B-tree well and have heard of GIN. Almost nobody reaches for BRIN consciously. Partial indexes are not a fifth type but a modifier that applies to any type; they are where the biggest wins often hide.

This article covers the three non-obvious index types, when to reach for each, the production failure mode for each, and how to confirm any of it actually worked.

B-tree is the right default, and that is precisely the problem

B-tree indexes maintain a balanced sorted tree of indexed values. Each lookup traverses from the root to a leaf node, then follows a list of tuple identifiers to the actual heap rows. This structure supports equality, range queries, sorting, and combinations of all three. It is fast, predictable, and handles most query shapes a web application generates.

Two categories of queries break that generalisation. The first is operators B-tree simply does not support: JSONB containment (@>), array overlap (&&), and full-text matching (@@). The second is tables where B-tree works but is a poor fit: very large, append-only tables where values happen to be correlated with physical row order.

For the first category, Postgres falls back to a sequential scan and does not throw an error. You will not know the index is unused without reading EXPLAIN output. For the second, B-tree produces an index that is hundreds of megabytes, slow to build after a crash, and adds overhead to every insert. Both situations have better options.

BRIN: the compact index for sequential, append-heavy data

BRIN stands for Block Range INdex. Where B-tree records each row individually, BRIN records the minimum and maximum values across a range of data blocks (128 pages by default). A query that filters on a column range uses the BRIN index to identify which blocks might contain matching rows, then reads only those blocks.

The size difference is dramatic. A B-tree on a 200 GB events table with 500 million rows and a timestamp column might run 15–20 GB. A BRIN on the same column might be 4 MB. The reason: BRIN stores one min/max summary per block range rather than one entry per row.

The catch is a hard requirement: the indexed column's values must be correlated with physical row order. For a created_at timestamp on an event log table where new rows are always appended in time order, correlation is naturally near 1.0. If row A was inserted before row B, row A's block will always have an earlier timestamp. BRIN can skip entire block ranges confidently.

For a user_id column distributed randomly across blocks, correlation is near 0. Every block range will have similar min/max values covering the full range of user IDs, and the BRIN index will tell Postgres almost nothing; it reads nearly every block anyway.

Check correlation before creating a BRIN index:

SELECT correlation
FROM pg_stats
WHERE tablename = 'events'
  AND attname   = 'created_at';

A correlation above 0.9 makes BRIN effective. Below 0.5, use B-tree. Values between 0.5 and 0.9 depend on query selectivity and table size; run EXPLAIN on both and compare.

Creating a BRIN index:

CREATE INDEX events_created_at_brin
  ON events
  USING brin (created_at);

-- Optional: increase pages_per_range for very large, very sequential tables
-- to reduce index size further at a small cost in query precision
CREATE INDEX events_created_at_brin_coarse
  ON events
  USING brin (created_at)
  WITH (pages_per_range = 256);

The primary failure mode: BRIN works on correlation between values and block location. If rows are frequently updated after insertion, Postgres may move them to different blocks (via heap updates), degrading correlation over time. Most event log and audit trail tables are append-only, so this is rarely a problem. For tables with significant UPDATE traffic, check correlation periodically and consider rebuilding the index or switching to B-tree.

GIN: when your column is a collection, not a scalar

GIN stands for Generalized Inverted INdex. The "inverted" is what distinguishes it from B-tree. A B-tree index maps each value to its rows: value → [row7, row42, row190]. An inverted index maps each element within a value to the rows that contain it: element → [rows containing this element]. This is exactly how a search engine index works.

For Postgres, GIN is the required index for:

• JSONB with @> (containment), ? (key exists), ?| (any key), ?& (all keys)
• Array columns with @> (contains), && (overlaps), <@ (is contained by)
• Full-text search: a tsvector column queried with @@ and a tsquery

Without a GIN index, every query using these operators on a JSONB or array column is a sequential scan. Postgres reads every row in the table and evaluates the condition. On a table with ten million rows, that scan runs regardless of how selective the filter is.

-- GIN on a JSONB column (covers all @>, ?, ?|, ?& operators)
CREATE INDEX events_metadata_gin
  ON events
  USING gin (metadata);

-- GIN on an array column
CREATE INDEX articles_tags_gin
  ON articles
  USING gin (tags);

-- GIN for full-text search
-- First, add a tsvector column or use an expression index
CREATE INDEX articles_search_gin
  ON articles
  USING gin (to_tsvector('english', title || ' ' || body));

After creating the JSONB index, containment queries use the index:

-- Uses the GIN index (metadata column has a GIN index)
SELECT id, created_at
FROM events
WHERE metadata @> '{"event_type": "checkout", "currency": "INR"}';

-- Also uses the index
SELECT *
FROM articles
WHERE tags && ARRAY['postgres', 'performance'];

The write performance tradeoff is real. Every insert or update to a GIN-indexed JSONB column may update many GIN entries, one per distinct key and value in the JSONB blob. Postgres manages this with a fastupdate mechanism: by default, it batches GIN updates into a "pending list" and merges them during autovacuum or when the pending list fills. This smooths write throughput but can cause occasional slow queries when the pending list must be flushed synchronously.

If your table sees very high write rates on JSONB columns and query latency spikes are unacceptable, set fastupdate = off at index creation. Writes become slightly slower on average, but you lose the variance spike:

CREATE INDEX events_metadata_gin
  ON events
  USING gin (metadata)
  WITH (fastupdate = off);

Partial indexes: the smallest index that answers your real question

A partial index is any index with a WHERE clause. It indexes only the rows that match the predicate. The result is smaller, cheaper to build, and faster to scan than a full index on the same column.

The classic case is soft deletion. Most queries that touch a users table want active users, not deleted ones. If 3% of users are soft-deleted, a B-tree on email that includes deleted rows is 97% wasted space for the most common query:

-- Full index: indexes every user, including deleted ones
CREATE INDEX users_email_full ON users (email);

-- Partial index: only indexes active users — 97% smaller
CREATE INDEX users_email_active
  ON users (email)
  WHERE deleted_at IS NULL;

-- Postgres uses the partial index for this query
SELECT id, name FROM users
WHERE email = '[email protected]'
  AND deleted_at IS NULL;

Job queues follow the same pattern. The queries that need to be fast ("give me the next pending job") only care about pending rows. A partial index on pending jobs shrinks as work is processed and grows only when new jobs are added:

CREATE INDEX jobs_pending_created_at
  ON jobs (created_at ASC)
  WHERE status = 'pending';

-- Fetching the next job uses the small partial index, not a full table scan
SELECT id, payload FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

Partial indexes can also enforce uniqueness within a subset. The scenario: a user can cancel and resubscribe, creating multiple subscription rows per user. A full unique constraint on user_id would prevent this. A partial unique index enforces the business rule correctly:

-- Unique among active subscriptions only
CREATE UNIQUE INDEX subscriptions_one_active_per_user
  ON subscriptions (user_id)
  WHERE status = 'active';

The requirement for Postgres to use a partial index: the WHERE clause in your query must logically imply the index's WHERE predicate. If the index has WHERE deleted_at IS NULL, the query must include deleted_at IS NULL. A query that filters only on email, without the deleted_at condition, will use the full index (if one exists) or do a sequential scan. When in doubt about whether Postgres is using a partial index, check EXPLAIN.

Reading EXPLAIN (ANALYZE, BUFFERS) to confirm any of this worked

Creating an index does not guarantee Postgres uses it. Run EXPLAIN (ANALYZE, BUFFERS) on the exact query you expect the index to help, and look at the plan Postgres chose:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, payload
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1;

What to look for in the output:

• "Index Scan using jobs_pending_created_at": the partial index is being used.
• "Index Only Scan": Postgres read the index without touching the heap. The fastest possible outcome.
• "Bitmap Index Scan" followed by "Bitmap Heap Scan": index used but Postgres had to fetch many heap pages. Normal for moderate selectivity.
• "Seq Scan": the planner chose not to use any index. This is either correct (the filter is not selective enough) or a sign of a problem.

Four reasons Postgres ignores a valid index:

One habit worth building: after any index creation in production, run EXPLAIN ANALYZE on the slow query that motivated the index, and confirm the plan changed. If it did not, you have not solved the problem yet.

A decision guide

The choice between index types reduces to one question: what is the shape of your data and your query?

Hash indexes, the fourth general-purpose type, are occasionally worth considering for pure equality queries on very high-read, low-write columns. Since Postgres 10 they are WAL-logged and safe for replication. In practice, B-tree equality performance is close enough that Hash is rarely worth the tradeoff in write overhead. If you find yourself reaching for Hash, benchmark both.

The question that surfaces most often in code review: "should we add a GIN index on this JSONB column?" The answer depends on query patterns. If queries consistently filter on the same one or two JSONB keys, an expression B-tree on that extracted value is smaller and faster. If queries filter on arbitrary keys across the blob, GIN is the right call. Look at the actual queries before deciding.

Postgres's planner is good at choosing between candidate indexes when given accurate statistics. Most indexing problems are not a wrong index type; they are a missing index, a correct index with stale stats, or a query shape that defeats selectivity. Three checks catch the rest: correlation for BRIN, fastupdate behaviour for GIN, and predicate match for partial.