supabase-postgres-best-practices

Start with EXPLAIN ANALYZE to find sequential scans and rows removed by filters, then add indexes on WHERE and JOIN columns. The skill's query performance rules cover missing indexes, composite indexes, partial indexes, and covering indexes with measured impact.

Wrap functions like auth.uid() in a SELECT subquery so they execute once instead of per row, and index every column used in policies. For complex checks, use SECURITY DEFINER functions in a private schema with EXECUTE revoked from public roles.

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.

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 constant-time 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 instantly. Range partitioning by date lets queries scan only relevant partitions and makes retention a fast DROP TABLE.