supabase-postgres-best-practices

Start by adding indexes on WHERE and JOIN columns, which can yield 100-1000x speedups on large tables. Use EXPLAIN ANALYZE to identify sequential scans, then apply composite, partial, or covering indexes matching your query filters.

Replace per-item queries in loops with a single batch query using WHERE id = ANY(array[...]) or a JOIN. This reduces database round trips from N+1 to one, typically improving performance 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.

Functions like auth.uid() are called once per row unless wrapped in a SELECT subquery, which caches the result. Rewrite policies as (select auth.uid()) = user_id and add indexes on columns referenced in policies.

OFFSET pagination scans all skipped rows, degrading linearly with page depth. Cursor-based (keyset) pagination using WHERE id > last_seen_id maintains constant O(1) performance regardless of page number.

Use GIN indexes for JSONB containment queries with the @> operator, since B-tree indexes cannot optimize them. The jsonb_path_ops operator class produces indexes 2-3x smaller when only containment queries are needed.