supabase-postgres-best-practices

Start by adding indexes on WHERE and JOIN columns, which can yield 100-1000x speedups on large tables. Use EXPLAIN ANALYZE to identify sequential scans and rows removed by filters, then apply composite, partial, or covering indexes matching your query patterns.

Use a pooler like PgBouncer between your application and Postgres, sizing the pool at roughly (CPU cores x 2). Transaction mode suits most applications, but prepared statements and temporary tables require session mode or unnamed statements.

Wrap functions like auth.uid() in a SELECT subquery so they execute once instead of per row, giving 100x+ improvements on large tables. Always index columns referenced in policies and use security definer functions for complex membership checks.

No, Postgres does not index foreign key columns automatically. Missing FK indexes cause slow JOINs and table-scanning CASCADE deletes, so create an index on every referencing column explicitly.

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

Partition when tables exceed roughly 100 million rows, contain time-series data queried by date ranges, or require efficient bulk deletion of old data. Range partitioning by timestamp lets queries scan only relevant partitions and makes dropping old data instant.