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 and rows removed by filters, then apply composite, partial, or covering indexes based 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 or temporary tables.

Wrap functions like auth.uid() in a subselect, such as (select auth.uid()), so they execute once instead of per row. Also add indexes on columns referenced in policies and use security definer functions for complex membership checks.

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

Partition when tables exceed roughly 100 million 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 dropping old partitions instant.

Postgres does not support IF NOT EXISTS for ADD CONSTRAINT, causing a syntax error. Wrap the statement in a DO block that checks pg_constraint for the constraint name before executing ALTER TABLE.