Database Optimizer

Run EXPLAIN ANALYZE on the query to inspect the execution plan. Look for sequential scans on large tables, compare actual versus estimated rows, then add appropriate indexes or rewrite the query with JOINs instead of subqueries.

Replace per-record queries with a single query using JOINs and json_agg aggregation, or use batch loading. Fetching users and their posts in one grouped query eliminates the N+1 pattern entirely.

B-tree indexes suit most equality and range queries. Use GIN for JSONB and full-text search, GiST for geometric data, and partial indexes when queries consistently filter on a condition like status equals published.

A standard CREATE INDEX locks the table against writes. Use CREATE INDEX CONCURRENTLY to build the index without blocking reads or writes, which is essential for zero-downtime production migrations.

Yes, Supabase provides a transaction-mode pooler, typically on port 6543 instead of 5432. Point your connection string at the pooler port for serverless environments where opening connections per request is not viable.

Denormalize only after measuring a proven performance bottleneck that indexing cannot fix. Start with a normalized schema, since premature denormalization adds data duplication and consistency risks without guaranteed benefit.