PostgreSQL Index Types: B-Tree, GIN, BRIN and Partial Indexes

How PostgreSQL index types work: matching structure to access pattern

A PostgreSQL index is a separate on-disk structure that maps values derived from table rows to the physical locations of those rows in the heap. The planner uses one only when two conditions hold: the query's WHERE, JOIN or ORDER BY clause uses an operator the index understands, and the estimated cost of reading the index plus the matching heap pages is lower than scanning the table. No index type is the fastest in general. Each is fast for particular operators on particular data shapes.

The operator class decides which operators an index understands. It tells the index access method how to compare, extract or summarize values for a data type. A B-Tree on text with the default operator class supports <, = and > under the column's collation. The same column indexed with text_pattern_ops can also serve left-anchored LIKE 'abc%'. For jsonb, GIN's default jsonb_ops supports key-existence operators such as ?. The smaller jsonb_path_ops supports containment (@>) and not key-existence.

The internal layout of each type explains which operators it can serve:

Sorted order makes ranges cheap. Element lists make membership tests cheap. Block summaries take up very little space but only help when the values in each range of blocks fall into a narrow band.

CopyB-TREE  sorted keys, balanced tree          ops: = < > BETWEEN ORDER BY
              +--------------+
              |  root 40 80  |
              +--------------+
                 |   |   |
     +-----------+   |   +-----------+
     v               v               v
+----------+    +----------+    +----------+
| 10 25 33 |--->| 41 57 72 |--->| 85 90 99 |  leaves linked in key order
+----------+    +----------+    +----------+
  each key -> heap row location (TID)    range scan: walk leaves in order

GIN  inverted index: element -> posting list        ops: @>  &&  ?  @@
+-------------+     +-------------------------+
| 'red'       |---->| (0,1) (0,4) (2,7)       |
| 'blue'      |---->| (0,4) (1,2)             |
| 'green'     |---->| (3,5)                   |
+-------------+     +-------------------------+
 element keys        row locations (block,item) holding that element

BRIN  min/max per block range    ops: < > BETWEEN on correlated cols
+-----------------+-----------------+-----------------+
| blk 0-127       | blk 128-255     | blk 256-383     |
| 1 .. 900        | 901 .. 1800     | 1801 .. 2700    |  <- min..max of v
+-----------------+-----------------+-----------------+
  heap rows stored roughly in order of v (e.g. append-only timestamps)
  WHERE v BETWEEN 1000 AND 1200 -> read only blocks 128-255

A B-Tree keeps keys sorted with linked leaves, GIN maps each element to the rows that contain it, and BRIN stores one min/max summary per block range. These layouts are why each type serves different operators.

B-Tree indexes: equality, ranges, sorting and multicolumn order

A B-tree keeps keys in sorted order across balanced pages. A lookup descends from the root to a leaf in a few page reads, then walks adjacent leaf pages in key order. This makes =, <, >, BETWEEN and ORDER BY cheap: each one is either a single point in the sorted order or a contiguous slice of it. LIKE 'abc%' also qualifies, because a fixed prefix is a range. It needs the C collation or a text_pattern_ops index to work this way. LIKE '%foo%' has no starting point in the sort order, so the planner falls back to scanning.

Column order in multicolumn indexes

Take CREATE INDEX ON orders (customer_id, created_at). Entries are sorted by customer_id first and by created_at within each customer. The query WHERE customer_id = 42 AND created_at >= '2026-01-01' ORDER BY created_at maps to one contiguous run of leaf entries that is already in the requested order. A filter on customer_id alone also works. A filter on created_at alone does not have a contiguous run to read, because matching rows are spread across every customer. This is why equality columns go before range columns. If you reverse the order, the range on the leading column covers many entries, and the equality condition on the second column has to be checked against each one.

Index-only scans and INCLUDE

If every column a query references is in the index, PostgreSQL can answer the query without reading the table. CREATE INDEX ON orders (customer_id, created_at) INCLUDE (total) stores total as payload: it is available to read but is not part of the sort key.

