What are Postgres index types, and why start from the query instead of the column?

PostgreSQL ships several index access methods. Three of them cover most backend workloads:

  • B-tree is the default. It keeps keys sorted in a balanced tree, so it can answer equality, range and ordering questions.
  • GIN (Generalized Inverted Index) maps each element inside a composite value, such as a jsonb key, an array member or a text lexeme, to the rows that contain it.
  • BRIN (Block Range Index) stores a compact summary, by default the min and max, for each range of physical table blocks. The planner uses those summaries to skip ranges that cannot match.

An index is useful only for the operators it supports. Each index is built with an operator class, which declares the operators the access method can evaluate. The planner considers the index only when a predicate uses one of those operators against the indexed expression. For example, a B-tree on a jsonb column does nothing for @> containment. A B-tree on lower(email) is not used for WHERE email = $1. A B-tree using a non-C collation cannot serve LIKE 'abc%' unless you build it with text_pattern_ops.

Index type Typical operators Supports ORDER BY Relative size
B-tree =, <, <=, >, >=, BETWEEN, IN Yes Medium
GIN @>, <@, &&, ?, @@ No Large
BRIN =, <, >, range comparisons No Very small

For this reason, start from the queries rather than the columns. Collect the real WHERE, JOIN and ORDER BY patterns from your application code and slow-query logs. Note the operator, the expression and the sort direction for each one. Then choose the index type whose operator class matches.

Who should use this query-first approach?

This approach is for anyone who writes or reviews SQL that runs against PostgreSQL in production:

  • Backend and application engineers who add queries as part of feature work and need to decide whether a new index is required, and of which type.
  • Engineers reviewing schema migrations, who need to check that a proposed index matches the operators the application actually uses before it ships.
  • Teams storing semi-structured data in jsonb or arrays, or running full-text search, where the default B-tree does not help and GIN operator class choice matters.
  • Teams with large event, log or metrics tables that grow by appending, where a B-tree on the timestamp may be much larger than necessary and BRIN is worth evaluating.
  • On-call engineers and DBAs investigating slow queries, who need to explain why an existing index is not being used.

If your tables are small enough that sequential scans finish quickly, or your queries already use well-matched B-tree indexes, you may not need anything beyond the default. The method still helps when a query is unexpectedly slow, because the first question is always whether the predicate's operator matches the index's operator class.

How does it work? What each index type stores on disk

The planner can use an index only if its operator class matches the query's operator, and that match depends on what each structure physically stores.

B-tree                     GIN                        BRIN
[root: 40 | 80]            key     -> row TIDs        pages     min    max
  /     |     \            "red"   -> (1,3) (9,2)     0-127     01-01  01-04
[..39] [40..79] [80..]     "blue"  -> (1,1)           128-255   01-04  01-09
 leaves sorted, linked     "admin" -> (2,5) (9,2)     256-383   01-09  01-13

B-tree is a balanced tree of sorted keys. Internal pages route a search down to leaf pages, and each leaf entry points to one heap tuple. The leaves are linked to their neighbours, so a scan can walk a range of keys in either direction. That is why a B-tree can satisfy ORDER BY and LIMIT without a separate sort step.

GIN is an inverted index. It extracts every key or element from a value, such as each jsonb key, array element or text lexeme, and keeps a sorted entry tree of those keys. Each key points to a list of the rows that contain it. A query looks up its keys and combines the row lists, which is why GIN answers "which rows contain X" well. The combined result is an unordered set of row locations, so GIN cannot return rows in sorted order.

BRIN stores one small summary for each block range: a group of consecutive heap pages, sized by the pages_per_range storage parameter (default 128). For minmax indexes the summary is the lowest and highest value in the range. The index can only say "matching rows might be in these pages." Every tuple in a candidate range must therefore be read and rechecked against the condition. A BRIN scan is always lossy, and it only pays off when values on disk are physically correlated with their position in the table.

