Database indexes in Supabase - Mini Project: Build & Apply

Database Indexes with Supabase

📖 Scenario: You are managing a Supabase database for an online bookstore. The database has a table called books that stores information about each book, including its id, title, author, and published_year.To improve the speed of searching books by their author, you want to create an index on the author column.

🎯 Goal: Create a database index on the author column of the books table in Supabase to speed up queries filtering by author.

📋 What You'll Learn

Create a table called books with columns id, title, author, and published_year.

Add a configuration variable to hold the index name.

Write the SQL command to create an index on the author column using the index name variable.

Complete the SQL script by adding a command to verify the index creation.

💡 Why This Matters

🌍 Real World

Indexes help databases find data faster, just like an index in a book helps you find a topic quickly.

💼 Career

Database indexing is a key skill for backend developers and cloud engineers to optimize application performance.

1

Create the books table

Write SQL to create a table called books with these columns and types: id as integer primary key, title as text, author as text, and published_year as integer.

Supabase

-- Your SQL code here to create the books table

Hint

Use CREATE TABLE books and define each column with its type. Remember to set id as the primary key.

2

Define the index name variable

Add a SQL variable called index_name and set it to the string 'idx_books_author' to hold the name of the index.

Supabase

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    published_year INTEGER
);

-- Define the index_name variable here
-- Your code here

Hint

Use the \set command in psql to define a variable named index_name.

3

Create the index on author

Write the SQL command to create an index using the variable index_name on the author column of the books table.

Supabase

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    published_year INTEGER
);

\set index_name 'idx_books_author'

-- Create the index on author column here
-- Your code here

Hint

Use CREATE INDEX :index_name ON books(author); to create the index using the variable.

4

Verify the index creation

Add a SQL command to list all indexes on the books table to verify the index was created.

Supabase

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    published_year INTEGER
);

\set index_name 'idx_books_author'

CREATE INDEX :index_name ON books(author);

-- Add command to list indexes on books table
-- Your code here

Hint

Use the pg_indexes system catalog to list indexes on the books table.