When to Use PostgreSQL GIN Indexes

When a PostgreSQL query searches inside an array or JSONB document, or looks for terms in full-text search data, a GIN index can find rows that match the contents. GIN means Generalized Inverted Index. It indexes the individual keys or values in a column and links each one to the rows that contain it.

Use GIN when a query checks whether an array or JSONB document contains a value, or searches for full-text terms. Use B-tree when a query checks a single-value column for one exact value or for values within a range. Use it to sort results or make another ordered lookup. PostgreSQL’s index-type documentation describes B-tree as the default index type, and its GIN documentation lists the operators GIN supports. GIN stores multiple index entries for one array or document, which increases write and storage costs.

What GIN indexes

A GIN index breaks each indexed value into searchable keys and records which rows contain each key. An array contributes its elements. A full-text search vector contributes normalized words, called lexemes. This structure suits queries that look for rows containing particular keys.

PostgreSQL uses a GIN index only when the query expression and operator match the index’s operator class, which defines the operators that the index supports for a data type. A query on an array or JSONB column does not automatically use a GIN index just because the column has one.

Use GIN for array membership and overlap

For arrays, GIN supports overlap (&&) and containment (@>). It also supports contained-by (<@) and equality. Overlap finds rows whose arrays share at least one element. Containment finds arrays that include the specified elements, while contained-by checks the reverse. These operators suit searches like the ones in the PostgreSQL GIN operator list:

CREATE INDEX products_tags_gin
    ON products USING GIN (tags);

SELECT *
FROM products
WHERE tags @> ARRAY['sale']::text[];

SELECT *
FROM products
WHERE tags && ARRAY['sale', 'featured']::text[];

The first query finds rows whose tags array contains sale; the second finds rows containing either requested tag. PostgreSQL also documents searches written with ANY, but the GIN operator list names specific array operators. Check that a predicate uses a supported operator and confirm index use in the query plan rather than assuming that similar-looking syntax behaves the same way. The array-searching documentation also cautions that large collections of array elements can be easier to search and scale as separate rows in a related table.

Use GIN for JSONB containment and key checks

A GIN index on a JSONB column supports queries that test whether a document contains a structure or has a top-level key. The default jsonb_ops operator class also supports operators for searching with JSONPath, PostgreSQL’s syntax for querying JSON data. For example:

CREATE INDEX events_payload_gin
    ON events USING GIN (payload);

SELECT *
FROM events
WHERE payload @> '{"status": "active"}'::jsonb;

SELECT *
FROM events
WHERE payload ? 'status';

The first predicate checks for a matching key-value structure; the second checks whether the document has the top-level key status. PostgreSQL's JSONB indexing documentation describes these operators and index choices.

The index expression must match the query expression. A GIN index on payload does not automatically index every part extracted from the document. If a query often checks a nested tags value, PostgreSQL supports an expression index on that part:

CREATE INDEX events_tags_gin
    ON events USING GIN ((payload -> 'tags'));

SELECT *
FROM events
WHERE payload -> 'tags' ? 'urgent';

A whole-document index supports a wider range of JSONB queries. An expression index focuses on the selected part and is likely to use less space when queries concentrate there. Choose jsonb_path_ops when containment and supported JSONPath searches are enough:

CREATE INDEX events_payload_path_gin
    ON events USING GIN (payload jsonb_path_ops);

PostgreSQL says jsonb_path_ops indexes are usually smaller and searches typically perform better for the operators they support. This operator class does not support key-existence operators such as ?. It also creates no index entries for structures without values, such as {"a": {}}, so searching for those structures can require a full-index scan.

Use GIN for full-text search and trigram patterns

PostgreSQL recommends GIN for full-text search. A tsvector is a representation of text as searchable lexemes; a tsquery describes the terms to match. GIN indexes the lexemes so PostgreSQL can locate rows matching the query. The text-search configuration in the index expression and query must match for PostgreSQL to use the expression index as intended, as the text-search indexing documentation explains.

CREATE INDEX articles_body_fts_gin
    ON articles USING GIN (to_tsvector('english', body));

SELECT *
FROM articles
WHERE to_tsvector('english', body)
      @@ plainto_tsquery('english', 'postgres index');

Substring searches such as ILIKE '%postgres%' are a different case. The pg_trgm extension breaks text into groups of three consecutive characters, called trigrams, and its gin_trgm_ops operator class supports pattern searches that contain useful trigrams:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX articles_title_trgm_gin
    ON articles USING GIN (title gin_trgm_ops);

SELECT *
FROM articles
WHERE title ILIKE '%postgres%gin%';

Patterns without extractable trigrams can require scanning the index, reducing the benefit. Trigram indexing does not replace B-tree for ordinary equality or simple comparisons. PostgreSQL describes the extension’s behavior in the pg_trgm documentation.

Account for GIN write and storage costs

GIN can create multiple index entries for a single row because it indexes keys within each value. Inserts and updates therefore require more index maintenance than an index that stores one entry per row.

By default, PostgreSQL uses GIN’s fastupdate mechanism to place new entries in a pending list before merging them into the main index. Buffering can speed up updates, but searches must also check the pending entries. When PostgreSQL clears the list, one update can take longer. PostgreSQL’s autovacuum background maintenance can help control the list; see the GIN documentation and CREATE INDEX options.

Index size depends on the data and the operator class. A whole-document JSONB index stores entries across the document, while an expression index limits indexing to the selected expression. jsonb_path_ops is usually smaller than jsonb_ops on the same data, but supports fewer operators. Measure index size and write impact on representative data rather than relying on a universal size or performance estimate.

Choose GIN when the predicate fits

Choose GIN when queries repeatedly filter by array elements, JSONB values or keys, full-text terms, or supported text patterns, and the write and storage costs suit the workload. Choose B-tree when queries check a single-value column for an exact value or a range, sort results, or make simple comparisons.

Before adding GIN, check that the query condition uses an operator the index supports and that the query expression matches the indexed expression. Then compare query plans and performance using data like the data you expect to store. For large collections that queries search element by element, consider storing each element in its own row in a related table instead of in an array.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com