GIN index for full-text search in PostgreSQL - Mini Project: Build & Apply

Create a GIN Index for Full-Text Search in PostgreSQL

📖 Scenario: You manage a small library database that stores book titles and descriptions. You want to make searching for books by keywords faster and more efficient.

🎯 Goal: Build a PostgreSQL table with book data, add a full-text search column, and create a GIN index on it to speed up keyword searches.

📋 What You'll Learn

Create a table named books with columns id, title, and description

Add a column search_vector of type tsvector to store searchable text

Fill search_vector with combined text from title and description

Create a GIN index on the search_vector column

💡 Why This Matters

🌍 Real World

Full-text search is used in libraries, blogs, and e-commerce sites to quickly find relevant content by keywords.

💼 Career

Database developers and administrators often create indexes like GIN to optimize search performance in PostgreSQL.

1

Create the books table

Write SQL to create a table called books with columns: id as serial primary key, title as text, and description as text.

PostgreSQL

-- Create the books table with id, title, and description columns
-- Your code here

Need a hint?

Use SERIAL PRIMARY KEY for the id column to auto-increment.

2

Add the search_vector column

Write SQL to add a new column called search_vector of type tsvector to the books table.

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT,
    description TEXT
);
-- Add search_vector column of type tsvector
-- Your code here

Need a hint?

Use ALTER TABLE to add the new column.

3

Fill search_vector with combined text

Write SQL to update the search_vector column by combining title and description using to_tsvector and concatenation with ||.

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT,
    description TEXT
);

ALTER TABLE books ADD COLUMN search_vector tsvector;
-- Update search_vector with combined title and description
-- Your code here

Need a hint?

Use coalesce to avoid null values and concatenate title and description with a space.

4

Create a GIN index on search_vector

Write SQL to create a GIN index named idx_books_search_vector on the search_vector column of the books table.

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT,
    description TEXT
);

ALTER TABLE books ADD COLUMN search_vector tsvector;

UPDATE books SET search_vector = to_tsvector('english', coalesce(title, '') || ' ' || coalesce(description, ''));
-- Create a GIN index on search_vector
-- Your code here

Need a hint?

Use CREATE INDEX with USING GIN on the search_vector column.