GIN, GiST and BRIN in PostgreSQL: which index to choose? Intermediate

🎮 Try it

• Choose an index type: GIN for jsonb and LIKE, BRIN for append-only tables.

Quick summary: which operator needs which index

A B-tree only answers ordered comparison questions (<, <=, =, >=, >, see B-tree index). Everything else needs another access method. Choose by the operator in WHERE:

You need	Operator / function	Index	Notes
=, <, BETWEEN, ORDER BY	= < > BETWEEN	B-tree	The default for most cases
Arrays, JSONB containing elements	@>, <@, &&	GIN	JSONB: jsonb_ops or jsonb_path_ops
Full-text search	@@ on tsvector	GIN	Index the tsvector column
LIKE '%x%', similarity	LIKE, ILIKE, %	GIN + pg_trgm	gin_trgm_ops
Overlapping ranges/geometry	&&, @>, <@	GiST	Also used for exclusion constraints
Nearest neighbor (kNN)	ORDER BY col <-> x LIMIT n	GiST (or SP-GiST)	Depends on the operator class
Huge table, column correlated with write order	<, <=, =, >=, >	BRIN	Only when correlation is near ±1
Equality only	=	Hash	Rarely worth replacing a B-tree

Loading diagram…

What the three index types store

Index	Stores	Recheck?
GIN	One entry per component inside the value (each tag, each JSON key, each word)	Depends on the operator class
GiST	A generalized tree, each node has a “bounding region” defined by the operator class	Depends on the operator class
BRIN	A min/max summary for each group of physically adjacent pages	Always (lossy)

The PostgreSQL docs describe BRIN as lossy: the executor always rechecks rows. GIN and GiST only need a recheck with some operator classes. Even a B-tree can become lossy in a bitmap scan when work_mem runs short.

How it works

1. GIN: split composite values into components

Searching for the tag rare (100 of 100,000 rows) without an index scans the whole table:

Seq Scan on search_demo  (cost=0.00..2439.12 rows=482 width=4) (actual time=0.139..6.438 rows=100 loops=1)
  Filter: (tags @> '{rare}'::text[])
  Rows Removed by Filter: 99900
Execution Time: 6.453 ms

After CREATE INDEX ... USING GIN (tags):

Bitmap Heap Scan on search_demo  (cost=46.06..300.25 rows=80 width=4) (actual time=0.025..0.159 rows=100 loops=1)
  Recheck Cond: (tags @> '{rare}'::text[])
  Heap Blocks: exact=100
  ->  Bitmap Index Scan on search_demo_tags_gin
        Index Cond: (tags @> '{rare}'::text[])
Execution Time: 0.181 ms

6.453 ms down to 0.181 ms, 36x faster. GIN is an inverted index: each element has a list of rows containing it, so finding rare is a direct lookup of that list.

backend -> [1, 2, 3, ..., 100000]     (every row)
general -> [1, 2, 3, ...]             (most rows)
rare    -> [1000, 2000, 3000, ...]    (100 rows)

Two more GIN examples, measured on 200,000 rows (one run, real table):

Use case	No index	With GIN
Full-text search: tsv @@ to_tsquery('simple','postgresql') (40 matching rows)	9.488 ms (Seq Scan)	0.082 ms (Bitmap)
LIKE '%number 12345 %' with pg_trgm (gin_trgm_ops, 1 matching row)	15.433 ms (Seq Scan)	0.980 ms (Bitmap)

LIKE '%x%' is where a B-tree gives up (see B-tree index); pg_trgm solves it by indexing every three-character sequence.

JSONB has two operator classes:

Operator class	Supports	Size (200,000 docs)
jsonb_ops (default)	@>, ?, ?|, ?& (and jsonpath @?, @@)	3,144 kB
jsonb_path_ops	only @> (and jsonpath @?, @@)	2,488 kB

If you only need @>, jsonb_path_ops is smaller and faster.

GIN’s write cost (per the docs): fastupdate is on by default, so new entries go to a pending list and are merged in bulk when VACUUM/autoanalyze runs or when gin_pending_list_limit is exceeded. Consequences:

• Searches must also scan the pending list.
• Any single INSERT can suddenly slow down because it has to flush the list. If you need stable latency: WITH (fastupdate = off), or let autovacuum clean up in the background.
• GIN index builds are very sensitive to maintenance_work_mem; for bulk loads, create the index after loading.

GIN cannot serve ORDER BY: results do not come back in index order.

2. GiST: a tree with bounding regions per operator class

Find int4range ranges overlapping [42000, 42010), without an index:

