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 for 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 checks.

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

OFFSET scans and discards all skipped rows, so page 10000 reads hundreds of thousands of rows. Cursor-based pagination using WHERE id > last_seen_id uses the index directly and stays O(1) regardless of page depth.

No, Postgres does not support ADD CONSTRAINT IF NOT EXISTS and it raises a syntax error. Use a DO block that checks pg_constraint for the constraint name before running ALTER TABLE to make migrations 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.