Index entries do not record whether a row is visible to the current transaction. PostgreSQL therefore checks the visibility map. When a heap page is marked all-visible, it skips the fetch for that page. When it is not, it reads the heap page anyway. VACUUM sets these bits, and any write to a page clears them. Heap Fetches in EXPLAIN ANALYZE shows how many fetches the scan could not avoid.

Copy-- Equality column first, range/sort column second, payload via INCLUDE.
CREATE INDEX orders_customer_created_idx
    ON orders (customer_id, created_at)
    INCLUDE (total);

-- Served by one contiguous slice of leaf entries, already in ORDER BY order.
-- Every referenced column is in the index, so an index-only scan is possible.
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, created_at, total
FROM orders
WHERE customer_id = 42
  AND created_at >= '2026-01-01'
ORDER BY created_at;

-- Leading column alone: still a tight range scan on the same index.
SELECT created_at, total FROM orders WHERE customer_id = 42;

-- Second column alone: matches are spread across every customer,
-- so this index helps much less (often a seq scan is chosen instead).
SELECT customer_id, total FROM orders WHERE created_at >= '2026-01-01';

-- Prefix LIKE needs pattern ops (or the C collation) to become a range scan.
CREATE INDEX customers_email_prefix_idx
    ON customers (email text_pattern_ops);
SELECT id FROM customers WHERE email LIKE 'alice%';   -- can use the index
SELECT id FROM customers WHERE email LIKE '%alice%';  -- no start point; scans

-- Refresh the visibility map so index-only scans can skip heap fetches.
-- Re-run the EXPLAIN above and compare the "Heap Fetches" line.
VACUUM (ANALYZE) orders;

Creating a multicolumn B-tree index with INCLUDE, the queries it does and does not serve well, a text_pattern_ops index for prefix LIKE, and a VACUUM that updates the visibility map so index-only scans can avoid heap fetches.

GIN indexes for JSONB, arrays, full-text and substring search

A B-Tree stores one entry per row. GIN (Generalized Inverted Index) breaks each value into component items and stores one entry per item, each pointing to the rows that contain it. For an array column, the items are the array elements. For JSONB, they are keys and values. For a tsvector, they are lexemes. This structure answers "which rows contain X?", which is the question behind containment (@>), key existence (?) and full-text match (@@). A B-Tree on the whole value cannot answer it.

For JSONB, choose the operator class deliberately. The default, jsonb_ops, indexes keys and values separately, so it supports @>, ?, ?| and ?&. jsonb_path_ops stores a hash of each path-and-value pair. That makes it smaller and usually faster for @>, but it cannot answer key-existence queries. If you only ever ask "does this document contain this fragment?", jsonb_path_ops is the better fit.

The pg_trgm extension adds gin_trgm_ops, which splits text into three-character trigrams. With it, GIN can serve LIKE '%term%' and ILIKE searches that a B-Tree cannot. Patterns shorter than three characters get little help from the index.

The costs:

• Size and write cost. A single inserted row can add dozens of index entries.
• The pending list. fastupdate buffers new entries in an unsorted pending list, which makes writes cheaper. Every search must also scan that list, and the eventual flush, triggered by vacuum or by the list reaching its size limit, can stall the write that triggers it.
• Rechecks. GIN results go through a bitmap heap scan. Trigram and jsonb_path_ops matches are lossy, so a broad pattern can make PostgreSQL fetch and recheck many heap rows that turn out not to match.

Copy-- JSONB: default jsonb_ops supports @>, ?, ?| and ?&
CREATE INDEX events_payload_gin ON events USING GIN (payload);

-- jsonb_path_ops: smaller, usually faster for @>, but no key-existence (?)
CREATE INDEX events_payload_path_gin ON events USING GIN (payload jsonb_path_ops);

-- Containment can use either index
SELECT id FROM events WHERE payload @> '{"type": "checkout", "country": "DE"}';

-- Key existence can only use the jsonb_ops index
SELECT id FROM events WHERE payload ? 'coupon_code';

-- Arrays: one index entry per element
CREATE INDEX articles_tags_gin ON articles USING GIN (tags);
SELECT id FROM articles WHERE tags @> ARRAY['postgres', 'indexing'];

-- Full-text: one index entry per lexeme
CREATE INDEX articles_fts_gin ON articles
  USING GIN (to_tsvector('english', body));
