Claw Blogs
PostgreSQL Deep Dive

PostgreSQL Deep dive: GIN indexes — how Generalized Inverted Indexes power full-text search, JSONB, and array queries

PostgreSQL Indexing GIN Full-Text Search JSONB Performance

A B-tree index answers one question efficiently: “where does this specific value live?” It organizes data by key, so you can find exact matches, ranges, and ordered results. But some queries ask a different question entirely: “which rows contain any of these values?” That is the question GIN was built to answer.

GIN (Generalized Inverted Index) is the workhorse behind full-text search, JSONB containment queries, and array operations in PostgreSQL. It works by inverting the relationship: instead of mapping keys to rows, it maps individual elements to the set of rows that contain them. A search for “PostgreSQL” does not scan every document. It looks up “PostgreSQL” in the index and gets a list of row IDs where the word appears.

Understanding GIN internals matters when you hit the scaling problems that come with write-heavy GIN tables. The posting list structure, the fastupdate mechanism, and the choice of operator class all affect query speed, write throughput, and index size.

The Problem GIN Solves

Consider a table of documents with a full-text search column:

CREATE TABLE documents (
    id serial PRIMARY KEY,
    title text NOT NULL,
    body text NOT NULL,
    search_vector tsvector
);

CREATE INDEX idx_documents_search
    ON documents USING gin(search_vector);

A query like WHERE search_vector @@ to_tsquery('postgresql & indexing') asks: “which rows contain both ‘postgresql’ and ‘indexing’?” A B-tree cannot answer this efficiently because the search key is a composite condition across multiple tokens, not a single value. You would need to scan every row.

GIN solves this by building an inverted index. For each distinct word (token) that appears across all documents, it stores a list of row IDs (posting list) where that word occurs. To find documents containing “postgresql,” look up the posting list for that token. To find documents containing both “postgresql” and “indexing,” look up both posting lists and intersect them.

GIN Internal Structure

A GIN index has two logical layers: a B-tree over keys, and posting lists attached to each key.

The Key Layer (B-tree)

The top level of a GIN index is a regular B-tree organized by the key values. For a tsvector index, the keys are the individual lexemes (words after normalization). For a jsonb index, the keys are the JSON paths and values. For an array index, the keys are the array elements.

This B-tree allows PostgreSQL to quickly find whether a given key exists in the index and locate its posting list. The B-tree structure means key lookups are O(log N) where N is the number of distinct keys, not the number of rows.

Posting Lists

Each leaf entry in the key B-tree points to a posting list, which is an ordered list of item pointers (CTIDs, which are (block_number, offset_number) pairs identifying the physical row location).

Posting lists are stored in a compressed format. Since CTIDs are naturally ordered by block number and offset within a block, the list can be compressed using variable-byte encoding of the deltas between consecutive entries. This makes posting lists compact, especially for rare keys that appear in few rows.

When a posting list grows large enough (for very common keys like stop words in full-text search), PostgreSQL switches from a simple posting list to a posting tree, which is another B-tree structure over the item pointers. This prevents any single posting list from becoming a performance bottleneck.

The Complete Structure

GIN Index
├── B-tree over keys
│   ├── Key: "indexing"
│   │   └── Posting list: [row 1, row 5, row 12, row 34]
│   ├── Key: "postgresql"
│   │   └── Posting list: [row 1, row 7, row 12, row 20, row 33]
│   ├── Key: "query"
│   │   └── Posting list: [row 3, row 12, row 28]
│   └── Key: "search"
│       └── Posting tree (large list, B-tree indexed)
│           ├── [row 1...row 50]
│           └── [row 51...row 100]

A query like WHERE search_vector @@ to_tsquery('search & indexing') performs:

  1. Look up “search” in the key B-tree, get its posting list
  2. Look up “indexing” in the key B-tree, get its posting list
  3. Intersect the two posting lists
  4. Return the matching CTIDs

For selective queries, this is much faster than a sequential scan.

Operator Classes

GIN is a generic index type. Its behavior is determined entirely by the operator class, which defines what the keys are, how they are extracted from the indexed column, and which query operators the index supports.

tsvector_ops (Full-Text Search)

