Postgres indexing on Supabase: what to index and how to check it

Which indexes does every Supabase app need?

• Foreign keys. Postgres does not index them for you, and joins and cascading deletes scan the child table without one.
• RLS columns such as `user_id`, `team_id` and `tenant_id`.
• Sort columns for feeds and lists, usually `created_at`.

create index orders_customer_id_idx on public.orders (customer_id);
create index orders_tenant_created_idx on public.orders (tenant_id, created_at desc);

Which index type should I use?

• B-tree (the default): `=`, `<`, `>`, `between`, `order by`.
• GIN: jsonb containment (`@>`), arrays and full-text search.
• Partial: a query that always has the same filter, such as `where status = 'open'`.
• Expression: a query on a function of a column, such as `lower(email)`.

create index tickets_open_idx on public.tickets (created_at) where status = 'open';
create index users_email_lower_idx on public.profiles (lower(email));
create index events_payload_idx on public.events using gin (payload);

How do I order the columns in a composite index?

Put equality columns first and the range or sort column last. An index on `(tenant_id, created_at)` serves `where tenant_id = $1 order by created_at desc`; an index on `(created_at, tenant_id)` does not serve it well.

How do I check that an index is used?

Look for `Index Scan` or `Index Only Scan` on the table. A `Seq Scan` on a large table means the index is missing or does not match the query.

explain (analyze, buffers)
select * from public.orders
where tenant_id = '...' order by created_at desc limit 20;