supabase-postgres-best-practices

Start by adding indexes on WHERE and JOIN columns to eliminate sequential scans, then use EXPLAIN ANALYZE to confirm the query plan. The query performance rules cover composite indexes, partial indexes, covering indexes, and choosing the right index type such as GIN for JSONB.

Replace per-item queries in loops with a single batch query using WHERE id = ANY($1) with an array parameter, or rewrite the logic as a JOIN. This reduces database round trips from N+1 to one, often improving throughput 10-100x.

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.

Wrap functions like auth.uid() in a SELECT subquery so they execute once instead of per row, and add indexes on columns referenced in policies. For complex checks, use security definer functions to avoid per-row evaluation overhead.

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 old-data deletion instant via DROP TABLE.

OFFSET forces Postgres to scan and discard all skipped rows, so deep pages get progressively slower. Cursor-based (keyset) pagination using WHERE id > last_seen_id uses the index directly and maintains constant performance regardless of page depth.