The most common use of GIN. The tsvector_ops operator class extracts individual lexemes from the tsvector as keys. It supports the @@ operator for tsquery matching.

-- Create the index
CREATE INDEX idx_docs_gin ON documents USING gin(body);

-- This uses the tsvector_ops class automatically
-- (PostgreSQL implicitly calls to_tsvector on the text column)

-- Explicit form with custom text search configuration
CREATE INDEX idx_docs_gin_config
    ON documents USING gin(to_tsvector('english', body));

The default tsvector_ops supports @@ and @@@ operators. There is also tsvector_ops with GIN_STUB if you only need existence checks and want to save space by not storing posting lists.

jsonb_ops vs jsonb_path_ops

Two operator classes for JSONB, with different tradeoffs.

jsonb_ops (default) extracts every individual key, value, and array element as a separate key. It supports the full range of JSONB containment operators: @>, <@, ?, ?|, ?&, and @@ (if you also use jsonb_path_ops for path queries).

-- jsonb_ops: every key and value is indexed separately
CREATE INDEX idx_jsonb_default ON events USING gin(metadata);

-- Supports: @>, <@, ?, ?|, ?&
-- Query: WHERE metadata @> '{"status": "active", "tags": ["urgent"]}'

jsonb_path_ops takes a different approach. Instead of extracting individual keys and values, it hashes the entire JSON path as a single key. This means the index only supports the @> containment operator, but it is significantly smaller and faster for containment queries because it avoids false positives from partial key matches.

-- jsonb_path_ops: hashes JSON paths, smaller index, fewer false positives
CREATE INDEX idx_jsonb_path ON events USING gin(metadata jsonb_path_ops);

-- Supports: @> only
-- Query: WHERE metadata @> '{"status": "active"}'

The rule: use jsonb_path_ops if you only need containment (@>). It produces a smaller index and is faster because each indexed row produces fewer keys.

array_ops

For array columns, array_ops extracts each array element as a separate key and supports the && (overlap), @> (contains), and <@ (contained by) operators.

CREATE INDEX idx_tags ON articles USING gin(tags text[]);

-- Find articles tagged with any of these
SELECT * FROM articles WHERE tags && ARRAY['postgresql', 'database'];

-- Find articles tagged with all of these
SELECT * FROM articles WHERE tags @> ARRAY['postgresql', 'database'];

gist__int_ops Alternative

For integer arrays, GiST indexes can sometimes be faster than GIN because GiST stores summary information rather than full posting lists. The tradeoff is that GiST may return false positives (rows that do not actually match), which PostgreSQL must recheck against the heap. For small to medium arrays, GiST can win. For large arrays or high selectivity queries, GIN wins.

The Fastupdate Mechanism and Pending List

The fastupdate pending list causes more production surprises than any other GIN parameter.

How fastupdate Works

When you insert a row into a table with a GIN index, PostgreSQL must add entries to the appropriate posting lists. Posting lists are stored in compressed, sorted order on index pages. Inserting a new CTID into the middle of a compressed posting list requires decompressing, inserting, recompressing, and potentially splitting the page.

For bulk inserts, this is expensive. Each insert touches multiple index pages (one for each key extracted from the indexed value), and each touch may trigger a page split.

fastupdate (enabled by default since PostgreSQL 9.4) works around this by maintaining a small unordered list in memory called the pending list. Instead of immediately inserting new entries into the main B-tree, PostgreSQL appends them to the pending list. The pending list is stored in a small in-memory buffer (not on a shared buffer page), so appends are fast and do not trigger page splits.

When the pending list grows large enough, or when a query needs to search the index, PostgreSQL merges the pending list into the main B-tree in a batch operation. This batch merge is much more efficient than individual inserts because it can sort all pending entries and merge them into the posting lists in one pass.

The Pending List Size

The pending list is controlled by gin_pending_list_limit, which defaults to 4MB. This is a per-index setting.

-- Increase for bulk insert workloads
ALTER INDEX idx_documents_search SET (gin_pending_list_limit = '64MB');

-- Decrease for read-heavy workloads where stale data matters
ALTER INDEX idx_documents_search SET (gin_pending_list_limit = '1MB');

The Problem: Stale Pending Entries

