Database indexes in Supabase

Introduction

Database indexes help find data faster, like a book's index helps find pages quickly.

When you want to speed up searching for users by email in a large user table.

When you need to quickly find orders by date in an orders table.

When filtering products by category often in an online store.

When joining tables on a specific column to improve query speed.

When sorting data by a column frequently used in reports.

Syntax

Supabase

CREATE INDEX index_name ON table_name (column_name);

Indexes are created on one or more columns to speed up queries.

Creating too many indexes can slow down data updates.

Examples

This creates an index on the email column in the users table to speed up email searches.

Supabase

CREATE INDEX idx_users_email ON users (email);

This index helps find orders quickly by their date.

Supabase

CREATE INDEX idx_orders_date ON orders (order_date);

This is a multi-column index to speed up queries filtering by category and sorting by price.

Supabase

CREATE INDEX idx_products_category_price ON products (category, price);

Sample Program

This example creates a users table and adds an index on the email column to speed up email lookups.

Supabase

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(100)
);

CREATE INDEX idx_users_email ON users (email);

Important Notes

Indexes speed up read queries but can slow down inserts, updates, and deletes because the index must be updated too.

Use indexes on columns you search or join on often.

Supabase uses PostgreSQL, so all PostgreSQL index features apply.

Summary

Indexes help find data faster by creating a quick lookup structure.

Create indexes on columns used often in searches or joins.

Too many indexes can slow down data changes.