LLM Best Practices

GIN Index

2 min read

View markdown source

Definition

A GIN (Generalized Inverted Index) indexes the elements of composite values. For a tsvector column, GIN creates an entry for each lexeme pointing to the rows that contain it; for a jsonb column, it indexes each key-value pair; for an integer[] column, it indexes each array element. A single row can contribute hundreds of index entries.

GIN supports operators that B-tree does not:

  • Full-text: @@ (tsvector match)
  • JSONB containment: @>, <@, ?, ?|, ?&
  • Array operators: @>, <@, &&

GIN trade-offs vs. B-tree:

  • Build is slower; GIN must index many keys per row.
  • Updates are slower; GIN uses a pending-list that VACUUM or gin_clean_pending_list() merges.
  • Queries that hit GIN are faster than sequential full-text search by orders of magnitude on large tables.
  • fastupdate = on (default) batches inserts into a pending list; flush with vacuum for consistent query performance.

GiST is an alternative to GIN for full-text and geometric queries; GiST builds faster and updates faster but queries slower than GIN for large lexeme sets.

When it applies

Use GIN on tsvector columns for full-text search, on jsonb columns queried with containment operators, and on array columns queried with overlap or containment. Use CREATE INDEX CONCURRENTLY to avoid locking the table during build.

Example

ALTER TABLE articles ADD COLUMN search_vec tsvector
  GENERATED ALWAYS AS (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''))) STORED;
 
CREATE INDEX idx_articles_fts ON articles USING gin(search_vec);
 
SELECT id, title FROM articles
WHERE search_vec @@ to_tsquery('english', 'postgres & index');
 
CREATE INDEX idx_events_data ON events USING gin(data);
SELECT * FROM events WHERE data @> '{"type": "purchase"}';
  • btree-index - the sibling index type for equality and range; not useful for composite values.
  • query-plan - EXPLAIN shows Bitmap Index Scan or Index Scan on the GIN.
  • jsonb-path - JSONB path queries may use GIN if the access pattern matches.
  • postgres - the Postgres deep-dive.
  • postgres-explain - reading EXPLAIN output to confirm GIN usage.

Citing this term

See GIN Index (llmbestpractices.com/glossary/gin-index).

Graph View

Backlinks