GIN: Understanding PostgreSQL's Inverted Index and Its Limitations

This series of posts traces the evolution from GIN to RUM to Extended RUM, showing how a single architectural idea—to store more in the index to do less work at query time—unlocks major performance improvements at each step.

---

Generalized Inverted Index (GIN) is PostgreSQL's answer to the question "how do I index composite values?" — including arrays, JSONB documents, tsvector, and any data type that decomposes into multiple keys per row.

The structure is conceptually simple:

Entry Tree (B-tree of keys)
├── "alice"  → Posting List: [(0,1), (3,7), (5,2)]
├── "bob"    → Posting List: [(0,2), (4,1)]
├── "carol"  → Posting Tree (when list becomes too large for in-line storage)"
│             └── B-tree of ItemPointers
└── ...

For each indexed value, GIN calls extractValue to decompose it into keys. Each key gets an entry in the B-tree. Each entry points to a posting list — a sorted array of heap TIDs (tuple identifiers) that contain that key.

At query time, GIN calls extractQuery to decompose the search condition into keys, then intersects or unions the posting lists using a consistent function.

What GIN Gets Right:

1. Space efficiency: posting lists are compressed (variable-byte encoding), and duplicate keys are stored once.
2. Fast containment queries: @>, <@, @@ — any "does this value contain these keys?" pattern is natural.
3. Extensible operator classes: anyone can plug in new data types via extractValue/extractQuery/consistent.

Limitations of GIN:

1. Posting lists lack ordering info. GIN's lists only contain TIDs sorted by physical location, with no space for extra data.
2. Cannot perform ordered scans. GIN doesn’t support ORDER BY pushdown, and queries with sorting require an explicit sort after the index scan.
3. No extra payload per TID. Posting lists hold only 6-byte TIDs, without timestamps, scores, or additional info.
4. Only bitmap scans are supported, not true index scans. GIN generates and processes bitmaps of matching TIDs.

Implications:

• Ranking needs heap access. For ranked searches like ts_rank(), GIN finds TIDs but lacks term frequency or position info, so it must fetch and sort matching heap tuples.
• Phrase searches need reprocessing. GIN can locate tuples containing specific words, but it cannot confirm if these words are adjacent based only on the index. It identifies possible matches and then rechecks each heap tuple to ensure the words are in sequence. As clarified by Artur Zakirov (Postgres Professional), "GIN supports it, but it requires additional bitmap heap scan and so it is slower."
• No LIMIT pushdown — must scan the entire posting list even for a small number of results.
• Cursor-based iteration isn't supported.

I created the following tables that I'll use for the examples in this blog post and the next ones:

CREATE EXTENSION IF NOT EXISTS btree_gin;

DROP TABLE IF EXISTS articles;
CREATE TABLE articles (
    id          serial PRIMARY KEY,
    title       text NOT NULL,
    body        text NOT NULL,
    category    text NOT NULL,
    published   timestamp NOT NULL,
    score       int NOT NULL,
    tsv         tsvector GENERATED ALWAYS AS (
                    to_tsvector('simple', title || ' ' || body)
                ) STORED
);

INSERT INTO articles (title, body, category, published, score)
SELECT
    'Article about ' || words[1 + (i % 10)] || ' and ' || words[1 + ((i*7) % 10)],
    'This is the body discussing ' || words[1 + ((i*3) % 10)] ||
    ' in the context of ' || words[1 + ((i*11) % 10)] ||
    '. We also mention ' || words[1 + ((i*13) % 10)] || ' here.',
    categories[1 + (i % 4)],
    '2020-01-01'::timestamp + (i || ' hours')::interval,
    (i * 17) % 100
FROM generate_series(1, 1e6) AS s(i),
LATERAL (SELECT ARRAY['postgresql','indexing','performance','database',
                       'query','optimization','storage','replication',
                       'vacuum','analytics'] AS words) w,
LATERAL (SELECT ARRAY['tech','science','blog','news'] AS categories) c;

ANALYZE articles;

CREATE INDEX idx_gin_tsv ON articles USING gin (tsv);

