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.

Practice

1. What is the main purpose of creating a database index in Supabase?

easy

A. To speed up data searches and queries

B. To store more data in the database

C. To backup the database automatically

D. To encrypt the data for security

Solution

1. Step 1: Understand what an index does

   An index creates a quick lookup structure to find data faster.

2. Step 2: Compare options with index purpose

   Only speeding up searches matches the index function.

3. Final Answer:

   To speed up data searches and queries -> Option A

4. Quick Check:

   Index = Faster search [OK]

Hint: Indexes speed up searches, not storage or security [OK]

Common Mistakes:

• Thinking indexes store more data
• Confusing indexes with backups
• Assuming indexes encrypt data

2. Which of the following is the correct SQL syntax to create an index on the column email in a Supabase table named users?

easy

A. CREATE INDEX email ON users;

B. INDEX CREATE users.email;

C. CREATE INDEX ON users (email);

D. CREATE users INDEX email;

Solution

1. Step 1: Recall correct SQL index syntax

   The standard syntax is: CREATE INDEX index_name ON table_name (column_name); but index_name is optional.

2. Step 2: Match syntax with options

   CREATE INDEX ON users (email); uses correct syntax without specifying index name, which is allowed.

3. Final Answer:

   CREATE INDEX ON users (email); -> Option C

4. Quick Check:

   CREATE INDEX ON table (column) [OK]

Hint: Use CREATE INDEX ON table (column) for indexes [OK]

Common Mistakes:

• Placing column before table incorrectly
• Using wrong keyword order
• Omitting ON keyword

3. Given the table orders with an index on customer_id, what will be the effect of this query?

SELECT * FROM orders WHERE customer_id = 123;

medium

A. The query will return no results because indexes filter data

B. The query will ignore the index and scan the whole table

C. The query will fail due to missing index

D. The query will run faster by using the index on customer_id

Solution

1. Step 1: Understand index usage in queries

   Indexes help the database find matching rows quickly when filtering by indexed columns.

2. Step 2: Analyze the query filter

   The query filters by customer_id, which has an index, so the index will be used.

3. Final Answer:

   The query will run faster by using the index on customer_id -> Option D

4. Quick Check:

   Indexed column filter = faster query [OK]

Hint: Queries filtering indexed columns run faster [OK]

Common Mistakes:

• Thinking index causes query failure
• Assuming index filters out data
• Believing index is ignored automatically

4. You created multiple indexes on a Supabase table, but now data insertions are very slow. What is the most likely cause?

medium

A. Too many indexes slow down data changes

B. Indexes cause data loss during insert

C. Indexes prevent data from being inserted

D. Indexes automatically delete old data

Solution

1. Step 1: Recall how indexes affect data changes

   Each index must be updated when data changes, so many indexes slow inserts and updates.

2. Step 2: Match symptoms with cause

   Slow insertions match the effect of too many indexes.

3. Final Answer:

   Too many indexes slow down data changes -> Option A

4. Quick Check:

   Many indexes = slower inserts [OK]

Hint: More indexes = slower writes, fewer indexes = faster writes [OK]

Common Mistakes:

• Thinking indexes cause data loss
• Assuming indexes block inserts
• Believing indexes delete data automatically

5. You want to optimize a Supabase table products that is frequently searched by category and price. Which index strategy is best to improve query speed without slowing inserts too much?

hard

A. Create no indexes to keep inserts fast

B. Create a composite index on (category, price)

C. Create an index only on price

D. Create separate indexes on category and price

Solution

1. Step 1: Understand composite vs separate indexes

   A composite index on (category, price) helps queries filtering both columns efficiently.

2. Step 2: Consider insert performance

   One composite index is faster to update than two separate indexes, balancing speed and insert cost.

3. Step 3: Evaluate options

   Create a composite index on (category, price) optimizes queries on both columns with minimal insert slowdown.

4. Final Answer:

   Create a composite index on (category, price) -> Option B

5. Quick Check:

   Composite index = faster multi-column queries [OK]

Hint: Use composite indexes for multi-column searches [OK]

Common Mistakes:

• Creating too many separate indexes
• Indexing only one column when both are searched
• Avoiding indexes and hurting query speed