PostgreSQL Index Types — B-tree, GIN, GiST, BRIN

← Back to the section

Without an index, the database reads every row of the table on every query. On a thousand rows this is unnoticeable. On a million, the query takes seconds instead of milliseconds. On tens of millions, minutes.

An index is a separate data structure that PostgreSQL builds and maintains alongside the table. It lets you find the rows you need without reading everything in sequence.

PostgreSQL supports six kinds of indexes. Each is built differently and works well on its own class of tasks. The right choice can sometimes speed up a query hundreds of times.

B-tree — the default index

When you write just CREATE INDEX, PostgreSQL creates a B-tree. It is a balanced tree where each node holds a range of values. The database walks down the tree from the root to the right leaf — in O(log N) steps, not O(N).

CREATE INDEX ix_orders_created_at ON orders (created_at);
-- the same thing with an explicit type:
CREATE INDEX ix_orders_created_at ON orders USING btree (created_at);

B-tree works with comparison operators (=, <, <=, >, >=), ranges (BETWEEN, IN), sorting (ORDER BY), IS NULL / IS NOT NULL checks, and — with a caveat — prefix search (LIKE 'prefix%'). The caveat matters: a plain index speeds that LIKE up only in a database with the C collation. Under any other collation the character order differs, and prefix search needs a separate index with the text_pattern_ops operator class. UNIQUE constraints and primary keys, meanwhile, are always backed by a B-tree.

One such index covers both the range filter and the sorting: the leaves already lie in the right order, so no separate sort step follows.

live example

SELECT id, status, created_at
FROM orders
WHERE created_at >= DATE '2026-08-10' AND created_at < DATE '2026-08-20'
ORDER BY created_at;

For the vast majority of ordinary tables, B-tree is the only type you need.

Hash — almost never needed

A hash index stores hashes of values and can only check for exact equality (=). It would be logical to assume it is faster than B-tree where only = is needed. In practice:

• B-tree performs comparably on exact equality.
• Hash does not support sorting, ranges, or composite queries.
• Before PostgreSQL 10, a hash index was not written to the WAL log and could get corrupted on a crash.

-- a rare use case
CREATE INDEX ix_account_email_hash ON account USING hash (email);

Take hash only with a measurement showing it beats B-tree in your case.

GIN — for JSONB, arrays, and full-text search

GIN stands for Generalized Inverted Index. The principle is the same as in a search engine: for each "token" (a JSONB key, an array element, a word), it stores a list of rows where that token appears.

-- Search over JSONB
CREATE INDEX ix_event_payload ON event_log USING gin (payload jsonb_path_ops);

-- Search over an array of tags
CREATE INDEX ix_article_tags ON article USING gin (tags);

-- Full-text search
CREATE INDEX ix_post_search ON post USING gin (to_tsvector('english', body));

Characteristic properties of GIN:

• Reads are fast — find the rows containing a given key or word in a single pass.
• Writes are slower than in B-tree: changing a single row can touch many entries in the index.
• There is a fastupdate parameter — a buffer of deferred changes that speeds up inserts at the cost of a small delay on reads.

For JSONB columns there are two operator classes: the standard one (jsonb_ops) and the compact one (jsonb_path_ops). The compact one supports containment @> and jsonpath queries (@?, @@), while taking up less space and working faster — choose it unless you need key-existence checks with the ? and ?| operators.

GiST — ranges, geometry, exclusions

GiST (Generalized Search Tree) is designed for data types that have no linear ordering: geometric shapes, time ranges, IP networks.

-- EXCLUDE constraint: no overlaps in booking a room
CREATE EXTENSION btree_gist;
CREATE TABLE booking (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id bigint NOT NULL,
    period tstzrange NOT NULL,
    EXCLUDE USING gist (room_id WITH =, period WITH &&)
);

-- Nearest points on a map (kNN)
CREATE INDEX ix_shop_location ON shop USING gist (location);
SELECT * FROM shop ORDER BY location <-> ST_Point(37.6, 55.7) LIMIT 10;

GiST supports nearest-neighbor search (ORDER BY x <-> point) and the EXCLUDE constraint — a guarantee that records do not overlap.

Compared to GIN: GiST writes faster, reads slower, and takes up less space.

When GIN and when GiST

Both are suitable for full-text search, but they behave differently:

• GIN — fast reads, slow writes, large size. Choose it when the table is read more often than written.
• GiST — fast writes, slower reads, compact. Choose it for frequent updates.

For JSONB and arrays, GIN is usually preferable. GiST is taken when you need specific capabilities: kNN, EXCLUDE, geometry.

BRIN — for huge log tables

BRIN (Block Range Index) is an index for tables where rows are physically arranged in ascending order of some value. A typical example is an event table with an occurred_at field.

The idea: instead of indexing every row, BRIN remembers the minimum and maximum value for each range of pages (128 pages by default). On a query "events for the last hour," the database discards all ranges whose maximum is earlier than the required time.

