PostgreSQL GIN Indexes: JSONB, Arrays & Full-Text Search

PostgreSQL GIN Indexes: JSONB, Arrays & Full-Text Search

A PostgreSQL GIN (Generalized Inverted Index) index maps individual values inside composite data types -- JSONB keys, array elements, text search lexemes, and trigrams -- to the rows that contain them, enabling fast lookups on data that B-tree indexes cannot handle.

The Problem

B-tree indexes work well for scalar comparisons: equality, range, and ordering on a single value per row. But PostgreSQL supports rich data types where a single column contains multiple searchable values. A JSONB column might hold a document with dozens of keys. An array column might contain a list of tags. A tsvector column holds the lexemes of an entire text document. A text column searched with LIKE '%pattern%' needs trigram matching.

None of these can be served by a B-tree index. Without a GIN index, every query that searches inside JSONB, checks array containment, or performs full-text search falls back to a sequential scan -- reading every row in the table and evaluating the condition row by row. On a table with a million JSONB documents, a containment query like WHERE metadata @> '{"status": "active"}' without a GIN index scans every document, extracting and comparing the JSON value for each row.

The performance impact is often hidden during development. With 1,000 rows in a dev database, the sequential scan completes in milliseconds. In production with millions of rows, the same query takes seconds. Teams add GIN indexes reactively after users report slowness, when they could have been created proactively based on the query patterns.

GIN indexes also have characteristics that differ from B-tree indexes in ways that catch people off guard. They are slower to build and larger on disk. They use a "pending list" (fastupdate) that batches insertions for efficiency but can cause occasional slow queries when the pending list is flushed. And the choice of operator class determines exactly which operators the index supports -- creating a GIN index with the wrong operator class may not help the query you are trying to optimize.

How to Detect It

Identify queries that would benefit from GIN indexes by looking for sequential scans on tables with JSONB, array, or text search columns:

-- Find tables with JSONB or array columns that lack GIN indexes
SELECT
    t.schemaname,
    t.relname AS table_name,
    a.attname AS column_name,
    pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type,
    t.seq_scan,
    t.seq_tup_read,
    pg_size_pretty(pg_relation_size(t.relid)) AS table_size
FROM pg_stat_user_tables t
JOIN pg_attribute a ON a.attrelid = t.relid
WHERE a.atttypid IN ('jsonb'::regtype, 'json'::regtype)
   OR pg_catalog.format_type(a.atttypid, a.atttypmod) LIKE '%[]'
   OR a.atttypid = 'tsvector'::regtype
ORDER BY t.seq_tup_read DESC;

-- Check if existing GIN indexes are actually being used
SELECT
    indexrelname AS index_name,
    relname AS table_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE indexrelname LIKE '%gin%' OR indexrelname LIKE '%jsonb%'
ORDER BY idx_scan;

Confirm whether a specific query needs a GIN index using EXPLAIN:

-- Check if a JSONB containment query is doing a sequential scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM products
WHERE attributes @> '{"color": "blue", "size": "large"}';

-- If you see "Seq Scan" with a "Filter" node, the query needs a GIN index
-- If you see "Bitmap Index Scan" on a GIN index, it's already optimized

Look for the Filter line in the EXPLAIN output with a Rows Removed by Filter count. If the query filters out 99% of rows, a GIN index would eliminate that wasted work.

How myDBA.dev Shows It

myDBA.dev's Index Advisor analyzes your query patterns and existing indexes to identify where GIN indexes are needed. The advisor detects queries performing sequential scans on JSONB containment checks, array overlap operations, and full-text search queries, then recommends the specific GIN index that would eliminate the scan. Each recommendation includes the operator class to use and the expected performance improvement.

Index Advisor showing GIN index recommendations for JSONB and array columns with usage statistics

The Queries page complements this by showing the actual execution times of queries that would benefit from GIN indexes. You can sort by total execution time to find the most expensive JSONB or array queries, then click through to see their EXPLAIN plans. A query spending 2 seconds on a Seq Scan with Filter: (attributes @> ...) is a clear candidate for a GIN index -- and the execution time trend shows whether it is getting worse as the table grows.

Queries page showing JSONB and array queries with execution time trends

How to Fix It

GIN indexes for JSONB

There are two operator classes for JSONB GIN indexes, each supporting different operations:

-- Default operator class: supports @>, ?, ?|, ?& operators
-- Use this for containment queries (the most common pattern)
CREATE INDEX CONCURRENTLY idx_products_attributes_gin
    ON products USING gin (attributes);

-- Example queries this index serves:
-- SELECT * FROM products WHERE attributes @> '{"color": "blue"}';
-- SELECT * FROM products WHERE attributes ? 'warranty';
-- SELECT * FROM products WHERE attributes ?| array['color', 'size'];

-- jsonb_path_ops: supports only @> but is smaller and faster
-- Use this when you only need containment checks
CREATE INDEX CONCURRENTLY idx_products_attributes_path
    ON products USING gin (attributes jsonb_path_ops);

-- This index is typically 2-3x smaller than the default operator class
-- and faster for containment queries, but does NOT support ?, ?|, ?& operators

The choice between jsonb_ops (default) and jsonb_path_ops matters. If your queries only use @> (containment), jsonb_path_ops is the better choice -- it produces a smaller index and faster lookups. If you also need key existence checks (?, ?|, ?&), you need the default operator class.

GIN indexes for arrays

-- Index an array column for containment and overlap queries
CREATE INDEX CONCURRENTLY idx_articles_tags_gin
    ON articles USING gin (tags);

