Catalog My learning Blog
PostgreSQL Course: Beginner to Production in 30 Days · Phase 3 · Lesson 23 · 7 min read

PostgreSQL Index Types: GIN, GiST, BRIN, Hash

On this page
  1. Choosing a PostgreSQL index type
  2. GIN: "which rows contain this?"
  3. GiST: overlaps and "no double bookings"
  4. BRIN: tiny indexes for huge, ordered tables
  5. Hash indexes: equality only
  6. Partial and expression indexes
  7. Check yourself

Choosing a PostgreSQL index type

B-tree (Day 11) handles equality, ranges and sorting on ordinary values, and it's the right default. But some questions don't fit a sorted list. "Which rows contain this tag?" "Does this booking overlap another?" "Which rows from last Tuesday, in a 2-billion-row log?" PostgreSQL has a specialized index type for each of those.

IndexBest forTypical operators
B-treeEquality, ranges, ORDER BY=, <, >, BETWEEN
GINValues that contain many items: arrays, jsonb, tsvector@>, ?, &&, @@
GiSTOverlap and distance: ranges, geometry, nearest neighbor&&, @>, <->
BRINHuge tables where the column follows physical row order=, <, > on time or id
HashEquality only=

Start from the query's operator, not the column type. A jsonb column filtered only by ->> 'status' = wants a B-tree expression index, not GIN.

GIN: "which rows contain this?"

A GIN index (Generalized Inverted Index) flips the usual mapping. Instead of "row → values", it stores "value → list of rows", like the index at the back of a book. That's ideal when one row holds many searchable items:

SQL
CREATE TABLE tickets (id int PRIMARY KEY, tags text[] NOT NULL);
CREATE INDEX tickets_tags_idx ON tickets USING gin (tags);
SELECT id FROM tickets WHERE tags @> ARRAY['urgent'];          -- contains
SELECT id FROM tickets WHERE tags && ARRAY['billing', 'refund']; -- overlaps

You've already used GIN for jsonb on Day 18 and for full-text search on Day 19. One more use earns a place here. The pg_trgm extension splits text into three-letter chunks, and a GIN index on those makes ILIKE '%...%' searches fast, the ones a B-tree can never help:

SQL
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE TABLE notes (id int PRIMARY KEY, body text NOT NULL);
INSERT INTO notes SELECT g, 'invoice ' || g || ' ' || md5(g::text) FROM generate_series(1, 100000) AS g;
CREATE INDEX notes_body_trgm_idx ON notes USING gin (body gin_trgm_ops);
ANALYZE notes;
EXPLAIN SELECT id FROM notes WHERE body ILIKE '%invoice 4471%';
-- Bitmap Index Scan on notes_body_trgm_idx

The cost is writes. One inserted row can update many entries in a GIN index. PostgreSQL softens this with a "pending list" that batches new entries and merges them later, during VACUUM or when the list fills up. GIN can't do index-only scans either.

GiST: overlaps and "no double bookings"

GiST (Generalized Search Tree) is built for values that overlap or have a distance between them: ranges, shapes and map points through PostGIS. Its most useful trick is the exclusion constraint, a generalized UNIQUE. UNIQUE says no two rows may be equal. EXCLUDE says no two rows may match a condition you choose, like "same room and overlapping time":

SQL
CREATE EXTENSION IF NOT EXISTS btree_gist;   -- lets GiST handle plain = on integers

