supabase-postgres-best-practices

Add indexes on columns used in WHERE and JOIN clauses to avoid sequential scans, which can be 100-1000x slower on large tables. Use composite indexes for multi-column filters, partial indexes for filtered queries, and covering indexes with INCLUDE to enable index-only scans.

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. Always index columns referenced in policies, and use SECURITY DEFINER functions in a private schema for complex membership checks.

Named prepared statements fail in transaction-mode pooling because connections are shared between requests. Use unnamed prepared statements, deallocate statements after use, or switch to session-mode pooling where the connection persists.

Postgres does not support ADD CONSTRAINT IF NOT EXISTS, so that syntax throws a SQLSTATE 42601 error. Wrap the ALTER TABLE in a DO block that checks pg_constraint for the constraint name before adding it, making the migration idempotent.

Partition tables exceeding roughly 100 million rows, time-series data queried by date ranges, or tables where you need to drop old data efficiently. Range partitioning by timestamp lets queries scan only relevant partitions and makes dropping old partitions instant.

Use bigint generated always as identity for single-database sequential IDs. For distributed systems, use time-ordered UUIDv7 via the pg_uuidv7 extension instead of random UUIDv4, which causes index fragmentation on large tables.