GIN index for arrays and JSONB in PostgreSQL - Mini Project: Build & Apply

Creating and Using GIN Indexes for Arrays and JSONB in PostgreSQL

📖 Scenario: You are managing a PostgreSQL database for a book store. The store keeps track of books with tags and additional metadata stored as JSONB. To speed up searches on tags and JSONB fields, you want to create GIN indexes.

🎯 Goal: Build a PostgreSQL table with array and JSONB columns, then create GIN indexes on these columns to optimize search queries.

📋 What You'll Learn

Create a table called books with columns id (integer primary key), title (text), tags (text array), and metadata (JSONB).

Insert three specific rows into the books table with given values.

Create a GIN index on the tags column.

Create a GIN index on the metadata column.

💡 Why This Matters

🌍 Real World

Many modern applications store tags as arrays and flexible data as JSONB in PostgreSQL. GIN indexes help speed up searches on these complex data types.

💼 Career

Database administrators and backend developers often create GIN indexes to improve query performance on array and JSONB columns in PostgreSQL.

1

Create the books table with array and JSONB columns

Write a SQL statement to create a table called books with these columns: id as an integer primary key, title as text, tags as a text array, and metadata as JSONB.

PostgreSQL

-- Write the CREATE TABLE statement for books with id, title, tags (text[]), and metadata (JSONB)
-- Your code here

Need a hint?

Use SERIAL PRIMARY KEY for id. Define tags as TEXT[] and metadata as JSONB.

2

Insert three rows into the books table

Insert these three rows into books:
1. title: 'PostgreSQL Basics', tags: ['database', 'sql'], metadata: {'author': 'Alice', 'year': 2020}
2. title: 'Advanced SQL', tags: ['database', 'sql', 'advanced'], metadata: {'author': 'Bob', 'year': 2021}
3. title: 'JSON and Arrays', tags: ['json', 'arrays'], metadata: {'author': 'Carol', 'year': 2022}

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    tags TEXT[],
    metadata JSONB
);
-- Insert the three rows into books
-- Your code here

Need a hint?

Use ARRAY[...] syntax for the tags array. Use JSON syntax inside single quotes for metadata.

3

Create a GIN index on the tags column

Write a SQL statement to create a GIN index named idx_books_tags on the tags column of the books table.

PostgreSQL

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

INSERT INTO books (title, tags, metadata) VALUES
('PostgreSQL Basics', ARRAY['database', 'sql'], '{"author": "Alice", "year": 2020}'),
('Advanced SQL', ARRAY['database', 'sql', 'advanced'], '{"author": "Bob", "year": 2021}'),
('JSON and Arrays', ARRAY['json', 'arrays'], '{"author": "Carol", "year": 2022}');
-- Create a GIN index on tags
-- Your code here

Need a hint?

Use CREATE INDEX idx_books_tags ON books USING GIN (tags); to create the index.

4

Create a GIN index on the metadata JSONB column

Write a SQL statement to create a GIN index named idx_books_metadata on the metadata column of the books table.

PostgreSQL

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

INSERT INTO books (title, tags, metadata) VALUES
('PostgreSQL Basics', ARRAY['database', 'sql'], '{"author": "Alice", "year": 2020}'),
('Advanced SQL', ARRAY['database', 'sql', 'advanced'], '{"author": "Bob", "year": 2021}'),
('JSON and Arrays', ARRAY['json', 'arrays'], '{"author": "Carol", "year": 2022}');

CREATE INDEX idx_books_tags ON books USING GIN (tags);
-- Create a GIN index on metadata
-- Your code here

Need a hint?

Use CREATE INDEX idx_books_metadata ON books USING GIN (metadata); to create the index.