CREATE TABLE reservations (
  id       int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id  int NOT NULL,
  during   tstzrange NOT NULL,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

INSERT INTO reservations (room_id, during) VALUES (5, '[2026-08-10 14:00, 2026-08-10 16:00)');
INSERT INTO reservations (room_id, during) VALUES (5, '[2026-08-10 15:00, 2026-08-10 17:00)');
-- ERROR: conflicting key value violates exclusion constraint
INSERT INTO reservations (room_id, during) VALUES (5, '[2026-08-10 16:00, 2026-08-10 17:00)');
-- OK: [ ) means 16:00 is the end of the first booking, not part of it

This rule is very hard to enforce correctly in application code. Two requests can each check "free?" at the same moment and both book. The database checks atomically, so no race is possible.

GiST also powers nearest-neighbor search. ORDER BY location <-> point(-73.99, 40.73) LIMIT 10 finds the 10 closest points without sorting the whole table.

BRIN: tiny indexes for huge, ordered tables

A BRIN index (Block Range Index) doesn't index rows at all. For each run of table pages (128 by default) it stores only the smallest and largest value. A query skips every range whose min and max can't match. On a 2-million-row event table where created_at rises with each insert:

SQL
CREATE TABLE events (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, created_at timestamptz NOT NULL, kind text NOT NULL);
INSERT INTO events (created_at, kind)
SELECT TIMESTAMPTZ '2026-01-01' + g * INTERVAL '10 seconds', 'k' || (g % 7)
FROM generate_series(1, 2000000) AS g;

CREATE INDEX events_created_btree ON events (created_at);
CREATE INDEX events_created_brin  ON events USING brin (created_at);
SELECT pg_size_pretty(pg_relation_size('events_created_btree')) AS btree,
       pg_size_pretty(pg_relation_size('events_created_brin'))  AS brin;
-- 43 MB | 24 kB

The BRIN index is about 1,800 times smaller, and a one-day query against it still ran in about 5 ms. The catch is in the precondition: the column must follow the physical order of the rows. That's true for timestamps or ids in append-only logs and time series. It's false for a column like customer_id, or for tables where updates scatter rows, and then every range's min and max cover almost everything and BRIN skips nothing.

Hash indexes: equality only

A hash index stores a hash of each value and supports only =. Before PostgreSQL 10 hash indexes weren't crash-safe, which is why old advice says to avoid them. Today they're safe. They can be smaller than a B-tree for long keys used only for equality lookups, like session tokens or API keys. They can't help with ranges, sorting or uniqueness constraints, though, so benchmark against a B-tree before you choose one.

Partial and expression indexes

These two work with any index type, and they solve very common problems.

A partial index covers only rows matching a WHERE clause. If 2% of orders are pending and only those are ever queried by date, index just those:

SQL
CREATE INDEX orders_pending_idx ON orders (created_at) WHERE status = 'pending';

It's a fraction of the size of a full index and cheaper to maintain. The planner only uses it when the query's own WHERE clause includes status = 'pending'.

An expression index indexes a computed value, so a query filtering on that same expression can use it. Combined with a partial index, you get a classic: emails unique regardless of case, ignoring soft-deleted users.

SQL
CREATE TABLE users (id int PRIMARY KEY, email text NOT NULL, deleted_at timestamptz);
CREATE UNIQUE INDEX users_email_active_idx ON users (lower(email)) WHERE deleted_at IS NULL;

INSERT INTO users VALUES (1, 'Ana@x.com', now());   -- deleted user
INSERT INTO users VALUES (2, 'ana@x.com', NULL);    -- OK: the first one is deleted
INSERT INTO users VALUES (3, 'ANA@x.com', NULL);    -- ERROR: duplicate key

The query must use the same expression the index was built on. WHERE lower(email) = ... uses this index. WHERE email = ... and WHERE upper(email) = ... don't.

Check yourself

A GIN trigram index from the pg_trgm extension: CREATE INDEX ... USING gin (body gin_trgm_ops). A B-tree can't help when the pattern starts with a wildcard.

Add an exclusion constraint, EXCLUDE USING gist (room_id WITH =, during WITH &&), with the btree_gist extension. The database rejects the second booking atomically, so concurrent requests can't both succeed.

A BRIN index on recorded_at. Values follow insertion order, so BRIN can skip most of the table with an index measured in megabytes, not gigabytes. Compare query times with EXPLAIN ANALYZE before dropping the B-tree.

When queries only ever look at a small slice of the table, such as WHERE status = 'pending' on an orders table where most rows are completed. CREATE INDEX ON orders (created_at) WHERE status = 'pending' is small, cheap to maintain, and exactly matches those queries.

Rarely. Hash indexes only support equality, not ranges or sorting, and since PostgreSQL 10 they're crash-safe. They can be somewhat smaller for long values like URLs used only in = lookups, but a B-tree is the safer default for almost everything.