Seq Scan on search_demo  (cost=0.00..2485.00 rows=21 width=4) (actual time=2.348..5.085 rows=19 loops=1)
  Filter: (during && '[42000,42010)'::int4range)
Execution Time: 5.088 ms

With CREATE INDEX ... USING GiST (during):

Bitmap Heap Scan on search_demo  (cost=4.44..77.05 rows=20 width=4) (actual time=0.034..0.036 rows=19 loops=1)
  Recheck Cond: (during && '[42000,42010)'::int4range)
  ->  Bitmap Index Scan on search_demo_during_gist
        Index Cond: (during && '[42000,42010)'::int4range)
Execution Time: 0.053 ms

5.088 ms down to 0.053 ms, about 96x faster. The range operator class teaches GiST how to compute the bounding region of a group of ranges and test whether two regions overlap, so it prunes whole branches that cannot contain results.

GiST has two more uses senior engineers reach for:

• kNN: ORDER BY location <-> point '(x,y)' LIMIT 10 fetches the 10 nearest points straight from the index (depending on the operator class).
• Exclusion constraints: forbid overlapping data at the database level. For example, room bookings must not overlap (requires btree_gist to use = on the room column):

CREATE TABLE booking (
  room int NOT NULL,
  during tstzrange NOT NULL,
  EXCLUDE USING gist (room WITH =, during WITH &&)
);

Inserting [09:30, 10:30) into room 1, which already has [09:00, 10:00), fails, while room 2 still accepts it:

ERROR:  conflicting key value violates exclusion constraint "booking_room_during_excl"
DETAIL:  Key (room, during)=(1, ["2026-10-20 09:30:00+00","2026-10-20 10:30:00+00")) conflicts with existing key (room, during)=(1, ["2026-10-20 09:00:00+00","2026-10-20 10:00:00+00")).

This is the proper, race-free way to prevent double booking, something UNIQUE cannot do.

3. BRIN: only good when data correlates with physical storage order

BRIN is the only index type whose effectiveness depends on how the data was written, not just on the index structure.

BRIN: min/max summaries prune ranges when data is sequential, but not when it is shuffled

Enlarge diagram

Range 1 · Range 2 · Range 3 · Range 1 · Range 2 · Range 3

◀ Prev▶ PlayNext ▶↺ RestartStep 1/3

BRIN only remembers the min/max of each page group. Top row: data written sequentially. Bottom row: the same data, physically shuffled.

The key parameter: pages_per_range. The original demo below uses pages_per_range = 16, not the default. The default is 128 pages (1 MB) per range. Smaller values give finer summaries and a larger index.

Measured on a real 1,000,000-row table (143 MB), ascending created_at, a 10-minute query (600 rows):

Configuration	Index size	Heap blocks read	Rows removed by recheck	Time
Default BRIN (pages_per_range = 128)	24 kB	128 (lossy)	6,440	0.28 ms
BRIN pages_per_range = 16	48 kB	32 (lossy)	1,160	0.14 ms

Heap Blocks: lossy=128 matches the default pages_per_range exactly. The 16 and 24 kB figures in the demo below depend on that configuration; copy it with the default and you will get different numbers.

Original demo (100,000 rows, pages_per_range = 16). The table has created_at ascending in insert order:

Bitmap Heap Scan on search_demo  (cost=12.16..1314.23 rows=611 width=4) (actual time=0.033..0.071 rows=600 loops=1)
  Recheck Cond: ((created_at >= '2026-01-02 02:00:00+00') AND (created_at < '2026-01-02 02:10:00+00'))
  Heap Blocks: lossy=16
  ->  Bitmap Index Scan on search_demo_created_brin
Execution Time: 0.092 ms

Same data but with shuffled physical order (ORDER BY random() when copying), same BRIN:

Seq Scan on search_demo_shuffled  (cost=0.00..2780.00 rows=599 width=4) (actual time=0.006..2.891 rows=600 loops=1)
  Filter: ((created_at >= '2026-01-02 02:00:00+00') AND (created_at < '2026-01-02 02:10:00+00'))
  Rows Removed by Filter: 99400
Execution Time: 2.909 ms

Both indexes are 24 kB and perfectly healthy, yet 0.092 ms vs 2.909 ms, 31x slower, purely because of physical order.

On the shuffled 1,000,000-row table, the planner still uses BRIN but has to read 18,192 blocks, i.e. the whole table, removing 999,400 rows in the recheck step, 89 ms in total.

Diagnosis: pg_stats.correlation

Before creating a BRIN, and periodically afterwards, check:

SELECT correlation FROM pg_stats WHERE tablename = 'log_demo' AND attname = 'created_at';

