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

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

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'

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

Hint

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

Practice

1. What is the main purpose of creating a GIN index on a JSONB column in PostgreSQL?

easy

A. To convert JSONB data into plain text for easier reading

B. To compress the JSONB data to save disk space

C. To automatically backup JSONB data to another table

D. To speed up queries that search for specific keys or values inside the JSONB data

Solution

1. Step 1: Understand GIN index purpose

   GIN indexes are designed to speed up searches inside complex data types like JSONB by indexing keys and values.

2. Step 2: Identify the benefit for JSONB queries

   Queries using operators like @> that check for existence of keys or values run faster with GIN indexes.

3. Final Answer:

   To speed up queries that search for specific keys or values inside the JSONB data -> Option D

4. Quick Check:

   GIN index = faster JSONB key/value search [OK]

Hint: GIN indexes speed up JSONB key/value searches [OK]

Common Mistakes:

• Thinking GIN compresses JSONB data
• Confusing GIN with backup or conversion tools
• Assuming GIN indexes speed up all query types

2. Which of the following is the correct syntax to create a GIN index on a JSONB column named data in a table items?

easy

A. CREATE INDEX idx_data ON items USING GIN (data);

B. CREATE INDEX idx_data ON items USING BTREE (data);

C. CREATE INDEX idx_data ON items USING GIN (data (jsonb_path_ops));

D. CREATE INDEX idx_data ON items USING HASH (data);

Solution

1. Step 1: Recall correct GIN index syntax

   The standard way to create a GIN index on a JSONB column is: CREATE INDEX index_name ON table_name USING GIN (column_name);

2. Step 2: Check options for correctness

   CREATE INDEX idx_data ON items USING GIN (data); matches the correct syntax. CREATE INDEX idx_data ON items USING BTREE (data); uses BTREE which is not suitable for JSONB. CREATE INDEX idx_data ON items USING GIN (data (jsonb_path_ops)); has incorrect syntax (extra parentheses around jsonb_path_ops). CREATE INDEX idx_data ON items USING HASH (data); uses HASH which is not valid for JSONB indexing.

3. Final Answer:

   CREATE INDEX idx_data ON items USING GIN (data); -> Option A

4. Quick Check:

   Correct GIN index syntax = CREATE INDEX idx_data ON items USING GIN (data); [OK]

Hint: Use USING GIN with column name in parentheses [OK]

Common Mistakes:

• Using BTREE or HASH instead of GIN
• Incorrect syntax with extra keywords
• Missing parentheses around column name

3. Given a table products with a JSONB column attributes and a GIN index on it, what will this query return?

SELECT * FROM products WHERE attributes @> '{"color": "red"}';

medium

A. All rows where the JSONB column contains the key "color" with value "red"

B. All rows where the JSONB column contains any key named "color"

C. All rows where the JSONB column contains the value "red" anywhere

D. Syntax error due to incorrect JSON format

Solution

1. Step 1: Understand the @> operator

   The @> operator checks if the left JSONB contains the right JSONB as a subset, meaning the key-value pair must exist exactly.

2. Step 2: Analyze the query condition

   The condition attributes @> '{"color": "red"}' means the JSONB column must have a key "color" with value "red".

3. Final Answer:

   All rows where the JSONB column contains the key "color" with value "red" -> Option A

4. Quick Check:

   @> means contains key-value pair exactly [OK]

Hint: Use @> to find exact key-value pairs in JSONB [OK]

Common Mistakes:

• Thinking @> matches only keys, not values
• Assuming it matches any occurrence of value
• Confusing JSON syntax causing errors

4. You created a GIN index on a JSONB column but your queries are still slow. What is the most likely cause?

medium

A. The GIN index does not support JSONB data types

B. The index was created without specifying the JSONB column

C. The queries are not using the @> operator

D. The table has no rows

Solution

1. Step 1: Understand when GIN index helps

   GIN indexes on JSONB columns speed up queries that use operators like @> which check for containment.

2. Step 2: Identify why queries remain slow

   If queries do not use @> or other supported operators, the index won't be used, causing slow performance.

3. Final Answer:

   The queries are not using the @> operator -> Option C

4. Quick Check:

   GIN index helps only with supported operators [OK]

Hint: Use @> operator to leverage GIN index speedup [OK]

Common Mistakes:

• Assuming GIN indexes speed up all JSONB queries
• Not verifying query operator usage
• Thinking GIN index doesn't support JSONB

5. You want to create a GIN index on a JSONB column info in table users to optimize queries checking for existence of specific keys only, ignoring values. Which index operator class should you use?

hard

A. jsonb_ops

B. jsonb_path_ops

C. btree_gin

D. hash_ops

Solution

1. Step 1: Understand GIN operator classes for JSONB

   PostgreSQL provides two main operator classes for JSONB GIN indexes: jsonb_ops (default) indexes keys and values, and jsonb_path_ops indexes only keys for existence queries.

2. Step 2: Choose operator class for key existence queries

   Since the goal is to optimize queries checking for key existence only, jsonb_path_ops is the best choice as it creates a smaller, faster index for this purpose.

3. Final Answer:

   jsonb_path_ops -> Option B

4. Quick Check:

   Key-only queries use jsonb_path_ops [OK]

Hint: Use jsonb_path_ops for key-existence queries [OK]

Common Mistakes:

• Using default jsonb_ops which indexes values too
• Choosing unrelated operator classes like btree_gin
• Confusing hash_ops with GIN operator classes