Postgres Index Types Explained: B-tree, GIN, BRIN and More

Postgres has six built-in index types: B-tree (the default, for equality, ranges and sorting), hash (equality only), GIN (values with many parts, such as jsonb, arrays and full-text tsvector), GiST and SP-GiST (geometric data, ranges, nearest-neighbor search and exclusion constraints), and BRIN (tiny summaries for huge tables whose rows are stored in roughly sorted order, like time-series). If you are not sure, use B-tree; it covers most app queries and is the only type that can enforce uniqueness.

This guide goes through each Postgres index type with the query it is built for, the SQL to create it and the cases where it is the wrong choice. It ends with a decision table and the index variations (partial, expression, covering) that matter as much as the type.

#A one-minute primer

An index is a separate structure that lets Postgres find rows without reading the whole table. Every index speeds up some reads and slows down every write, because each insert, update or delete also changes the index. So the goal is not "index everything" but "index what your slow queries filter, join and sort on".

You pick the type with using:

create index orders_customer_idx on public.orders using btree (customer_id);  -- btree is the default

An index type only helps queries that use operators it supports. A B-tree on a jsonb column does not help @>; a GIN index does not help order by. Matching operator to index type is the whole skill.

#B-tree: the default

Built for: =, <, <=, >, >=, between, in, is null, order by, and like 'prefix%'.

create index orders_created_idx on public.orders (created_at);

-- served by the index
select * from public.orders where created_at >= now() - interval '7 days' order by created_at desc limit 50;

B-tree keeps keys sorted, so it answers both lookups and ranges, and it can return rows already in order, which removes a sort step. It is the only built-in type that supports unique, which is why every primary key and unique constraint is a B-tree.

Multicolumn order matters. An index on (tenant_id, created_at) serves where tenant_id = $1 order by created_at, and where tenant_id = $1 alone. It is much less useful for where created_at > $1 without tenant_id. Put the column you filter with = first, then the range or sort column.

Text patterns. In a database with a non-C collation, like 'abc%' needs the text_pattern_ops operator class to use a B-tree. A leading wildcard such as like '%abc%' cannot use a B-tree at all; see GIN with pg_trgm below.

Avoid when: you search inside values (JSON keys, array elements, words in text) or by distance.

#Hash: equality only

Built for: = and nothing else.

create index sessions_token_hash on public.sessions using hash (token);

A hash index stores a 32-bit hash of each value rather than the value itself. That can make it smaller than a B-tree for long keys such as tokens or URLs, and lookups touch fewer pages. Since Postgres 10 hash indexes are written to the WAL, so they are crash-safe and replicate like any other index.

#Hash index vs B-tree

Pick hash when the column holds long values that you only ever look up by exact match, and you have measured that it is smaller or faster. Pick B-tree in every other case, including whenever you might later need a range, a sort or uniqueness. In practice B-tree is the safer default even for equality.

#GIN: many values per row

Built for: values that contain many searchable parts. GIN (Generalized Inverted Index) maps each part, a key, an element or a word, to the rows that contain it.

-- documents
create index products_attrs_gin on public.products using gin (attrs jsonb_path_ops);
select id from public.products where attrs @> '{"color": "red"}';

-- tags
create index posts_tags_gin on public.posts using gin (tags);
select id from public.posts where tags && array['postgres', 'sql'];

-- substring search on names
create extension if not exists pg_trgm with schema extensions;
create index users_name_trgm on public.users using gin (name extensions.gin_trgm_ops);
select id from public.users where name ilike '%ann%';

Write cost. One row can produce dozens of index entries, so GIN is slower to update than B-tree. By default GIN uses a "fast update" pending list that batches new entries and merges them later; that keeps inserts quick but can make some searches slower until the list is cleaned up by vacuum. For write-heavy tables, watch insert latency after adding a GIN index.

Avoid when: you need sorting or ranges on a scalar value. GIN cannot return rows in order.

Full-text search with GIN is covered step by step in Postgres full text search, and JSONB indexing in Querying JSONB in Postgres.

#GiST: shapes, ranges and nearest neighbors

Built for: data where "overlaps", "contains" or "distance" matter more than "equals". GiST (Generalized Search Tree) is a framework that operator classes plug into.

Common uses:

• Range types: tstzrange, daterange, int4range with && (overlaps) and @>.
• Exclusion constraints: "no two bookings for the same room may overlap".
• Geometry: PostGIS geometries and the built-in geometric types.
• Nearest neighbor: order by location <-> point '(10, 20)' limit 10.
• Full-text: an alternative to GIN that is faster to update but usually slower to search.