Here is a Full-Text Search — Bitmap Only:

postgres=# EXPLAIN (COSTS OFF, ANALYZE, BUFFERS)
SELECT id, title, score
FROM articles
WHERE tsv @@ to_tsquery('simple', 'postgresql & article')
LIMIT 10
;

QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Limit (actual time=24.049..24.104 rows=10 loops=1)
   Buffers: shared hit=299 read=6
   ->  Bitmap Heap Scan on articles (actual time=24.045..24.087 rows=10 loops=1)
         Recheck Cond: (tsv @@ '''postgresql'' & ''article'''::tsquery)
         Heap Blocks: exact=6
         Buffers: shared hit=299 read=6
         ->  Bitmap Index Scan on idx_gin_tsv (actual time=16.165..16.165 rows=100000 loops=1)
               Index Cond: (tsv @@ '''postgresql'' & ''article'''::tsquery)
               Buffers: shared hit=299
 Planning:
   Buffers: shared hit=33 read=2 dirtied=7
 Planning Time: 0.314 ms
 Execution Time: 24.138 ms
(13 rows)

GIN uses Bitmap Index Scan → Bitmap Heap Scan. It never produces a true Index Scan. Even with LIMIT 10, it builds the full bitmap first on one million rows before discarding most of them to return only ten.

An ORDER BY requires a Sort operation:

postgres=# EXPLAIN (COSTS OFF, ANALYZE)
SELECT id, title, published
FROM articles
WHERE tsv @@ to_tsquery('simple', 'postgresql & article')
ORDER BY published DESC
LIMIT 5;

QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Limit (actual time=1838.940..1853.064 rows=5 loops=1)
   ->  Gather Merge (actual time=1838.937..1853.054 rows=5 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort (actual time=1812.430..1812.435 rows=4 loops=3)
               Sort Key: published DESC
               Sort Method: top-N heapsort  Memory: 26kB
               Worker 0:  Sort Method: top-N heapsort  Memory: 26kB
               Worker 1:  Sort Method: top-N heapsort  Memory: 26kB
               ->  Parallel Bitmap Heap Scan on articles (actual time=113.247..1784.517 rows=33333 loops=3)
                     Recheck Cond: (tsv @@ '''postgresql'' & ''article'''::tsquery)
                     Heap Blocks: exact=19968
                     ->  Bitmap Index Scan on idx_gin_tsv (actual time=127.531..127.532 rows=100000 loops=1)
                           Index Cond: (tsv @@ '''postgresql'' & ''article'''::tsquery)
 Planning Time: 1.290 ms
 Execution Time: 1853.445 ms
(16 rows)

The Sort node is unavoidable. GIN collected all matching TIDs, accessed the heap, sorted them, and retained the top 5.

Phrase search with distance — Recheck Required:

postgres=# EXPLAIN (COSTS OFF, ANALYZE)
SELECT id, title
FROM articles
WHERE tsv @@ to_tsquery('simple', 'postgresql <-> article')
LIMIT 5;

QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Limit (actual time=357.799..357.803 rows=0 loops=1)
   ->  Bitmap Heap Scan on articles (actual time=357.795..357.797 rows=0 loops=1)
         Recheck Cond: (tsv @@ '''postgresql'' <-> ''article'''::tsquery)
         Rows Removed by Index Recheck: 100000
         Heap Blocks: exact=55556
         ->  Bitmap Index Scan on idx_gin_tsv (actual time=48.745..48.746 rows=100000 loops=1)
               Index Cond: (tsv @@ '''postgresql'' <-> ''article'''::tsquery)
 Planning Time: 0.561 ms
 Execution Time: 357.873 ms
(9 rows)

With Rows Removed by Index Recheck, GIN found candidates containing both words but had to access the heap to verify adjacency. No positional information in the index.

What's Next

These three limitations — no ordering, no positions, bitmap-only scans — are exactly what RUM was designed to solve. In the next post of this series, we'll see how a single architectural change (adding addInfo to each posting list entry) eliminates all three.