GIN, GiST and BRIN: When a B-tree Is the Wrong Tool

A B-tree answers "equal to", "less than" and "sorted by" on one value per row. Some questions are not that shape. Which products have the tag gift? Which names contain mug anywhere in the middle? Does this booking overlap another one in the same room? Which rows of a 500 GB log are from last Tuesday? A B-tree is a poor fit or cannot do any of these. PostgreSQL has other index types, called access methods, for them. This lesson shows GIN, GiST and BRIN with a real table for each, and says when to leave them alone.

The tables

pg_trgm and btree_gist are extensions that ship with PostgreSQL. On a managed service you may need to enable them first.

CREATE EXTENSION pg_trgm;
CREATE EXTENSION btree_gist;
CREATE TABLE big_products AS
SELECT g AS product_id,
       (ARRAY['Notebook', 'Gel Pen', 'Desk Lamp', 'Mug', 'Backpack', 'Sleeve', 'Cable', 'Stand', 'Mouse', 'Sticker'])[(g % 10) + 1] || ' ' || g AS name,
       CASE WHEN g % 50 = 0 THEN ARRAY['gift', 'sale']
            WHEN g % 25 = 0 THEN ARRAY['gift']
            WHEN g % 10 = 0 THEN ARRAY['sale']
            ELSE ARRAY['regular'] END AS tags
FROM generate_series(1, 20000) AS g;
ALTER TABLE big_products ADD PRIMARY KEY (product_id);
ANALYZE big_products;
SET max_parallel_workers_per_gather = 0;

CREATE EXTENSION
CREATE EXTENSION
SELECT 20000
ALTER TABLE
ANALYZE
SET

GIN: many keys inside one value

GIN (Generalized Inverted Index) stores each element of a value, such as each tag in an array, with a list of the rows that contain it. It answers "which rows contain this?" Arrays, jsonb, full-text search and trigram search all work this way.

EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT product_id FROM big_products WHERE tags @> ARRAY['gift'];
CREATE INDEX big_products_tags_gin ON big_products USING gin (tags);
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT product_id FROM big_products WHERE tags @> ARRAY['gift'];

                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on big_products (actual rows=800.00 loops=1)
   Filter: (tags @> '{gift}'::text[])
   Rows Removed by Filter: 19200
(3 rows)

CREATE INDEX
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Bitmap Heap Scan on big_products (actual rows=800.00 loops=1)
   Recheck Cond: (tags @> '{gift}'::text[])
   Heap Blocks: exact=200
   ->  Bitmap Index Scan on big_products_tags_gin (actual rows=800.00 loops=1)
         Index Cond: (tags @> '{gift}'::text[])
         Index Searches: 1
(6 rows)

@> means "contains". Without the index PostgreSQL read all 20,000 rows. With it, the Bitmap Index Scan found the 800 matching rows directly. A B-tree on the tags array could only compare whole arrays, so it could not answer this.

The same type serves LIKE '%mug%' with trigrams, which are three-letter pieces of the text. A B-tree cannot help with a leading wildcard, because the sort order says nothing about the middle of a word.

EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT product_id FROM big_products WHERE name LIKE '%Mug 197%';
CREATE INDEX big_products_name_trgm ON big_products USING gin (name gin_trgm_ops);
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT product_id FROM big_products WHERE name LIKE '%Mug 197%';
SELECT pg_size_pretty(pg_relation_size('big_products'))           AS table_size,
       pg_size_pretty(pg_relation_size('big_products_name_trgm')) AS trigram_index;

                      QUERY PLAN
------------------------------------------------------
 Seq Scan on big_products (actual rows=11.00 loops=1)
   Filter: (name ~~ '%Mug 197%'::text)
   Rows Removed by Filter: 19989
(3 rows)

CREATE INDEX
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Bitmap Heap Scan on big_products (actual rows=11.00 loops=1)
   Recheck Cond: (name ~~ '%Mug 197%'::text)
   Heap Blocks: exact=2
   ->  Bitmap Index Scan on big_products_name_trgm (actual rows=11.00 loops=1)
         Index Cond: (name ~~ '%Mug 197%'::text)
         Index Searches: 1
(6 rows)

 table_size | trigram_index
------------+---------------
 2048 kB    | 608 kB
(1 row)

The trigram index is the cost. It is about a third the size of the table here, and it is slower to update than a B-tree. Add a GIN index only for a question you really ask.