+----------------------+  +----------------------+  +----------------------+
| B-TREE               |  | GIN                  |  | BRIN (minmax)        |
+----------------------+  +----------------------+  +----------------------+
|      [ 40 | 80 ]     |  | entry tree (sorted)  |  | pages    min   max   |
|     /     |      \   |  | "admin" -> (2,5)(9,2)|  | 0-127    01-01 01-04 |
|[..39] [40..79] [80..]|  | "blue"  -> (1,1)     |  | 128-255  01-04 01-09 |
| leaves: sorted keys  |  | "red"   -> (1,3)(9,2)|  | 256-383  01-09 01-13 |
| <--> sibling links   |  | query: keys -> lists |  | pages_per_range=128  |
| 1 leaf entry = 1 TID |  | AND/OR the TID lists |  | d='01-05': 128-255?  |
| range scan both ways |  | -> unordered TID set |  | read+recheck all rows|
| -> ordered results   |  | -> no ORDER BY       |  | -> always lossy      |
+----------------------+  +----------------------+  +----------------------+
           |                         |                         |
           v                         v                         v
+--------------------------------------------------------------------------+
| HEAP pages: B-tree/GIN point at row TIDs, BRIN at page ranges            |
+--------------------------------------------------------------------------+

A B-tree keeps sorted, linked leaves that point to individual rows. GIN maps each extracted key to an unordered list of row TIDs. BRIN stores only a min/max summary per range of pages_per_range heap pages, so every row in a matching range must be read and rechecked.

When should you use a B-tree index?

B-tree is the default for good reason. It handles equality (=), range comparisons (<, <=, >, >=), BETWEEN, and IS NULL. Because entries are stored in sorted order, it can also return rows already sorted for ORDER BY created_at DESC LIMIT 20 with no separate sort step. Prefix matching such as LIKE 'abc%' uses a B-tree only under the C collation or with an operator class like text_pattern_ops. Most locale collations do not sort strings in a way that keeps a prefix contiguous.

Column order in multicolumn indexes

For WHERE tenant_id = $1 AND status = $2 AND created_at > $3, index (tenant_id, status, created_at). Put equality columns first and the range column last. The equality columns narrow the scan to one contiguous slice, and the range then trims that slice. If created_at comes first, the range spans many different tenant_id values. The scan must walk all of those entries and filter the trailing columns as it goes, so they no longer bound the work.

Features engineers often miss

  • Unique constraints. UNIQUE and primary keys are enforced through a B-tree index. B-tree is the only built-in index type that supports uniqueness.
  • INCLUDE columns. CREATE INDEX ON orders (customer_id) INCLUDE (total) stores total in the leaf pages. Queries that read only those columns can then use an index-only scan.
  • Partial indexes. CREATE INDEX ON jobs (run_at) WHERE status = 'pending' indexes only the hot subset, so the index stays small and fits in memory.
  • Expression indexes. CREATE INDEX ON users (lower(email)) serves WHERE lower(email) = $1. The query must use the same expression as the index.
-- Multicolumn: equality columns first, range column last
CREATE INDEX orders_tenant_status_created_idx
  ON orders (tenant_id, status, created_at);

SELECT id, total FROM orders
WHERE tenant_id = 42 AND status = 'paid' AND created_at > now() - interval '7 days'
ORDER BY created_at DESC   -- sorted order comes straight from the index
LIMIT 20;

-- Prefix LIKE under a non-C collation needs the pattern operator class
CREATE INDEX products_sku_prefix_idx ON products (sku text_pattern_ops);
SELECT id FROM products WHERE sku LIKE 'ABC-%';

-- Uniqueness is enforced by a B-tree index created for the constraint
ALTER TABLE users ADD CONSTRAINT users_username_key UNIQUE (username);

-- INCLUDE: total lives in leaf pages, enabling an index-only scan
CREATE INDEX orders_customer_incl_idx ON orders (customer_id) INCLUDE (total);
SELECT customer_id, total FROM orders WHERE customer_id = 1001;

-- Partial: index only the hot subset of pending jobs
CREATE INDEX jobs_pending_run_at_idx ON jobs (run_at) WHERE status = 'pending';
SELECT id FROM jobs WHERE status = 'pending' AND run_at <= now()
ORDER BY run_at LIMIT 100;

