supabase-postgres-best-practices

Start by adding indexes on columns used in WHERE and JOIN clauses, which can yield 100-1000x speedups on large tables. Use EXPLAIN ANALYZE to confirm whether the planner uses an index scan instead of a sequential scan, then consider composite, partial, or covering indexes for multi-column or filtered queries.

B-tree is the default and handles equality and range comparisons. Use GIN for JSONB, arrays, and full-text search, BRIN for large time-series tables, and Hash for equality-only lookups. Choosing the right type can improve queries 10-100x.

Wrap functions like auth.uid() in a SELECT subquery so they execute once instead of per row, which can be 100x faster on large tables. Add indexes on columns referenced in policies and use security definer functions for complex membership checks.

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 connections persist for the whole session.

Partition when tables exceed roughly 100M rows, when queries filter by time ranges, or when you need to drop old data efficiently. Range partitioning by date lets queries scan only relevant partitions and makes dropping old partitions instant compared to DELETE.

OFFSET forces the database to scan and discard all skipped rows, so deep pages get progressively slower. Cursor-based (keyset) pagination using WHERE id > last_seen_id uses the index directly and maintains constant O(1) performance regardless of page depth.