Database indexes in Supabase - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

What is the main benefit of a database index?

Imagine you have a large table with millions of rows. You want to find rows quickly based on a column value. What does adding an index on that column do?

A It speeds up data retrieval by allowing the database to find rows faster.

B It compresses the data to save storage space.

C It automatically backs up the table data regularly.

D It encrypts the column data for security.

💡 Hint

Think about how a book's index helps you find a topic quickly.

An index creates a quick lookup structure that helps the database find rows without scanning the entire table.

❓ service_behavior

intermediate

What happens when you query a table without an index on the searched column?

You run a query filtering by a column that has no index. What is the expected behavior?

A The database caches the query results for future use.

B The database instantly returns results using a hidden index.

C The query fails with an error about missing indexes.

D The database scans every row in the table to find matches, which is slower.

💡 Hint

Without a map or index, how do you find a word in a dictionary?

Without an index, the database must check each row one by one, which takes more time.

❓ Configuration

advanced

Which SQL command creates a unique index on the 'email' column in a Supabase PostgreSQL table named 'users'?

Choose the correct SQL statement to create a unique index on the 'email' column.

A CREATE UNIQUE INDEX idx_users_email ON users(email);

B CREATE INDEX UNIQUE idx_users_email ON users(email);

C CREATE UNIQUE KEY idx_users_email ON users(email);

D CREATE INDEX idx_users_email UNIQUE ON users(email);

💡 Hint

Remember the correct order of keywords in PostgreSQL for unique indexes.

The correct syntax is CREATE UNIQUE INDEX index_name ON table(column);

❓ security

advanced

What is a potential security risk of indexing sensitive columns like passwords?

Why should you avoid creating indexes on sensitive data columns such as passwords?

A Indexes slow down the database, causing denial of service.

B Indexes store data in plain text, which can expose sensitive information if accessed.

C Indexes prevent data encryption on those columns.

D Indexes automatically share data with third-party services.

💡 Hint

Think about where index data is stored and who can access it.

Indexes can store copies of column data in plain text, so indexing sensitive data risks exposure if the index is accessed.

❓ Architecture

expert

In a Supabase project, you want to optimize read performance for a table with frequent writes and reads. Which indexing strategy balances performance best?

Given a table with many inserts and updates, but also many read queries filtering by 'status' and 'created_at', which indexing approach is best?

A Avoid indexes entirely to keep write speed high, accepting slower reads.

B Create separate indexes on 'status' and 'created_at' columns to maximize read speed at any cost.

C Create a composite index on (status, created_at) to speed up filtered reads while minimizing write overhead.

D Create a full-text index on all columns to cover all query types.

💡 Hint

Think about how composite indexes help with multi-column queries and write costs.

A composite index on the queried columns improves read speed for those filters and reduces the number of indexes to update on writes, balancing performance.