In PostgreSQL, GIN index

In PostgreSQL, a GIN index stands for Generalized Inverted Index. It is a special type of index designed for searching inside composite values like arrays, JSONB, full-text documents, etc.

It is very useful when one column contains multiple values and you want to search inside those values efficiently.

1. Simple Idea

Normally, a B-tree index works like this:

ValueRowapplerow1bananarow2

But sometimes one column contains multiple elements, for example:

["apple", "banana", "orange"]

A GIN index breaks these values into pieces and indexes each piece.

So internally it becomes something like:

ElementRowsapplerow1, row5bananarow1, row3orangerow1

This makes searching very fast.

1. Example with JSONB

Suppose we have a table like:

CREATE TABLE permissions (
  id UUID,
  permissionToggles JSONB
);

Example data:

{
  "viewReports": true,
  "exportReports": false
}

If you want to query:

SELECT *

FROM permissions

WHERE permissionToggles @> '{"viewReports": true}';

Without an index → full table scan.

With GIN index:

ON permissions

USING GIN (permissionToggles);

Now PostgreSQL can search inside the JSON efficiently.

1. Example with Arrays

Table:

CREATE TABLE articles (
  id SERIAL,
  tags TEXT[]
);

Example:

{"tech", "javascript", "backend"}

Query:

SELECT *

FROM articles

WHERE tags @> ARRAY['javascript'];

Create GIN index:

ON articles

USING GIN (tags);

Now searching tags becomes very fast.

1. Example with Full-Text Search

GIN is heavily used for PostgreSQL full-text search.

ON documents

Query:

SELECT *

FROM documents

@@ to_tsquery('postgres');

1. When to Use GIN Index

Use GIN when working with:

• JSONB

• Arrays

• Full text search

• hstore

• composite data

Typical operators:

@> contains

<@ contained by

? key exists

@@ full text match

1. Trade-offs

Advantages:

✅ Very fast for searching inside JSON / arrays

✅ Excellent for full-text search

Disadvantages:

❌ Slower inserts/updates (index maintenance cost)

❌ Uses more disk space

✅ Simple summary

IndexBest ForB-Treeequality, range queriesGINJSONB, arrays, full-textGiSTgeometric / advancedHashequality only