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), a pattern with a fixed prefix (LIKE 'prefix%'), and IS NULL / IS NOT NULL checks. It is exactly what backs UNIQUE constraints and primary keys.

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);

Choose hash only when you have measured confirmation that it is faster than B-tree in your specific 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 only the containment operator @>, but takes up less space and works faster — choose it if you do not need 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. For example, you cannot book one room for overlapping periods.

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.
• BRIN does not speed up point lookups (=).

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%' — it does not know where the substring starts. The pg_trgm extension solves this problem.

A trigram is a triple of consecutive characters. The string 'ivanov' breaks into the trigrams 'iva', 'van', 'ano', 'nov'. 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
SELECT * FROM customer WHERE similarity(full_name, 'ivnv') > 0.4;

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 ('NEW', 'PAID', 'SHIPPED');

-- the query uses the index only if it contains the same condition
SELECT * FROM orders
WHERE customer_id = ?
  AND status IN ('NEW', 'PAID', 'SHIPPED');

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 entire document as a whole — which is almost never needed. For queries on fields inside the document (@>, ?), you need GIN.

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

LIKE '%X%' without pg_trgm. The query will run via a full scan. You need a GIN index with the gin_trgm_ops operator class.

BRIN on a table with updates. If rows are not inserted in ascending order of the indexed field, BRIN loses effectiveness. Use B-tree.

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 for comparisons, sorting, ranges, UNIQUE, and foreign keys.
• Hash — can only do =, in practice no better than B-tree; take it only with a measured advantage.
• 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. Size in kilobytes for gigabytes of data.
• SP-GiST — IP addresses and URL prefixes. Appears rarely.
• pg_trgm + GIN solves LIKE '%substring%' and fuzzy search.
• Partial index — add a WHERE if most of the table's rows never 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.