Partial indexes with WHERE clause in PostgreSQL - Mini Project: Build & Apply

Creating Partial Indexes with WHERE Clause in PostgreSQL

📖 Scenario: You are managing a PostgreSQL database for an online bookstore. The books table contains information about all books, including their id, title, author, price, and stock status.To improve query performance for books that are currently in stock, you want to create a partial index that only indexes rows where stock is greater than zero.

🎯 Goal: Build a partial index on the books table that indexes only the books with stock > 0 using the WHERE clause.

📋 What You'll Learn

Create a books table with columns id, title, author, price, and stock.

Insert sample data into the books table with some books having stock greater than zero and some with zero stock.

Create a partial index on the books table that indexes only rows where stock > 0.

Verify the partial index creation with the correct WHERE clause.

💡 Why This Matters

🌍 Real World

Partial indexes help speed up queries by indexing only relevant rows, saving space and improving performance in large databases.

💼 Career

Database administrators and backend developers use partial indexes to optimize query speed and resource usage in production systems.

1

Create the books table and insert sample data

Create a table called books with columns id (integer primary key), title (text), author (text), price (numeric), and stock (integer). Then insert these exact rows: (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99, 3), (2, '1984', 'George Orwell', 8.99, 0), (3, 'To Kill a Mockingbird', 'Harper Lee', 12.99, 5).

PostgreSQL

-- Create the books table and insert sample data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Define the partial index condition

Create a variable or note the condition stock > 0 that will be used in the WHERE clause of the partial index.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    price NUMERIC,
    stock INTEGER
);

INSERT INTO books (id, title, author, price, stock) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99, 3),
(2, '1984', 'George Orwell', 8.99, 0),
(3, 'To Kill a Mockingbird', 'Harper Lee', 12.99, 5);

-- Define the partial index condition
-- Your code here

Need a hint?

Just write the condition stock > 0 as a comment or variable for clarity.

3

Create the partial index on books for in-stock books

Write a SQL statement to create an index named idx_books_in_stock on the books table for the stock column, but only for rows where stock > 0 using the WHERE clause.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    price NUMERIC,
    stock INTEGER
);

INSERT INTO books (id, title, author, price, stock) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99, 3),
(2, '1984', 'George Orwell', 8.99, 0),
(3, 'To Kill a Mockingbird', 'Harper Lee', 12.99, 5);

-- Partial index condition
-- stock > 0

-- Create the partial index below
-- Your code here

Need a hint?

Use CREATE INDEX index_name ON table(column) WHERE condition syntax.

4

Verify the partial index creation

Write a SQL query to list all indexes on the books table and confirm that the index idx_books_in_stock exists with the WHERE stock > 0 condition.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    price NUMERIC,
    stock INTEGER
);

INSERT INTO books (id, title, author, price, stock) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99, 3),
(2, '1984', 'George Orwell', 8.99, 0),
(3, 'To Kill a Mockingbird', 'Harper Lee', 12.99, 5);

-- Partial index condition
-- stock > 0

CREATE INDEX idx_books_in_stock ON books(stock) WHERE stock > 0;

-- Verify the partial index below
-- Your code here

Need a hint?

Use the pg_indexes system catalog to check indexes on a table.