supabase-postgres-best-practices

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

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.

Prepared statements are tied to individual connections, so they fail in transaction-mode pooling where connections are shared. Use unnamed prepared statements, deallocate statements after use, or switch to session-mode pooling to avoid conflicts.

RLS policies that call functions like auth.uid() directly execute them once per row. Wrap the function in a subselect like (select auth.uid()) so it is evaluated once, and add indexes on columns referenced in the policy.

Use cursor-based pagination whenever users paginate deep into large result sets. OFFSET scans all skipped rows, so page 10000 scans 200,000 rows, while keyset pagination with WHERE id > last_seen_id stays O(1) regardless of depth.

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