supabase-postgres-best-practices

Start by adding indexes on WHERE and JOIN columns, then use EXPLAIN ANALYZE to identify sequential scans and rows removed by filters. The query performance rules cover composite indexes, covering indexes with INCLUDE, and partial indexes for filtered queries.

Use a pooler like PgBouncer between your application and Postgres, sizing the pool at roughly (CPU cores * 2) plus spindle count. Transaction mode works for most apps, but prepared statements require unnamed statements or session mode to avoid conflicts.

No, Postgres does not automatically index foreign key columns. Missing FK indexes cause slow JOINs and table-locking CASCADE deletes, so you should create an index on every referencing column and audit for missing ones with a pg_constraint query.

Wrap functions like auth.uid() in a SELECT subquery so they execute once instead of per row, which can be over 100x faster on large tables. Also add indexes on columns referenced in policies and use security definer functions for complex checks.

OFFSET scans and discards all skipped rows, so page 10000 scans 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, 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 removal an instant DROP TABLE.