Indexes

An index lets Postgres find rows without reading the whole table — the difference between milliseconds and seconds once a table has more than a few thousand rows. Each index takes disk space and makes every insert and update a little slower, so add the ones your queries need and remove the ones nobody uses.

#When to add an index

• Columns you filter on often: where user_id = $1, where status = 'open'.
• Foreign key columns. Postgres indexes the referenced key, not the column that points to it, so orders.customer_id needs its own index for joins and for deleting customers quickly.
• Columns you sort or paginate by: order by created_at desc limit 20.
• Columns used in row-level security policies (owner_id): the policy is added to every query (Row-level security and access).

Primary keys and unique constraints get an index automatically. The Performance advisor suggests indexes from your slowest queries, and Performance explains how to read a query plan with Explain in the SQL editor.

#In the console

Indexes that enforce a primary key or unique constraint can't be deleted here: remove the constraint in the Table editor and the index goes with it.

#Create an index

1. 1

   New index

   Pick the table, then tick columns in the order you want them: the one you filter on most first.

2. 2

   Type

   Leave btree unless the table below says otherwise.

3. 3

   Unique and partial

   Unique refuses two rows with the same values. Only rows where makes a partial index, e.g. deleted_at IS NULL.

4. 4

   Check, then create

   Check builds it in a transaction that is rolled back; Create index keeps it as a recorded migration you can undo.

-- Foreign key used in joins
create index orders_customer_id_idx on orders (customer_id);

-- "My open orders, newest first"
create index orders_customer_status_created_idx on orders (customer_id, status, created_at desc);

-- One active subscription per user; cancelled ones don't count
create unique index subscriptions_one_active_key on subscriptions (user_id) where cancelled_at is null;

-- Case-insensitive email lookups: where lower(email) = lower($1)
create index users_lower_email_idx on users (lower(email));

-- jsonb containment: where tags @> '["sale"]'
create index products_tags_idx on products using gin (tags);

#Index types

#Multi-column, unique and partial indexes

• Order matters. An index on (customer_id, created_at) serves where customer_id = $1, and where customer_id = $1 order by created_at, but not where created_at > $1 alone.
• Put equality columns first and the range or sort column last.
• Unique indexes enforce uniqueness like a unique constraint. nulls don't clash with each other unless you add nulls not distinct in SQL.
• Partial indexes cover only matching rows: smaller, faster to update, and the way to enforce "unique among active rows". A query uses one only when its where implies the index's condition.

#Building on large tables

Indexes created from the console or a migration are built inside a transaction, which blocks writes to the table while it runs: well under a second for small tables, longer for millions of rows. Migrations wait at most 15 seconds for a lock and run for at most 120 seconds (Migrations).

For big tables in production, build it with create index concurrently over a direct connection, then record it with a migration that uses if not exists — Safe schema changes shows how. If a concurrent build fails it leaves an Invalid index: delete it and build it again.

#Keep indexes lean

• Review the Unused tag now and then. Check a replica or staging environment before deleting, as some indexes serve rare but important queries (month-end reports).
• Two indexes where one's columns start the other ((a) and (a, b)) usually means the shorter one can go.
• Delete is undoable: Version History re-creates the index from its saved definition.
• Index size counts toward your database disk (Database: connection, compute, disk and backups).

#Limits

Every index counts against the plan, including those behind primary keys and unique constraints. Indexes on Sovaria's own tables don't.

#Coming from Firebase or Supabase?

Moving an existing app? Migrate from Supabase or Firebase imports users (with their passwords), data, files and rules.