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

Use a pooler like PgBouncer between your application and Postgres so many concurrent users share a small connection pool. Size the pool around (CPU cores * 2) and prefer transaction mode unless you need session-level prepared statements.

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.

Cursor-based (keyset) pagination is preferred because OFFSET scans all skipped rows, getting slower on deeper pages. Filtering with WHERE id > last_seen_id keeps performance constant regardless of page depth.

Partition when tables exceed roughly 100M rows, when queries filter by time ranges, or when you need to drop old data efficiently. Range partitioning by date lets queries scan only relevant partitions and makes old-data removal instant.

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