supabase-postgres-best-practices

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

Replace per-item queries in loops with a single batch query using WHERE id = ANY($1::bigint[]) or 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 conflict with transaction-mode pooling. Use unnamed prepared statements, deallocate after use, or switch to session-mode pooling where connections persist.

RLS policies that call functions like auth.uid() per row cause severe slowdowns. Wrap function calls in a subselect like (select auth.uid()) so they execute once, and add indexes on columns referenced in policies.

Use cursor-based (keyset) pagination whenever users paginate deep into large tables. OFFSET scans all skipped rows and gets slower with page depth, while cursor pagination with WHERE id > last_id stays O(1) using the index.

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 smaller and faster.