-- Queries this index serves:
-- Contains all specified elements (@>)
SELECT * FROM articles WHERE tags @> ARRAY['postgresql', 'performance'];

-- Contains any of the specified elements (&&)
SELECT * FROM articles WHERE tags && ARRAY['postgresql', 'mysql'];

-- Is contained by (<@)
SELECT * FROM articles WHERE tags <@ ARRAY['postgresql', 'performance', 'indexing'];

GIN indexes for full-text search

-- Index a tsvector column for full-text search
-- First, create a generated tsvector column (PG12+)
ALTER TABLE articles ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(body, '')), 'B')
    ) STORED;

-- Create the GIN index on the tsvector column
CREATE INDEX CONCURRENTLY idx_articles_search_gin
    ON articles USING gin (search_vector);

-- Full-text search query using the index
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'postgresql & performance') query
WHERE search_vector @@ query
ORDER BY rank DESC;

GIN indexes for trigram similarity (pg_trgm)

-- Enable the pg_trgm extension for LIKE/ILIKE pattern matching
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Create a GIN trigram index
CREATE INDEX CONCURRENTLY idx_customers_name_trgm
    ON customers USING gin (customer_name gin_trgm_ops);

-- Now LIKE with leading wildcards uses the index
SELECT * FROM customers WHERE customer_name LIKE '%smith%';
SELECT * FROM customers WHERE customer_name ILIKE '%john%';

-- Similarity searches also use this index
SELECT * FROM customers
WHERE customer_name % 'Jon Smith'
ORDER BY similarity(customer_name, 'Jon Smith') DESC;

Without the trigram GIN index, LIKE '%pattern%' always does a sequential scan because B-tree indexes require a fixed prefix.

Tuning fastupdate

GIN indexes use a pending list to batch insertions, controlled by the fastupdate storage parameter:

-- Disable fastupdate if you need consistent query latency
-- (at the cost of slower inserts)
ALTER INDEX idx_products_attributes_gin SET (fastupdate = off);

-- Or tune the pending list size
ALTER INDEX idx_products_attributes_gin SET (gin_pending_list_limit = 256);

With fastupdate = on (default), insertions go into a pending list that is periodically merged into the main index. This makes inserts fast but can cause occasional slow queries when the pending list is large and needs to be scanned. If you need predictable query latency, disable fastupdate -- inserts will be slower, but queries will be consistently fast.

How to Prevent It

Create GIN indexes proactively based on column types

Every JSONB column that is queried with @>, ?, or path expressions needs a GIN index. Every array column queried with @> or && needs a GIN index. Every tsvector column needs a GIN index. Every text column searched with LIKE '%pattern%' needs a trigram GIN index. These are not optional optimizations -- without them, the queries always do sequential scans.

Build this into your schema design process: when you add a JSONB or array column, add the corresponding GIN index in the same migration. Do not wait for production performance complaints.

Monitor GIN index size and performance

GIN indexes are larger than B-tree indexes and grow as the number of distinct indexed values increases. A JSONB column with highly varied key structures produces a large GIN index. Monitor index sizes over time and consider jsonb_path_ops if you only need containment queries -- it can be 2-3x smaller than the default operator class.

myDBA.dev tracks index sizes and usage over time, making it easy to spot GIN indexes that are growing disproportionately or not being used. An unused GIN index is expensive -- it slows down every INSERT and UPDATE on the table while providing no query benefit.

Choose the right operator class

Using the wrong operator class means the index does not serve your queries. The most common mistake is creating a jsonb_path_ops index and then querying with ? (key existence) -- the index is ignored and the query does a sequential scan. Check your query patterns before choosing the operator class, and verify with EXPLAIN after creating the index.

FAQ

What is a GIN index in PostgreSQL?

A GIN (Generalized Inverted Index) is a type of index that maps individual values within composite data types to the rows containing them. Unlike B-tree indexes that store one value per row, GIN indexes decompose a column value into its constituent parts (JSON keys, array elements, text lexemes) and create index entries for each part. This enables fast lookups for containment, overlap, and full-text search operations that B-tree indexes cannot support.

What is the difference between a GIN index and a normal (B-tree) index?

A B-tree index stores a single scalar value per row and supports equality, range, and ordering operations. A GIN index stores multiple values per row (the individual elements of JSONB, arrays, or text vectors) and supports containment (@>), overlap (&&), and existence (?) operations. GIN indexes are larger and slower to build than B-tree indexes, but they are the only way to index lookups inside composite data types. B-tree indexes cannot help with queries like WHERE tags @> ARRAY['postgresql'] or WHERE metadata @> '{"key": "value"}'.

Are GIN indexes supported in PostgreSQL 17?

Yes. GIN indexes have been a core feature since PostgreSQL 8.2 and continue to receive improvements in every major release. PostgreSQL 17 includes GIN index support for JSONB (jsonb_ops, jsonb_path_ops), arrays (array_ops), full-text search (tsvector_ops), and trigrams (gin_trgm_ops). Recent versions have improved GIN vacuum performance, parallel index builds, and the efficiency of the pending list mechanism.

Can GIN indexes be used with PostGIS?

GIN indexes are not typically used for PostGIS spatial data. PostGIS uses GiST (Generalized Search Tree) indexes for spatial queries like ST_Contains, ST_Intersects, and ST_DWithin. However, if you store spatial attributes as JSONB (such as properties attached to geographic features), a GIN index on that JSONB column would be useful for querying those attributes. For the spatial geometry itself, always use a GiST index (CREATE INDEX ... USING gist (geom)).