GiST: overlap, distance and "no two may overlap"

GiST (Generalized Search Tree) indexes values that have shape: ranges, geometry, and anything with "overlaps" or "nearest to". Its best everyday use is an exclusion constraint, which is like UNIQUE but with a different test than equal. Here no room may be booked twice for overlapping dates.

CREATE TABLE bookings (
  booking_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room       text NOT NULL,
  stay       daterange NOT NULL,
  EXCLUDE USING gist (room WITH =, stay WITH &&)
);
INSERT INTO bookings (room, stay) VALUES
  ('R1', '[2026-11-01,2026-11-05)'),
  ('R2', '[2026-11-01,2026-11-05)'),
  ('R1', '[2026-11-05,2026-11-08)');
INSERT INTO bookings (room, stay) VALUES ('R1', '[2026-11-04,2026-11-06)');

CREATE TABLE
INSERT 0 3
ERROR:  conflicting key value violates exclusion constraint "bookings_room_stay_excl"
DETAIL:  Key (room, stay)=(R1, [2026-11-04,2026-11-06)) conflicts with existing key (room, stay)=(R1, [2026-11-01,2026-11-05)).

A range such as [2026-11-01,2026-11-05) includes the first day and not the last, so a guest can arrive on the day another leaves, as the third row shows. && is the overlap operator. The fourth insert overlaps the first booking of room R1, so PostgreSQL refuses it, with no application code and no race between two users. The room WITH = part needs the btree_gist extension, because plain GiST has no equality for text.

BRIN: a tiny index for ordered data

BRIN (Block Range Index) stores only the minimum and maximum of each group of table pages. It works when the column follows the physical order of the table, like a log that is only ever appended to. pg_stats has a number for that, correlation, where 1 means perfectly ordered.

CREATE TABLE access_log AS
SELECT g AS log_id, DATE '2026-01-01' + (g / 1440) AS log_day, repeat('x', 60) AS path
FROM generate_series(1, 30000) AS g;
ANALYZE access_log;
CREATE INDEX access_log_btree ON access_log (log_day);
CREATE INDEX access_log_brin ON access_log USING brin (log_day) WITH (pages_per_range = 4);
SELECT pg_size_pretty(pg_relation_size('access_log_btree')) AS btree,
       pg_size_pretty(pg_relation_size('access_log_brin'))  AS brin;
SELECT attname, correlation FROM pg_stats WHERE tablename = 'access_log' AND attname = 'log_day';

SELECT 30000
ANALYZE
CREATE INDEX
CREATE INDEX
 btree  | brin
--------+-------
 224 kB | 24 kB
(1 row)

 attname | correlation
---------+-------------
 log_day |           1
(1 row)

The BRIN index is a tenth of the B-tree. The default summarises 128 pages at a time, which suits a table of many gigabytes. This one is only a few megabytes, so pages_per_range = 4 makes the demo visible. Drop the B-tree and look at the plan:

DROP INDEX access_log_btree;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM access_log WHERE log_day = DATE '2026-01-10';

DROP INDEX
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   ->  Bitmap Heap Scan on access_log (actual rows=1440.00 loops=1)
         Recheck Cond: (log_day = '2026-01-10'::date)
         Rows Removed by Index Recheck: 504
         Heap Blocks: lossy=24
         ->  Bitmap Index Scan on access_log_brin (actual rows=240.00 loops=1)
               Index Cond: (log_day = '2026-01-10'::date)
               Index Searches: 1
(8 rows)

BRIN does not know which rows match. It knows which groups of pages can contain them, so it hands whole groups to the table scan (lossy) and PostgreSQL rechecks every row. Rows Removed by Index Recheck is the extra reading. That is the trade: a tiny index and some wasted reads. If the column is not ordered, a BRIN index summarises everything as "anything is possible" and the planner ignores it.

Which one

Common mistakes

• Using a B-tree on an array, a jsonb document or a LIKE '%x%' search, then wondering why it is ignored.
• Adding GIN to a table with heavy writes without checking the write cost.
• Using BRIN on a column that is not in physical order.
• Checking an overlap rule in application code instead of with an exclusion constraint.

If you skip this, you will keep forcing every question through a B-tree, or you will add a big GIN index where a small partial one would do. Which of your slow LIKE '%...%' searches would be better served by an index built for it?