supabase-postgres-best-practices

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

Use a GIN index for JSONB containment queries with the @> operator, since B-tree indexes cannot optimize them. For single-key lookups, an expression index on attributes->>'key' is more efficient.

Yes, the rules apply to Postgres running anywhere, since they target core Postgres features like indexes, RLS, partitioning, and connection pooling. Supabase-specific notes are included only where relevant, such as auth.uid() in RLS policies.

Replace per-item queries in a loop with a single batch query using WHERE id = ANY($1::bigint[]) or a JOIN. This reduces database round trips from N+1 to one, typically improving throughput 10-100x.

Functions like auth.uid() are called per row unless wrapped in a SELECT subquery, which caches the result. Also add indexes on columns referenced in policies and use security definer functions for complex membership checks.

Use cursor-based (keyset) pagination whenever queries page deep into large tables, because OFFSET scans all skipped rows and slows linearly. Cursor pagination with WHERE id > last_id stays O(1) regardless of page depth.