CREATE INDEX ix_event_log_at_brin ON event_log USING brin (occurred_at);

BRIN's main advantage is size: a few dozen kilobytes for a multi-gigabyte table. Yet range queries run fast.

Limitations:

• It works only with physical ordering. If rows are inserted out of order, BRIN will not help. If you need to order them, use CLUSTER.
• Point lookups (=) it barely speeds up: the whole 128-page range gets read anyway.

Good for: logs and events with an automatically increasing timestamp, metrics, archival table partitions.

Not good for: tables with updates, point lookups.

SP-GiST — a rare case

Space-Partitioned GiST splits the value space into uneven parts — suitable for IP addresses and URLs with common prefixes.

CREATE INDEX ix_request_url ON request USING spgist (url);
CREATE INDEX ix_visit_ip ON visit USING spgist (ip);

In an ordinary application it appears extremely rarely.

Decision table

pg_trgm for substring search

An ordinary B-tree cannot do LIKE '%word%'. To see why, look at a search by the beginning of a string — that one the tree handles:

live example

SELECT 'LIKE ''K%''' AS how, last_name
FROM customer
WHERE last_name LIKE 'K%'
UNION ALL
SELECT 'range K..L', last_name
FROM customer
WHERE last_name >= 'K' AND last_name < 'L';

Both halves return the same surnames: LIKE 'K%' is the range from K to L, and a range is one walk down the tree. A substring has no range — 'anov' may start anywhere inside the value, so there is nothing to walk down to. That is the job of the pg_trgm extension.

A trigram is a triple of consecutive characters. The string 'ivanov' breaks into the trigrams 'iva', 'van', 'ano', 'nov', plus the edge ones — the extension mentally pads it with two spaces in front and one behind, giving ' i', ' iv', 'ov '. Those edge trigrams are what let such an index serve prefix search too. A GIN index stores which rows each trigram appears in. On a LIKE '%anov%' search, the database finds rows containing the required trigrams.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX ix_customer_name_trgm
    ON customer USING gin (full_name gin_trgm_ops);

-- speeds up substring search
SELECT * FROM customer WHERE full_name ILIKE '%ivan%';

-- and fuzzy search: the % operator, not the similarity() function
SELECT * FROM customer WHERE full_name % 'ivnv';

This is an easy place to get it wrong. The condition similarity(full_name, 'ivnv') > 0.4 reads clearer, but no index speeds it up: the database cannot look inside a function call and computes the similarity for every row. The index works through the % operator, whose threshold is set separately — pg_trgm.similarity_threshold, 0.3 by default.

Partial index

A partial index indexes not the whole table, but only the rows matching a WHERE condition. This lets you make the index significantly smaller and faster.

A typical situation: an orders table where 90% of rows have status COMPLETED, while queries mostly work with active orders.

-- Index only active orders
CREATE INDEX ix_orders_active_customer
    ON orders (customer_id)
    WHERE status IN ('PENDING_PAYMENT', 'PAID', 'SHIPPED');

The query reaches such an index only if the planner can derive the index condition from the query's own. The simplest way is to repeat it:

live example

SELECT id, status, total_amount
FROM orders
WHERE customer_id = 'cus-01'
  AND status IN ('PENDING_PAYMENT', 'PAID', 'SHIPPED')
ORDER BY created_at;

A partial index applies to any type. Its size is smaller and writes are faster — updating a row does not touch the index if the row does not match the condition.

Common mistakes

B-tree on JSONB. PostgreSQL will not refuse, but such an index only helps with = on the whole document — almost never what you want. Queries on fields inside it (@>, ?) need GIN.

B-tree on an array. Same story: B-tree does not understand "is element X present in the array." You need GIN.

A full index where a partial one is needed. If 90% of rows never appear in queries on this field, a full index wastes space and slows down writes for no benefit.

In short

• B-tree — the default choice: comparisons, sorting, ranges, UNIQUE, foreign keys.
• GIN — JSONB, arrays, full-text search. Fast reads, slow writes.
• GiST — ranges, geometry, EXCLUDE, kNN. Fast writes, slower reads.
• BRIN — logs and metrics with an increasing timestamp: kilobytes of index per gigabytes of data, but only where rows are physically ordered.
• Hash and SP-GiST — narrow cases (exact equality; IP and URL prefixes), not needed in an ordinary application.
• pg_trgm + GIN solves LIKE '%substring%' and fuzzy search, and a partial index solves the table where only a minority of rows ever appear in queries.

What to read next

• Composite indexes and column order — how the leftmost-prefix rule works.
• How to choose an index: selectivity and EXPLAIN — when an index helps and when it does not.
• JSONB in PostgreSQL — more on GIN and operators for documents.
• Full-text search in PostgreSQL — tsvector, tsquery, ranking.