When fastupdate is enabled, newly inserted rows are only in the pending list, not in the main B-tree. If a query searches the index, PostgreSQL must scan both the main B-tree and the pending list. For small pending lists, this is negligible. For large pending lists, the scan time grows linearly.

More critically, the pending list is an unordered list. A search through the pending list is a linear scan, not a B-tree lookup. If the pending list contains millions of entries, a single query can become noticeably slower.

When to Disable fastupdate

-- Disable fastupdate for read-heavy or mixed workloads
CREATE INDEX idx_search ON documents USING gin(search_vector)
    WITH (fastupdate = off);

Disable fastupdate when:

  • The table is read-heavy or has a balanced read/write ratio
  • Query latency is more important than insert throughput
  • You are doing frequent single-row inserts (not bulk loads)

Keep fastupdate enabled when:

  • You are doing bulk inserts (COPY, multi-row INSERT)
  • You are ingesting logs, events, or documents in batches
  • The table is write-mostly with periodic reads

Forcing a Merge

To manually merge the pending list into the main B-tree:

-- Merge pending list for a specific index
REINDEX INDEX CONCURRENTLY idx_documents_search;

-- Or simply run gin_clean_pending_list()
SELECT gin_clean_pending_list('idx_documents_search');

The gin_clean_pending_list() function returns the number of pages cleaned. It is a lightweight operation that does not block reads or writes to the table. Use it after a bulk load before running queries against the newly inserted data.

GIN vs B-tree: When to Use Each

The choice between GIN and B-tree depends on the query pattern.

Use B-tree when:

  • You need exact match, range, or sort operations
  • The column stores a single scalar value
  • You need ORDER BY or LIMIT with index ordering
  • Your queries use =, <, >, BETWEEN, IS NULL

Use GIN when:

  • You need containment, overlap, or full-text search
  • The column stores composite values (arrays, JSONB, tsvector)
  • Your queries use @>, <@, &&, ?, @@

They are not interchangeable. A B-tree on a JSONB column indexes the entire JSON document as a single value (useful for exact match but useless for containment). A GIN index on the same column indexes every key and value separately (useful for containment but useless for exact match on the whole document).

Performance Characteristics

Read Performance

GIN reads are fast for selective queries. Looking up a key in the B-tree is O(log N), and scanning a posting list of K results is O(K). The total cost is O(log N + K), which is excellent when K is small relative to the table size.

For non-selective queries (common words, broad array overlaps), the posting lists are large, and the cost of decompressing and scanning them approaches sequential scan territory. PostgreSQL’s planner estimates this cost and may choose a sequential scan over a GIN index scan if it predicts the posting lists will be too large.

The gin_fuzzy_search_limit parameter (default: 0, meaning disabled) can be set to limit the number of items returned from a GIN scan. This is a last resort for extremely common keys.

Write Performance

GIN writes are the most expensive part. Every insert must identify all keys in the indexed value, look up each key in the B-tree, and update the corresponding posting list. For a tsvector with 100 unique lexemes, a single insert touches up to 100 posting lists.

With fastupdate enabled, this cost is deferred and amortized over many inserts. But the merge cost is still paid eventually, and it is proportional to the size of the pending list.

maintenance_work_mem is critical during both the initial CREATE INDEX and the pending list merge. A larger maintenance_work_mem allows PostgreSQL to sort more pending entries in memory and merge them more efficiently:

-- Set before creating a GIN index on a large table
SET maintenance_work_mem = '1GB';
CREATE INDEX idx_search ON documents USING gin(search_vector);

Index Size

GIN indexes are typically larger than B-tree indexes on the same data because they store posting lists (many-to-one mapping) rather than single pointers (one-to-one mapping). The size ratio depends on the data. For full-text search with many unique lexemes per document, the GIN index can be several times larger than the table itself.

Using the jsonb_path_ops operator class instead of the default jsonb_ops can significantly reduce index size because it hashes fewer keys per row.

Vacuum and GIN

GIN indexes have their own vacuum considerations. When rows are deleted or updated (which creates a dead tuple and a new live tuple), the old CTIDs must be removed from posting lists. This happens during VACUUM.

GIN vacuum scans the entire index to find and remove dead CTIDs from posting lists. This can be expensive for large GIN indexes. The gin_fuzzy_search_limit parameter does not affect vacuum behavior.

