Indexing JSONB with GIN in PostgreSQL - Mini Project: Build & Apply

Indexing JSONB with GIN in PostgreSQL

📖 Scenario: You are managing a PostgreSQL database for a small online bookstore. The book details are stored in a table with a JSONB column that holds various attributes like title, author, and genres. To make searching faster, you want to create an index on this JSONB column.

🎯 Goal: Build a PostgreSQL table with a JSONB column, create a GIN index on it, and write a query that uses this index to quickly find books by a specific author.

📋 What You'll Learn

Create a table named books with an id column and a details column of type JSONB

Insert three book records with exact JSONB details provided

Create a GIN index on the details column

Write a SELECT query to find books where the author is exactly 'J.K. Rowling'

💡 Why This Matters

🌍 Real World

Many modern applications store flexible data in JSONB columns in PostgreSQL. Indexing JSONB data with GIN indexes helps keep queries fast even as data grows.

💼 Career

Database developers and backend engineers often need to optimize JSONB queries in PostgreSQL for performance, making this skill valuable for real-world projects.

1

Create the books table with JSONB column and insert data

Create a table called books with columns id (integer primary key) and details (JSONB). Then insert these three rows exactly: (1, '{"title": "Harry Potter and the Sorcerer\'s Stone", "author": "J.K. Rowling", "genres": ["Fantasy", "Adventure"]}'), (2, '{"title": "The Hobbit", "author": "J.R.R. Tolkien", "genres": ["Fantasy"]}'), and (3, '{"title": "1984", "author": "George Orwell", "genres": ["Dystopian", "Science Fiction"]}').

PostgreSQL

-- Create the books table with id and details columns
-- Insert the three specified rows

Need a hint?

Use CREATE TABLE with id INTEGER PRIMARY KEY and details JSONB. Insert the JSON strings carefully, doubling single quotes inside strings.

2

Create a GIN index on the details JSONB column

Create a GIN index named idx_books_details on the details column of the books table to speed up JSONB queries.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    details JSONB
);

INSERT INTO books (id, details) VALUES
(1, '{"title": "Harry Potter and the Sorcerer''s Stone", "author": "J.K. Rowling", "genres": ["Fantasy", "Adventure"]}'),
(2, '{"title": "The Hobbit", "author": "J.R.R. Tolkien", "genres": ["Fantasy"]}'),
(3, '{"title": "1984", "author": "George Orwell", "genres": ["Dystopian", "Science Fiction"]}');

-- Create the GIN index on details column

Need a hint?

Use CREATE INDEX idx_books_details ON books USING GIN (details); to create the index.

3

Write a query to find books by author 'J.K. Rowling'

Write a SELECT query to get all columns from books where the details JSONB column has the key author with the exact value 'J.K. Rowling'. Use the JSONB containment operator @>.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    details JSONB
);

INSERT INTO books (id, details) VALUES
(1, '{"title": "Harry Potter and the Sorcerer''s Stone", "author": "J.K. Rowling", "genres": ["Fantasy", "Adventure"]}'),
(2, '{"title": "The Hobbit", "author": "J.R.R. Tolkien", "genres": ["Fantasy"]}'),
(3, '{"title": "1984", "author": "George Orwell", "genres": ["Dystopian", "Science Fiction"]}');

CREATE INDEX idx_books_details ON books USING GIN (details);

-- Write the SELECT query to find books by author 'J.K. Rowling'

Need a hint?

Use SELECT * FROM books WHERE details @> '{"author": "J.K. Rowling"}'; to query by author.

4

Verify the index usage with EXPLAIN

Write an EXPLAIN statement before the SELECT query from step 3 to check that the GIN index idx_books_details is used.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    details JSONB
);

INSERT INTO books (id, details) VALUES
(1, '{"title": "Harry Potter and the Sorcerer''s Stone", "author": "J.K. Rowling", "genres": ["Fantasy", "Adventure"]}'),
(2, '{"title": "The Hobbit", "author": "J.R.R. Tolkien", "genres": ["Fantasy"]}'),
(3, '{"title": "1984", "author": "George Orwell", "genres": ["Dystopian", "Science Fiction"]}');

CREATE INDEX idx_books_details ON books USING GIN (details);

-- Add EXPLAIN before the SELECT query

Need a hint?

Prefix the SELECT query with EXPLAIN to see the query plan and confirm index usage.