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 depending on your query patterns.

Use a pooler like PgBouncer between your application and Postgres, sizing the pool at roughly (CPU cores * 2) plus spindle count. Transaction mode works for most applications, but use session mode if you rely on named prepared statements.

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, often improving performance 10-100x.

Poorly written RLS policies can, especially when functions like auth.uid() are called per row. Wrap function calls in a SELECT subquery so they execute once, and always index columns referenced in policies for 5-10x faster RLS queries.

Use cursor-based (keyset) pagination whenever users paginate deeply into large datasets. OFFSET scans all skipped rows and gets slower with page depth, while cursor pagination with WHERE id > last_id maintains constant O(1) performance.