Indexing Full Text Search With GIN in PostgreSQL Tables

A full text search without an index reads every row, parses or reads every vector and compares. It works on 10,000 rows and fails on a million. With the right index, the same query touches only the rows that contain the term. PostgreSQL's index for that is GIN, and the decisions around it are about how it is built, how it stays current and when it is not used. This article covers the structure, the two index types, the update mechanism, build and size, and the reasons a query ignores it. It belongs to the cluster introduced by full text search in PostgreSQL before you add Elasticsearch.

What GIN is

GIN stands for Generalized Inverted Index. For a tsvector column, it builds a mapping from each lexeme to the set of rows containing it, like the index at the back of a book. A query for 'bicycle' & 'tire' looks up each lexeme, gets two lists of row pointers and intersects them. The lists are stored compressed in a posting tree, so a common term with a million rows is a compact structure, and the lookup cost depends on the list sizes, not on the table size.

CREATE INDEX article_search_idx ON article USING gin (search);

The query must use the column with the match operator:

SELECT id FROM article WHERE search @@ websearch_to_tsquery('english', 'bicycle tire');

In EXPLAIN, a good plan shows Bitmap Index Scan on article_search_idx feeding a Bitmap Heap Scan. GIN does not support index-ordered scans, so there is no plain Index Scan for text search, and the bitmap is expected.

GIN or GiST

PostgreSQL offers a second index type for tsvector, GiST, which stores a lossy signature of each document. The comparison:

The manual's rule of thumb: GIN is the choice for static or slowly changing data, and GiST for data that changes constantly with a modest number of distinct words. In practice nearly all applications use GIN, and moving to GiST is an optimization for a measured write bottleneck. The wider comparison is in GIN versus GiST and the tradeoffs.

Updates and the pending list

Updating a GIN index per row is costly: one document with 300 lexemes touches 300 posting lists. To soften this, PostgreSQL by default buffers new entries in a small unsorted pending list (fastupdate = on) and merges them into the main structure later in a batch. The merge happens when the list exceeds gin_pending_list_limit (4 MB by default), during VACUUM or autovacuum, or on a call to gin_clean_pending_list.

The effect: most inserts are fast, and one insert in a while pays for the merge and takes longer. Searches must scan the pending list in addition to the main index, so a large pending list slows reads slightly. If you see occasional multi-second inserts, the flush is the likely cause. Options: lower gin_pending_list_limit for smaller, more frequent flushes, run vacuum more often on the table, or set fastupdate = off on the index to pay on every insert and avoid spikes:

ALTER INDEX article_search_idx SET (fastupdate = off);

Bulk loads are best done by dropping nothing and loading in large transactions, or by loading first and building the index afterwards, which is far faster than inserting into an existing GIN index row by row.

Building the index

A GIN build reads every vector and sorts the lexeme-row pairs, so it is memory- and CPU-hungry. For a table of millions of articles, it can take minutes to an hour. Two settings matter:

SET maintenance_work_mem = '1GB';
SET max_parallel_maintenance_workers = 4;
CREATE INDEX CONCURRENTLY article_search_idx ON article USING gin (search);

A larger maintenance_work_mem lets the build keep more in memory and write fewer temporary files; the effect is described in maintenance_work_mem and faster index builds. Parallel builds for GIN arrived in PostgreSQL 18, so on earlier versions the worker setting has no effect for this index type. CONCURRENTLY builds without blocking writes, takes longer, cannot run in a transaction and, if it fails, leaves an invalid index that must be dropped and recreated, as creating indexes concurrently and handling failures explains.

Size

Check it after the build:

SELECT pg_size_pretty(pg_relation_size('article_search_idx')) AS index_size,
       pg_size_pretty(pg_relation_size('article'))            AS table_size;

A GIN index on a text search column is often of the same order as the text itself, depending on vocabulary and repetition. Positions are not stored in the index (only row pointers), which is why the heap must be rechecked for phrase queries, and also why the index does not grow with document length as fast as the vector does. A smaller index fits more easily in memory, and index cache hit ratio is what keeps searches fast; see pg_statio_user_indexes for the hit figures.

When the index is not used

Several situations produce a sequential scan, and most are correct.

The term is common. If a query term matches 40 percent of the table, reading the posting list, building a bitmap and fetching 40 percent of the heap costs more than a sequential scan. The planner is right. The remedy is a more selective query or a smaller candidate set, not forcing the index.

The query does not use the indexed expression. An index on search serves search @@ .... A query that computes to_tsvector('english', body) @@ ... cannot use it. If you index an expression instead, the query must repeat that exact expression, with the same configuration:

CREATE INDEX article_body_fts ON article USING gin (to_tsvector('english', body));
-- used only by:
SELECT id FROM article WHERE to_tsvector('english', body) @@ websearch_to_tsquery('english', 'tire');

A stored column is easier to keep correct.

Statistics are stale. After a large load, run ANALYZE article so the planner knows the lexeme frequencies it uses to judge selectivity.

The configuration differs. A query with simple against a vector built with english finds nothing and uses the index to find it quickly. Correct the configuration, not the plan.

Small tables. Below a few thousand rows a sequential scan is as fast. Do not worry about an index not being used on a test database with 50 rows.

Combining with other conditions

Real queries mix text search with filters. For WHERE search @@ q AND category_id = 7, the planner may use the GIN index for the text condition and filter the category afterwards, or use a B-tree index on the category and filter the text, or combine both with a BitmapAnd. Which wins depends on selectivity. If you routinely filter by a column with few distinct values (a tenant, a status), the btree_gin extension lets you put that column into the same GIN index:

CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE INDEX article_tenant_search ON article USING gin (tenant_id, search);

The combined index answers both conditions in one structure, at the cost of a larger index. Test it against the bitmap-and plan on your data before keeping it.

Maintenance

GIN indexes bloat like others under updates and deletes. Autovacuum cleans them and flushes the pending list. Monitor with pgstatginindex('article_search_idx') from the pgstattuple extension, which reports the pending list size and pages. Rebuild with REINDEX INDEX CONCURRENTLY if the index has grown far beyond its expected size, as covered in rebuilding indexes without locking writes. After a change in the text search configuration, rewrite the vectors, then the index follows with the updates.

When GiST or no index at all is the better choice

Choose GiST when the table takes continuous heavy writes, when documents are short and numerous and update latency matters more than read speed, or when you want one index to serve text search and another GiST-supported type, such as ranges. Use no index at all for tables below a few thousand rows or for an administrative search that runs once a day on a replica and can take a minute. And use a trigram index rather than a full text index when the requirement is substring or fuzzy matching, which GIN on a tsvector cannot provide; see typo tolerant search with pg_trgm.

What to do next

Check EXPLAIN (ANALYZE, BUFFERS) for your slowest search and confirm it shows a bitmap index scan, then look at the index size and the pending list. The manual describes the structure in GIN indexes and gives the guidance for text search in GIN and GiST index types. For the column the index sits on, see building a search column with a generated tsvector.