GIN Indexes: The Inverted Index

What Problem Does GIN Solve?

Think of a library's card catalog — the old-fashioned kind where you look up a subject like "volcanoes" and find a list of every book that covers that subject. The catalog is indexed by topic, not by book. That is exactly how a GIN (Generalized Inverted Index) works. Instead of mapping a row to its value, it maps each element inside a container value back to the rows that contain it.

GIN is designed for data types that are "containers" — values that hold multiple items inside them. In PostgreSQL, the primary use cases are JSONB documents, Arrays, and Full-Text Search Vectors (tsvector).

How It Works

Consider a posts table with a tags column of type text[] (an array):

Row 1: tags = ['sql', 'database', 'performance']
Row 2: tags = ['database', 'replication']
Row 3: tags = ['sql', 'tuning']

A GIN index decomposes each container and builds an inverted mapping:

'database'    → {Row 1, Row 2}
'performance' → {Row 1}
'replication' → {Row 2}
'sql'         → {Row 1, Row 3}
'tuning'      → {Row 3}

Now, when you run WHERE tags @> ARRAY['sql', 'database'] (find rows containing both tags), the database:

1. Looks up 'sql' → gets {Row 1, Row 3}
2. Looks up 'database' → gets {Row 1, Row 2}
3. Intersects the two sets → {Row 1}

This is a pure index operation. A B-Tree would have to scan every row and parse every array. For a table with millions of rows, the difference is seconds versus milliseconds. A GIN index works the same way over a JSONB column: once built with USING GIN, containment queries with @> (find rows with all of a given set of elements) or overlap queries with && (find rows with any of them) hit the inverted index instead of scanning and parsing every row.

GIN Trade-offs

GIN indexes are fast to read but expensive to write. Every INSERT or UPDATE that changes a container value requires updating potentially many entries in the inverted index. PostgreSQL mitigates this with a pending list — new entries are buffered in a small list and merged into the main index in bulk during a VACUUM or when the list fills up. This is controlled by gin_pending_list_limit (default 4MB). For write-heavy workloads, you can increase this limit to reduce merge frequency at the cost of slightly slower reads until the merge happens.