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.
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.
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.
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.
Example with Full-Text Search
GIN is heavily used for PostgreSQL full-text search.
ON documents
Query:
SELECT *
FROM documents
@@ to_tsquery('postgres');
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
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
Comments (0)
Login to leave a comment.