supabase-postgres-best-practices

Start by running EXPLAIN ANALYZE on the slow query to find sequential scans and rows removed by filters. Then add indexes on WHERE and JOIN columns, use composite or partial indexes for multi-column filters, and check pg_stat_statements for the highest total-time queries.

Wrap functions like auth.uid() in a SELECT subquery so they execute once instead of per row, and always index columns referenced in policies. For complex checks, use SECURITY DEFINER functions in a private schema with an explicit auth.uid() check inside.

No, Postgres does not create indexes on foreign key columns automatically. You must create them manually, otherwise JOINs and ON DELETE CASCADE operations trigger full table scans and lock the table.

Postgres does not support IF NOT EXISTS for ADD CONSTRAINT, so that syntax raises a SQLSTATE 42601 error. Use a DO block that checks pg_constraint for the constraint name before executing ALTER TABLE.

Use connection pooling for any application with concurrent users, since each Postgres connection consumes 1-3MB of RAM. A pooler like PgBouncer in transaction mode lets hundreds of users share a small pool of connections, but avoid named prepared statements in transaction mode.

Use cursor-based (keyset) pagination with WHERE id > last_seen_id for consistent O(1) performance at any page depth. OFFSET scans all skipped rows, so page 10000 reads hundreds of thousands of rows before returning results.