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.

Enable row level security on the table, then create policies that wrap functions like auth.uid() in a SELECT subquery so they execute once instead of per row. Always index columns referenced in policies and use security definer functions in a private schema for complex 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 IF NOT EXISTS for ADD CONSTRAINT, so the statement raises a syntax error. Wrap the alteration in a DO block that checks pg_constraint for the constraint name before adding it, making the migration idempotent.

Use cursor-based pagination whenever queries page deep into large tables, because OFFSET scans all skipped rows and slows linearly with page depth. Cursor pagination filters on the last seen key and uses an index, keeping performance constant regardless of page number.

Random UUIDv4 primary keys cause index fragmentation and scattered inserts on large tables. Prefer bigint identity columns for single databases, or time-ordered UUIDv7 via the pg_uuidv7 extension for distributed systems.