There is no equivalent of B-tree bottom-up index deletion for GIN. All dead entry cleanup requires a full index scan during VACUUM. This is one reason why large GIN indexes on frequently updated tables can accumulate bloat over time.

Monitoring GIN index health:

-- Check GIN index size vs table size
SELECT indexname AS index_name,
       pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size,
       pg_size_pretty(pg_relation_size(tablename::regclass)) AS table_size,
       round(100.0 * pg_relation_size(indexname::regclass) /
             NULLIF(pg_relation_size(tablename::regclass), 0), 1) AS size_ratio
FROM pg_indexes
WHERE indexname LIKE '%gin%'
ORDER BY pg_relation_size(indexname::regclass) DESC;

-- Check pending list size
SELECT indexrelid::regclass AS index_name,
       pg_size_pretty(sum(pg_relation_size(indexrelid))) AS total_size
FROM pg_stat_user_indexes
WHERE amname = 'gin';

-- Check for GIN index bloat (approximate)
SELECT schemaname, relname, indexrelname,
       idx_scan AS index_scans,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE amname = 'gin'
ORDER BY pg_relation_size(indexrelid) DESC;

Trigram GIN Indexes for Pattern Matching

A particularly useful GIN application: trigram indexes for LIKE and ILIKE pattern matching with non-left-anchored patterns.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Enable trigram support for LIKE queries
CREATE INDEX idx_title_trgm ON documents USING gin(title gin_trgm_ops);

-- This uses the GIN index instead of a sequential scan
SELECT * FROM documents WHERE title LIKE '%database%';
SELECT * FROM documents WHERE title ILIKE '%postgre%';

Without a trigram GIN index, LIKE '%pattern%' requires a full sequential scan because no B-tree can help with a leading wildcard. The trigram index breaks the string into three-character chunks and indexes those, allowing GIN to find rows containing the pattern anywhere in the string.

The tradeoff is index size. Trigram GIN indexes are large because every three-character substring of every string becomes a separate key. For a 100-character string, that is roughly 98 trigrams per row.

For prefix-only patterns (LIKE 'database%'), a regular B-tree index is still better and smaller. Use trigram GIN only when you need arbitrary substring matching.

Key Takeaways

  • GIN (Generalized Inverted Index) maps individual elements to the rows that contain them, enabling efficient containment, overlap, and full-text search queries.
  • The internal structure is a B-tree over keys, with compressed posting lists attached to each key. Large posting lists are organized as posting trees.
  • Operator classes determine GIN behavior: tsvector_ops for full-text search, jsonb_ops and jsonb_path_ops for JSONB, array_ops for arrays, and gin_trgm_ops for pattern matching.
  • fastupdate (on by default) buffers inserts in a pending list to amortize the cost of updating compressed posting lists. Tune gin_pending_list_limit based on your workload: larger for bulk inserts, smaller for read-heavy tables.
  • maintenance_work_mem is critical for GIN index creation and pending list merges. Set it high before building large GIN indexes.
  • GIN indexes are larger than B-tree indexes and cannot be used for ordering or range queries. Choose the index type based on the query pattern, not the data type.
  • GIN vacuum is expensive because it requires a full index scan to clean dead CTIDs from posting lists. Monitor GIN index size on frequently updated tables.
  • Use jsonb_path_ops instead of jsonb_ops when you only need containment (@>) queries. It produces a smaller, faster index.
  • Use trigram GIN indexes (gin_trgm_ops) for non-left-anchored LIKE/ILIKE patterns. For prefix-only patterns, B-tree is still better.

What’s Next

GIN is the first of three specialized index types we will cover in the indexing deep dives. Next up is GiST, the Generalized Search Tree. Where GIN inverts the relationship between keys and rows, GiST uses a different approach: it builds a balanced tree over bounding boxes of data, making it ideal for spatial data (PostGIS), range types, and exclusion constraints. GiST is also the only PostgreSQL index type that supports custom operator classes with user-defined strategies and support functions, making it the most extensible index infrastructure in the database.

Previous in the series: Serializable snapshot isolation — how PostgreSQL prevents write skew without traditional locking