correlation near +1 or -1 is good for BRIN; near 0 makes BRIN useless. Real measurements:

Situation	correlation
Append-only by time	1
Fully shuffled	0.008
Delete 1/3 of rows evenly, VACUUM, then insert new rows (backfilling the gaps)	0.547

The main correlation killer is not UPDATE but DELETE followed by VACUUM: new rows fill the gaps in the middle of the table. Backfilling old data and parallel batched inserts have the same effect. For tables with retention via scattered DELETEs, time-based partitioning is usually a cleaner solution.

Operating BRIN: new ranges have no summary until VACUUM runs; you can enable autosummarize (off by default) or call brin_summarize_new_values(). Besides minmax, there are the minmax-multi, inclusion and bloom operator classes for less correlated data.

When to use / when not to

Index	Use when	Do not use when
GIN	“Contains element” searches on arrays/JSONB/tsvector, LIKE '%x%' with trigrams	Very heavy writes needing stable latency (consider fastupdate = off); you need ORDER BY
GiST	Overlapping ranges/geometry, kNN, exclusion constraints	Only =/< comparisons: a B-tree is faster and smaller
BRIN	Huge append-only log tables, correlation near ±1	Shuffled data, lots of updates/deletes/backfills

Real-world example

access_logs with 50 million rows/month, insert only

A BRIN on created_at costs a few dozen kB instead of the hundreds of MB of a B-tree and still filters time ranges well, because natural insert order correlates with time.

If a “fix old logs” job or scattered deletes appear later, the correlation erodes without any warning. Schedule a weekly check of pg_stats.correlation; if it drops below about 0.9, consider daily partitions and DROPping old partitions instead of DELETE.

Trade-offs

Pros

• GIN is 36x faster in the demo for containment, over 100x for full-text, and is the answer for LIKE '%x%'
• GiST is about 96x faster for range overlap, and supports kNN and exclusion constraints
• BRIN is tiny (24 kB for 1 million rows by default), hundreds of times cheaper than a B-tree when correlation is good

Cons

• GIN has a write cost (pending list, sudden cleanups) and builds slowly without enough maintenance_work_mem
• GiST only supports the operators its operator class defines
• BRIN loses its effect as correlation drops, with no warning; you just see queries slowly getting slower

Common mistakes

Common pitfall

Creating a BRIN on a timestamp column just because the table is large. Check pg_stats.correlation first, and keep monitoring after DELETEs or backfills. Do not copy the “24 kB” or “lossy=16” figures from the demo and forget that they depend on pages_per_range.

Checklist before creating a GIN, GiST or BRIN index

1. Identify the operator in WHERE and check the summary table at the top; for ordered comparisons, a B-tree is still the first choice.
2. GIN on JSONB using only @>: choose jsonb_path_ops (smaller) over the default jsonb_ops.
3. GIN on a write-heavy table needing stable latency: consider fastupdate = off; for bulk loads, create the index after loading.
4. To prevent overlaps (room bookings, schedules): use EXCLUDE USING gist (with btree_gist for = columns), not an application-side check.
5. BRIN: check that pg_stats.correlation is near ±1 before creating it, and monitor periodically after DELETEs or backfills.
6. BRIN: choose pages_per_range deliberately (default 128) and handle unsummarized ranges with autosummarize or brin_summarize_new_values().

Summary

Key takeaways

• Choose the index by the operator in WHERE: ordered comparison is B-tree, element containment is GIN, overlap and kNN are GiST, huge correlated log tables are BRIN
• GIN: an inverted index, 36x faster (array) and over 100x (full-text) in the demo; remember the pending list and fastupdate
• GiST: about 96x faster for range overlap, and the foundation of EXCLUDE USING gist against overlapping bookings
• BRIN: the demo uses pages_per_range = 16, the default is 128; check with pg_stats.correlation, only worth it near ±1
• DELETE followed by VACUUM and backfills destroy correlation: from 1 down to 0.547 in the demo

Self-check

1. For WHERE tags @> ARRAY['rare'] and WHERE created_at BETWEEN ... on a 1-billion-row log table, which index do you choose for each query?
2. Why does Heap Blocks: lossy=128 match the default pages_per_range?
3. How do you tell that a BRIN is gradually losing its effect?
4. Why is an exclusion constraint with GiST better at preventing double booking than an application-side check?

References

• PostgreSQL 17 — Index Types
• PostgreSQL 17 — GIN Indexes
• PostgreSQL 17 — GiST Indexes
• PostgreSQL 17 — BRIN Indexes
• PostgreSQL 17 — pg_trgm