SELECT id FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'vacuum & bloat');

-- Substring search through trigrams
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX customers_name_trgm ON customers USING GIN (name gin_trgm_ops);
SELECT id FROM customers WHERE name ILIKE '%ström%';

-- Trade write cost against read cost: turn off the pending list
-- on a read-heavy index, or cap its size (in kB) on a write-heavy one
ALTER INDEX customers_name_trgm SET (fastupdate = off);
ALTER INDEX events_payload_gin SET (gin_pending_list_limit = 1024);

-- Look for 'Recheck Cond' and 'Rows Removed by Index Recheck'
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM customers WHERE name ILIKE '%son%';

Creating GIN indexes for JSONB with both operator classes, for arrays, for full-text search and for trigram substring search, then tuning the pending list and checking the recheck cost with EXPLAIN.

BRIN indexes for large append-only tables

A BRIN (block range index) does not point to individual rows. It splits the table into ranges of consecutive heap pages, 128 by default and tunable with the pages_per_range storage parameter. For each range it stores a summary, which by default is the minimum and maximum value of the column. A query for created_at >= '2024-06-01' checks every summary, skips ranges whose max is below the bound, and scans every page in the remaining ranges. This means the index holds one small entry per range instead of one per row. It stays very small even on large tables, and inserts rarely need to change it.

This only works when column values follow physical row order. Append-only event tables with an insert timestamp or a sequence-generated ID fit well. Before creating one with CREATE INDEX ... USING brin (created_at), check the correlation column in pg_stats for that table and column. Values near 1 or -1 mean physical order tracks value order. Values near 0 mean each range covers most of the value domain, so the summaries exclude nothing.

When to avoid BRIN

Bulk updates, backfills of old data, and out-of-order inserts break the correlation. Updated rows move to wherever free space exists, so ranges widen. A BRIN scan is always lossy, so it always reads whole ranges and rechecks each row. With wide ranges, the scan reads most of the table. EXPLAIN will still show a Bitmap Index Scan on the BRIN index, so it looks healthy. Use EXPLAIN (ANALYZE, BUFFERS) and look at heap blocks read and Rows Removed by Index Recheck.

BRIN does not replace B-Tree. It cannot enforce uniqueness or return rows in sorted order, and it is poor at point lookups that return a few rows.

Copy-- Refresh planner statistics so pg_stats reflects the current physical layout.
ANALYZE events;

-- Check how closely physical row order tracks value order.
-- Near 1 or -1: BRIN can skip ranges. Near 0: BRIN will exclude little.
SELECT attname, correlation
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename = 'events'
  AND attname IN ('created_at', 'id');

-- Create the BRIN index. pages_per_range = 128 is the default and is
-- written out here only to show where to tune it.
-- Smaller ranges skip more precisely but make the index larger.
CREATE INDEX events_created_at_brin
  ON events USING brin (created_at)
  WITH (pages_per_range = 128);

-- Compare index size with table size. The index should be tiny.
SELECT pg_size_pretty(pg_relation_size('events_created_at_brin')) AS index_size,
       pg_size_pretty(pg_relation_size('events')) AS table_size;

-- Plain EXPLAIN only shows that the index is chosen. ANALYZE and BUFFERS
-- show what the scan actually read.
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM events
WHERE created_at >= '2024-06-01'
  AND created_at <  '2024-06-02';

-- In the output, check these lines on the Bitmap Heap Scan node:
--   Rows Removed by Index Recheck: high relative to rows returned
--     means ranges are wide and the scan is lossy.
--   Heap Blocks: lossy=N, where N is close to the table's page count,
--     means the index is skipping almost nothing.
--   Buffers: shared hit/read shows the real I/O cost.

Checking column correlation in pg_stats, creating a BRIN index on an append-only timestamp, and using EXPLAIN (ANALYZE, BUFFERS) to confirm whether the index actually skips block ranges.

Partial indexes and partial unique constraints

A partial index stores entries only for rows that satisfy its WHERE predicate. If a jobs table holds millions of completed rows and a few thousand with status = 'pending', an index on (created_at) WHERE status = 'pending' covers only those few thousand. It stays small enough to remain in memory, and updates to rows outside the predicate never touch it. Soft-delete schemas work the same way: index WHERE deleted_at IS NULL so the index ignores the rows your queries already exclude.

