GIN index for arrays and JSONB in PostgreSQL

Introduction

A GIN index helps find items inside arrays or JSONB data quickly, making searches faster.

When you want to search for specific values inside an array column.

When you need to query parts of JSONB data efficiently.

When your table has many rows with array or JSONB columns and you want faster lookups.

When you often use operators like @> or ? on arrays or JSONB columns.

When you want to improve performance of complex queries on nested data.

Syntax

PostgreSQL

CREATE INDEX index_name ON table_name USING GIN (column_name);

GIN stands for Generalized Inverted Index, which is good for indexing composite data like arrays and JSONB.

You must have the "pg_trgm" or "btree_gin" extension installed for some advanced GIN index features.

Examples

Creates a GIN index on the 'tags' array column in the 'products' table.

PostgreSQL

CREATE INDEX idx_tags ON products USING GIN (tags);

Creates a GIN index on the 'data' JSONB column in the 'events' table.

PostgreSQL

CREATE INDEX idx_data ON events USING GIN (data);

Creates a GIN index on the 'content' JSONB column using jsonb_path_ops for faster path queries.

PostgreSQL

CREATE INDEX idx_jsonb_path ON documents USING GIN (content jsonb_path_ops);

Sample Program

This example creates a table with an array column, inserts some rows, creates a GIN index on the array column, and queries for rows containing a specific tag.

PostgreSQL

CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  title TEXT,
  tags TEXT[]
);

INSERT INTO books (title, tags) VALUES
('Learn SQL', ARRAY['database', 'sql', 'learning']),
('JSON Guide', ARRAY['json', 'data', 'guide']),
('PostgreSQL Tips', ARRAY['postgresql', 'database', 'tips']);

CREATE INDEX idx_books_tags ON books USING GIN (tags);

-- Query to find books tagged with 'database'
SELECT id, title, tags FROM books WHERE tags @> ARRAY['database'];

Important Notes

GIN indexes speed up searches inside arrays and JSONB but can be slower to update when data changes.

They use more disk space than regular indexes because they store entries for each element inside the array or JSONB.

Use GIN indexes when you query with operators like @>, ?, or ?| on arrays or JSONB columns.

Summary

GIN indexes help search inside arrays and JSONB data quickly.

They are useful when you query for specific elements inside these complex types.

Creating a GIN index is simple and improves query speed but may slow down inserts and updates.