Indexing JSONB with GIN in PostgreSQL

Introduction

Indexing JSONB with GIN helps the database find data inside JSON documents faster. It makes searching inside JSON data quick and efficient.

You store JSON data in a PostgreSQL table and want to search inside it often.

You want to speed up queries that look for specific keys or values inside JSONB columns.

You have a large table with JSONB data and want to avoid slow full table scans.

You want to filter rows based on JSON content without reading every row.

Syntax

PostgreSQL

CREATE INDEX index_name ON table_name USING GIN (jsonb_column);

GIN stands for Generalized Inverted Index, which is good for searching inside complex data like JSON.

You create the index on the JSONB column you want to search quickly.

Examples

This creates a GIN index named idx_data_gin on the data column of my_table.

PostgreSQL

CREATE INDEX idx_data_gin ON my_table USING GIN (data);

This creates a GIN index on the info JSONB column in the users table.

PostgreSQL

CREATE INDEX idx_info_gin ON users USING GIN (info);

Sample Program

This example creates a table with a JSONB column, inserts some data, creates a GIN index on the JSONB column, and then queries for products where the color is red.

PostgreSQL

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  details JSONB
);

INSERT INTO products (details) VALUES
  ('{"color": "red", "size": "M"}'),
  ('{"color": "blue", "size": "L"}'),
  ('{"color": "red", "size": "S"}');

CREATE INDEX idx_details_gin ON products USING GIN (details);

-- Query to find products where color is red
SELECT * FROM products WHERE details @> '{"color": "red"}';

Important Notes

Creating a GIN index on JSONB columns greatly improves query speed for containment queries using the @> operator.

Indexes take some time and space to build, but they save time on repeated searches.

You can drop the index anytime with DROP INDEX index_name; if you no longer need it.

Summary

Use GIN indexes to speed up searches inside JSONB columns.

Create the index with CREATE INDEX ... USING GIN on the JSONB column.

Queries using @> operator benefit most from this index.