-- Expression: the query must repeat lower(email) exactly
CREATE INDEX users_lower_email_idx ON users (lower(email));
SELECT id FROM users WHERE lower(email) = lower('Alice@Example.com');

-- Check which plan Postgres actually picks
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, total FROM orders WHERE customer_id = 1001;

Each B-tree pattern from this section paired with a query it serves: equality-then-range column order, index-ordered LIMIT, prefix LIKE with text_pattern_ops, unique constraints, INCLUDE, partial and expression indexes, plus EXPLAIN to confirm the chosen plan.

How do you index jsonb, arrays and full-text search with GIN?

GIN inverts the structure that B-tree uses. It stores each element (a jsonb key or value, an array member, a lexeme) once, with a list of the rows that contain it. This makes "which rows contain X" cheap. It does nothing for ordering or range scans.

For jsonb there are two operator classes, and the choice depends on which operators your queries use:

Operator class Supports Index size Typical fit
jsonb_ops (default) ?, ?|, ?&, @>, @?, @@ Larger: indexes keys and values separately Key-existence checks on schemaless data
jsonb_path_ops @>, @?, @@ Usually smaller Containment lookups
B-tree on (data->>'field') =, <, >, ORDER BY Small Equality or ranges on one known key

CREATE INDEX ON events USING gin (data jsonb_path_ops) serves WHERE data @> '{"status": "failed"}'. It cannot answer data ? 'status' because it hashes whole paths to values and never stores bare keys. That design is also why it is usually smaller and faster for containment. If you need key existence, use the default jsonb_ops.

A common mistake is writing WHERE data->>'status' = 'failed' and expecting either GIN index to help. Neither operator class covers ->> combined with =. For one key you filter on often, CREATE INDEX ON events ((data->>'status')) is smaller than a GIN index and also supports ranges and sorting.

For arrays, CREATE INDEX ON posts USING gin (tags) serves tags @> ARRAY['pg'], tags <@ ARRAY['pg','sql'] and the overlap query tags && ARRAY['pg','redis'].

For full-text search, index a tsvector with CREATE INDEX ON docs USING gin (search_vec) and query with WHERE search_vec @@ to_tsquery('english', 'index & bloat'). If you index an expression such as to_tsvector('english', body) instead of a stored column, the query must repeat that exact expression, including the configuration name.

-- Containment-only workload: jsonb_path_ops is smaller and faster for @>
CREATE INDEX events_data_path_gin ON events USING gin (data jsonb_path_ops);
SELECT id FROM events WHERE data @> '{"status": "failed"}';  -- can use the index
SELECT id FROM events WHERE data ? 'status';                   -- cannot: no bare keys stored

-- Need key existence (?, ?|, ?&)? Use the default jsonb_ops instead
CREATE INDEX events_data_gin ON events USING gin (data);
SELECT id FROM events WHERE data ?| ARRAY['error', 'retry'];

-- ->> with = is not a GIN operator; index the expression with a B-tree
CREATE INDEX events_status_idx ON events ((data->>'status'));
SELECT id FROM events WHERE data->>'status' = 'failed';         -- uses events_status_idx

-- Arrays: one GIN index serves contains, contained-by and overlap
CREATE INDEX posts_tags_gin ON posts USING gin (tags);
SELECT id FROM posts WHERE tags @> ARRAY['pg'];
SELECT id FROM posts WHERE tags <@ ARRAY['pg', 'sql'];
SELECT id FROM posts WHERE tags && ARRAY['pg', 'redis'];

-- Full-text search on a stored tsvector column
CREATE INDEX docs_search_gin ON docs USING gin (search_vec);
SELECT id FROM docs WHERE search_vec @@ to_tsquery('english', 'index & bloat');

-- Expression index: the query must repeat the same expression and config
CREATE INDEX docs_body_fts ON docs USING gin (to_tsvector('english', body));
SELECT id FROM docs
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'index & bloat');

-- Confirm which index the planner picks
EXPLAIN SELECT id FROM events WHERE data @> '{"status": "failed"}';

