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 find sequential scans, then apply composite, partial, or covering indexes depending on your query filters.

Use a pooler like PgBouncer between your application and Postgres so hundreds of concurrent users share a small pool of connections. Set pool_size around (CPU cores * 2) and prefer transaction mode unless you need prepared statements or temp tables.

Wrap functions like auth.uid() in a SELECT subquery so they execute once instead of per row, which can be 100x faster on large tables. Also add indexes on columns referenced in policies and use security definer functions for complex checks.

Yes, it includes a rule for eliminating N+1 queries by batching with WHERE id = ANY(array) or using JOINs instead of per-item loops. Examples show both pure SQL and application-level patterns with array parameters.

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

Named prepared statements are tied to individual connections, but transaction-mode pooling reassigns connections per transaction, causing 'prepared statement does not exist' errors. Use unnamed statements, deallocate after use, or switch to session mode.