supabase-postgres-best-practices

Add indexes on columns used in WHERE and JOIN clauses to replace sequential scans with index scans, which can be 100-1000x faster on large tables. Use EXPLAIN ANALYZE to confirm the query plan changes from Seq Scan to Index Scan.

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

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 connections persist.

RLS policies calling functions like auth.uid() per row cause severe slowdowns. Wrap the function in a SELECT subquery so it executes once, and add indexes on columns referenced in the policy.

Use cursor-based pagination whenever users page deeply into results, since OFFSET scans all skipped rows and slows linearly. Keyset pagination with WHERE id > last_seen_id stays O(1) regardless of page depth.

Use a GIN index for JSONB containment queries with the @> operator, since the default B-tree cannot optimize them. Choose jsonb_path_ops for a 2-3x smaller index when only containment queries are needed.