supabase-postgres-best-practices

Start by running EXPLAIN ANALYZE to find sequential scans and rows removed by filters, then add indexes on WHERE and JOIN columns. The rules cover composite indexes, partial indexes, covering indexes with INCLUDE, and choosing the right index type such as GIN for JSONB.

Replace per-item queries in a loop with a single batch query using WHERE id = ANY($1::bigint[]) or a JOIN. This reduces database round trips from N+1 to one, typically giving 10-100x fewer round trips.

Wrap functions like auth.uid() in a subselect, for example (select auth.uid()) = user_id, so they are evaluated once instead of per row. Also add indexes on columns used in policies and use security definer functions for complex membership checks.

Named prepared statements fail in transaction-mode pooling because connections are shared between requests. Use unnamed prepared statements, deallocate after use, or switch to session-mode pooling where the connection persists.

Use cursor-based (keyset) pagination whenever users page deeply into large result sets. OFFSET scans all skipped rows and gets slower per page, while WHERE id > last_seen_id uses an index and stays O(1) regardless of page depth.

Deadlocks occur when transactions lock rows in different orders. Prevent them by acquiring locks in a consistent order with SELECT ... ORDER BY id FOR UPDATE, or by combining updates into a single atomic statement.