The classic example is a booking system that the database itself keeps consistent:

create extension if not exists btree_gist with schema extensions;

create table public.bookings (
  id bigint generated always as identity primary key,
  room_id int not null,
  during tstzrange not null,
  exclude using gist (room_id with =, during with &&)
);

The btree_gist extension lets GiST handle the plain room_id equality next to the range overlap. Two inserts with overlapping times for the same room now fail with a constraint error, even under concurrency.

Avoid when: simple equality or ranges on scalar columns; B-tree is faster.

#SP-GiST: partitioned search trees

Built for: data that splits naturally into non-overlapping regions: points in a quadtree or k-d tree, IP addresses (inet), text prefixes (radix trees), and range types.

create index places_point_spgist on public.places using spgist (location);

SP-GiST can beat GiST for evenly spread point data and for prefix lookups. It is a specialist tool; reach for it when you have measured that GiST is slow on that kind of data.

#BRIN: tiny indexes for huge, ordered tables

Built for: very large tables where a column's values follow the physical order of rows. The textbook case is an append-only events or logs table where created_at only goes up.

BRIN (Block Range INdex) stores only the minimum and maximum value for each range of table pages (128 pages by default). A query for "last Tuesday" skips every block range whose min and max exclude it.

create index events_created_brin on public.events using brin (created_at);

The payoff is size: a BRIN index on a table with hundreds of millions of rows can be a few hundred kilobytes, where a B-tree would be gigabytes. The cost is precision: Postgres still reads whole block ranges, so results are fast for wide time windows but not for single-row lookups.

Avoid when: rows are updated or inserted out of order (the min/max ranges overlap and the index skips little), or when you need point lookups. For time-series tables that grow without limit, partitioning plus BRIN or B-tree per partition is a strong combination.

#Decision table

#Variations that matter as much as the type

#Partial indexes

Index only the rows you query:

create index orders_pending_idx on public.orders (created_at) where status = 'pending';

If 2% of orders are pending, this index is about 2% of the size and stays hot in memory.

#Expression indexes

Index the result of an expression; the query must use the same expression:

create index users_email_lower on public.users (lower(email));
select * from public.users where lower(email) = lower($1);

#Covering indexes

Add columns with include so Postgres can answer from the index alone (an index-only scan):

create index orders_customer_cover on public.orders (customer_id) include (status, total);

#Build without blocking writes

create index blocks writes to the table while it builds. On a live table use:

create index concurrently orders_customer_idx on public.orders (customer_id);

#Indexes on StitchDB

Every StitchDB project is its own Postgres database, so all six types work. In Studio:

1. Database, then Indexes creates btree, gin, gist, hash or brin indexes without SQL, and drops them.
2. The SQL Editor runs create index concurrently as-is, outside the wrapping transaction, and warns before a plain create index that would block writes on a large table (Safe schema changes).
3. Advisors flags foreign keys without an index and tables without a primary key (Advisors).
4. Database, then Query performance shows which statements take the most time, so you index for real traffic rather than guesses (Query performance).

The pg_trgm, btree_gin, btree_gist, bloom, hypopg and index_advisor extensions are all in the available extensions. The last two let you test a hypothetical index before building it; the walk-through is in How to find and fix slow Postgres queries.

#FAQ

#What is the default index type in Postgres?

B-tree. create index ... on t (col) without using creates a B-tree, and primary keys and unique constraints are B-trees too.

#When should I use a GIN index in Postgres?

When one value contains many searchable parts: jsonb documents, arrays, full-text tsvector columns, or text you search by substring with pg_trgm. GIN does not support sorting or range queries on scalars.

#Is a hash index faster than a B-tree?

Sometimes, for exact matches on long values, because it stores a fixed-size hash. But it cannot do ranges, sorting, uniqueness or multiple columns. Measure before switching; B-tree is the safer default.

#When is BRIN better than B-tree?

On very large tables whose rows are physically in the order of the indexed column, such as append-only logs by timestamp. BRIN is tiny and cheap to maintain, but much less precise for single-row lookups.

#Do indexes slow down inserts?

Yes. Every index is updated on every insert, and on updates that touch indexed columns. GIN indexes cost the most per write. Drop indexes that no query uses.

Create and test indexes on a real database: start a free StitchDB project.