supabase-postgres-best-practices

Start by running EXPLAIN ANALYZE on the slow query to identify sequential scans, rows removed by filter, and buffer misses. Then add indexes on WHERE and JOIN columns, use composite indexes for multi-column filters, and consider partial or covering indexes for common query patterns.

Replace per-item queries in loops with a single batch query using WHERE id = ANY($1::bigint[]) with an array parameter, or rewrite the logic as a JOIN. This reduces database round trips from N+1 to one, often improving performance 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 for the entire session.

Functions like auth.uid() in policies are called once per row by default. Wrap them in a subselect like (select auth.uid()) so they execute once, add indexes on columns referenced in policies, and use security definer functions for complex membership checks.

OFFSET pagination scans all skipped rows, so it degrades linearly on deep pages. Cursor-based (keyset) pagination using WHERE id > last_seen_id with an index delivers constant O(1) performance regardless of page depth, making it the better choice for large tables.

Use a GIN index for JSONB containment queries with operators like @>, since the default B-tree cannot optimize them. Choose jsonb_path_ops for a 2-3x smaller index when you only need the @> operator, or create expression indexes for specific key lookups.