GIN Index

GIN stands for Generalized Inverted Index. Unlike B-tree which indexes the values themselves, GIN indexes the components or elements within a value. This makes it perfect for complex data types like arrays, full-text search vectors, and JSONB.

Full-Text Search with GIN

One of the most common uses for GIN is full-text search:

CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  search_vector TSVECTOR
);

-- Populate the search vector
UPDATE articles
SET search_vector = to_tsvector('english', title || ' ' || body);

-- Create the GIN index
CREATE INDEX idx_articles_search ON articles USING gin (search_vector);

-- Now search is fast
SELECT title
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql & tutorial');

The @@ operator checks if the search vector matches the query. Without the GIN index, PostgreSQL would have to scan every row and convert it to a tsvector on the fly. With the index, it is instant.

JSONB Indexing

GIN indexes are also essential for querying JSONB data:

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  data JSONB
);

CREATE INDEX idx_events_data ON events USING gin (data);

-- Query for events where data contains a specific key-value pair
SELECT * FROM events
WHERE data @> '{"type": "click"}';

-- Query for events where data contains a specific key
SELECT * FROM events
WHERE data ? 'user_id';

The @> operator checks if one JSONB object contains another. The ? operator checks if a key exists. Both use the GIN index for fast lookups.

Array Indexing

GIN works with arrays too:

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200),
  tags TEXT[]
);

CREATE INDEX idx_posts_tags ON posts USING gin (tags);

-- Find posts with a specific tag
SELECT * FROM posts
WHERE tags @> ARRAY['postgresql'];

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

The @> operator checks if the array contains all specified elements. The && operator checks if the arrays have any elements in common.

GIN vs B-Tree

When to use which:

• B-Tree — Simple equality and range queries on scalar values (numbers, text, dates)
• GIN — Containment queries on complex types (arrays, JSONB, full-text search)

GIN indexes are slower for individual lookups than B-tree but are the only option for certain types of queries. Use them when B-tree cannot do what you need.

GIN Index Maintenance

GIN indexes can be slow to update because they need to update the inverted index structure. PostgreSQL supports fast update mode which batches changes:

CREATE INDEX idx_events_data ON events USING gin (data)
WITH (fastupdate = on);

Fast updates make INSERTs and UPDATEs faster but can slow down queries slightly. The trade-off depends on your workload.