GIN indexes for jsonb (jsonb_path_ops vs jsonb_ops), arrays and tsvector, plus the expression B-tree that handles data->>'status' = 'failed', which neither GIN operator class can serve.

When is a BRIN index the right choice for large append-only tables?

BRIN fits a different shape of data: very large, append-mostly tables such as events, logs and metrics, where a created_at value rises roughly in step with physical row order. A BRIN index does not point at rows. It stores a summary, by default the minimum and maximum value, for each block range of the heap. A query for the last hour reads the summaries, skips every range whose min/max cannot match, and scans only the remaining pages. This works only when values correlate with physical location. You can check this with correlation in pg_stats, which should be close to 1. Backfills or updates that scatter old timestamps across the table widen the ranges, and BRIN stops excluding pages.

Illustrative estimate. Assume 1 billion rows, about 100 rows per 8 kB page, and roughly 20 bytes per B-tree entry on a timestamptz.

  • B-tree: one entry per row, about 20 GB.
  • BRIN: about 10 million heap pages divided into 128-page ranges gives about 78,000 summaries. That is a few megabytes.

The gap is several orders of magnitude. Real sizes depend on row width, fillfactor and index settings.

New ranges are not summarized until vacuum processes them. Rows in unsummarized ranges are always scanned. Set autosummarize = on so autovacuum summarizes each range once inserts move past it. You can also summarize explicitly with brin_summarize_new_values('events_created_brin') or brin_summarize_range(...). A typical definition looks like this: CREATE INDEX events_created_brin ON events USING brin (created_at) WITH (pages_per_range = 64, autosummarize = on);. A smaller pages_per_range makes the index more selective, at the cost of a slightly larger index.

-- 1. Confirm created_at follows physical row order.
--    BRIN only helps when correlation is close to 1.
ANALYZE events;
SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'events' AND attname = 'created_at';

-- 2. Create the BRIN index. 64-page ranges are more selective than
--    the default of 128. autosummarize makes autovacuum summarize
--    each range once inserts have moved past it.
CREATE INDEX events_created_brin ON events
  USING brin (created_at)
  WITH (pages_per_range = 64, autosummarize = on);

-- 3. Summarize any ranges that are still unsummarized, for example
--    after a bulk load. Returns the number of ranges summarized.
SELECT brin_summarize_new_values('events_created_brin');

-- 4. Or summarize only the range that contains a given heap block.
SELECT brin_summarize_range('events_created_brin', 0);

-- 5. Compare the index size with the table size.
SELECT pg_size_pretty(pg_relation_size('events'))              AS table_size,
       pg_size_pretty(pg_relation_size('events_created_brin')) AS brin_size;

-- 6. A recent-window query should use a Bitmap Heap Scan driven by
--    the BRIN index. The 'Rows Removed by Index Recheck' line shows
--    how many rows the lossy summaries let through.
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM events
WHERE created_at >= now() - interval '1 hour';

This example checks physical correlation, creates a BRIN index on created_at with a custom pages_per_range and autosummarize, summarizes ranges explicitly, compares sizes and confirms the planner uses the index for a recent-time query.

When should you avoid each index type, and what does it cost to maintain?

GIN

Every indexed key is a separate index entry, so one inserted jsonb document with 40 keys can write roughly 40 entries. With fastupdate on (the default), new entries go to an unsorted pending list. This keeps inserts cheap, but queries must scan that list. The list is merged into the main index by autovacuum, by gin_clean_pending_list(), or by the insert that pushes it past gin_pending_list_limit. That last case is the source of occasional slow writes. A smaller limit trades those spikes for steadier overhead.

GIN cannot order results or range-scan values inside jsonb. Watch the operator class: switching to jsonb_path_ops makes the index smaller, but ?, ?| and ?& quietly fall back to sequential scans. Indexing the whole document is often far larger than a B-tree expression index on the two keys you actually filter on.

BRIN