The same mechanism enforces conditional uniqueness. A unique index on (user_id) WHERE active allows any number of inactive rows per user but rejects a second active one. A table-level constraint cannot express this. If you upsert against it, ON CONFLICT must repeat the predicate (ON CONFLICT (user_id) WHERE active) so PostgreSQL can infer which index to use.

The planner uses a partial index only when it can prove, at planning time, that the query's WHERE clause implies the index predicate. That proof works on the SQL text. It does not reason about runtime values. Several common patterns fail it without raising any error:

• Parameters on the predicate column. A query with status = $1 cannot be shown to imply status = 'pending' under a generic plan. A prepared statement can switch to a generic plan after several executions, so a query that was fast at first can later fall back to a sequential scan.
• ORM-generated SQL. Many ORMs bind every literal as a parameter, which produces the case above.
• Rewritten expressions. Casts, COALESCE, or a different but logically equivalent form of the condition can defeat the proof.

To match reliably, write the predicate condition as a literal in the query, using the same expression as the index definition, and pass only the other values as parameters. Then confirm the index is used with EXPLAIN on the statement exactly as the application sends it.

Implementing and verifying an index choice in production

Treat every index as a hypothesis and test it the same way each time.

Capture a baseline

Run the target query under EXPLAIN (ANALYZE, BUFFERS) before changing anything. ANALYZE executes the statement and reports real row counts and timings. BUFFERS shows how many pages were read from shared buffers (shared hit) and how many came from outside them (read). Buffer counts are the more stable comparison. Timing changes with cache warmth and concurrent load, while page counts show how much work the plan actually did.

Build without blocking writes

Use CREATE INDEX CONCURRENTLY. It allows inserts, updates and deletes to continue during the build, but it has three costs:

• It cannot run inside a transaction block. Migration tools that wrap each step in a transaction need that step marked as non-transactional.
• It takes longer than a plain build. It scans the table twice and waits for existing transactions to finish.
• It can fail partway. A failed build leaves an INVALID index. Queries cannot use it, but writes still maintain it. Check for invalid indexes after every build. If you find one, drop it with DROP INDEX CONCURRENTLY and rebuild.

Refresh statistics and compare

Run ANALYZE on the table so the planner has current statistics for its estimates. Then rerun the baseline query and compare the plan shape, the estimated versus actual row counts, and the buffer totals.

Watch usage over time

pg_stat_user_indexes reports idx_scan for each index. If the count stays flat under production traffic, the index is not being used, yet it still adds cost to every write.

A new index does not guarantee the planner will use it. With stale statistics or heavily skewed values, the planner can estimate that a sequential scan is cheaper. Large gaps between estimated and actual rows point to this problem. Fix the statistics first, before rewriting the query or the index.

Decision guide, alternatives, and the cost of indexing

Once the statistics are accurate, decide on the index by matching the query pattern to an access method.

Decision guide

Alternatives to a new index

• Rewrite the query. For example, make a predicate sargable by not wrapping the column in a function.
• Use an expression index when the function call is unavoidable.
• Partition the table so pruning skips whole partitions.
• Accept a sequential scan on small tables, where reading a few pages costs less than an index traversal.
• Add no index at all for rare ad-hoc queries.

The cost

Every index adds write amplification, because each insert, and each update that changes an indexed column, must also write index entries. Indexes take disk and buffer cache. An update that changes an indexed column generally cannot be a HOT update, so it creates new index entries and bloat. Bloated indexes then need REINDEX CONCURRENTLY and someone to schedule it.

Teams that own write-heavy, shared databases gain the most from this discipline. Confirm every index with EXPLAIN (ANALYZE, BUFFERS) before shipping it. Periodically drop indexes whose idx_scan stays at zero in pg_stat_user_indexes. That replaces repeated guess-and-check cycles with one measured decision.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgresql index types, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Adding or removing a column goes through one review with the inheriting services listed, instead of ad-hoc messages to each team.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• Services that depend on a column get the chance to object before it disappears, avoiding emergency fixes.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.