BRIN only works while physical order tracks the column. Updates, backfills and out-of-order loads widen each range's min/max until most ranges match every query. Ranges that have not been summarized yet are always scanned. BRIN cannot serve point lookups, enforce uniqueness or provide ordering.

B-tree

Each extra index adds work to every write and to every vacuum. It also blocks HOT updates whenever an indexed column changes. A low-selectivity index, such as one on a boolean, is usually ignored by the planner. A partial index covering the rare value is the better choice.

Type Avoid when Ongoing cost
B-tree Low-cardinality columns, many overlapping indexes Write amplification, vacuum work, lost HOT updates
GIN Write-heavy tables, range or ordering needs Per-key writes, pending-list flushes
BRIN Updated or randomly loaded tables Summarization, degradation over time

Locks and concurrent builds

Plain CREATE INDEX blocks writes for the whole build. CREATE INDEX CONCURRENTLY avoids that, with some caveats:

  • It scans the table twice and waits for open transactions to finish.
  • It cannot run inside a transaction block, which matters for migration tools that wrap each migration in one.
  • If it fails, it leaves an INVALID index. That index still slows writes but serves no queries. Drop it and rebuild it.

How can choosing indexes by query save engineering time?

Most wasted indexing effort comes from building an index that the planner never uses, then debugging why the query is still slow. Starting from the query's operators removes much of that guesswork:

  • Fewer build-and-check cycles. If you check the operator against the operator class before creating the index, you avoid building a B-tree for a @> query or a jsonb_path_ops index for a ? query. On a large table each failed attempt can cost a long concurrent build plus a migration to drop it.
  • Faster slow-query triage. When a query does a Seq Scan despite an index existing, the first check is whether the predicate uses a supported operator on the exact indexed expression, such as lower(email) versus email, or ->> with = against a GIN index. That question often settles the investigation before you look at statistics or planner settings.
  • Less maintenance work later. Choosing BRIN for a correlated append-only timestamp, or a partial B-tree for a hot subset, keeps indexes small. Smaller indexes mean shorter vacuum runs, quicker rebuilds and less time spent on bloat.
  • Fewer redundant indexes. Writing down each query's operator, expression and sort order makes overlapping indexes visible during review. Each one you avoid is one less index to slow writes, rebuild or debate removing later.
  • Clearer code review. A migration that states "this index serves this query with this operator" is quicker to review and to revisit than one that indexes a column because it appears in a WHERE clause.

Illustrative estimate. Assume a team adds about four indexes per quarter, and that without checking operator classes one in four does not get used by its target query. Also assume each such miss costs about half a day to diagnose, drop and replace. That is roughly half a day per quarter spent on avoidable rework, before counting the write overhead of the unused index while it existed. Your own numbers will depend on table sizes, build times and how often schemas change.

How do you verify your index choice and pick between the alternatives?

Before trusting a new index, run the real query, with realistic parameters, under EXPLAIN (ANALYZE, BUFFERS) on production-like data. Planner estimates can look good while the actual work stays the same, so check two things. First, confirm the plan uses the index and not a Seq Scan. Second, compare the Buffers: shared hit/read totals against the plan without the index. Buffers count the pages the query touched, which shows the I/O the index saves more reliably than timing does, because timing changes with cache state.

For BRIN, look at Rows Removed by Index Recheck and the lossy heap block count in the bitmap heap scan. High numbers mean each block range covers too many values to filter usefully. Before you build a BRIN index, check correlation in pg_stats for the column. A value near 1 or -1 means physical order follows column order, so BRIN can skip ranges. A value near 0 means BRIN will scan most of the table.

Use pg_relation_size or \di+ to check index size. An index nearly as large as its table needs to save a lot of buffers to be worth its write cost.

Query shape Index
Scalar equality, range, ORDER BY B-tree
Containment or key existence on jsonb, arrays, tsvector GIN
Range filter on a huge, time-ordered, append-mostly table BRIN
Most of the table returned anyway None, since a sequential scan wins

You can combine types. A BRIN index on created_at can sit alongside a partial B-tree on a hot status value, and each one serves its own query.

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 postgres 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.
  • Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

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.
  • A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
  • 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.