Database Patterns

Production Postgres patterns for web applications: schema design, querying, pooling, migrations, and optimization.

Connection pooling

• Never create a new connection per request — use a pool
• Supabase uses PgBouncer in transaction mode by default
• Serverless environments need external poolers (Supavisor, PgBouncer, Neon pooler)
• Keep pool size proportional to expected concurrency

Schema design

• Use UUIDs for primary keys (gen_random_uuid())
• Add created_at and updated_at timestamps to every table
• Use foreign keys for referential integrity
• Use enums or CHECK constraints for status fields
• Prefer text over varchar(n) unless you have a real length constraint

Indexing

• Index columns used in WHERE, JOIN, and ORDER BY
• Composite indexes for multi-column queries (order matters)
• GIN indexes for full-text search and JSONB containment
• Partial indexes for frequently filtered subsets
• Don't over-index — each index adds write overhead

Query optimization

• Use EXPLAIN ANALYZE to profile queries
• Avoid N+1 queries — use JOINs or batch fetches
• Use EXISTS over COUNT(*) > 0 for existence checks
• Limit result sets with pagination
• Use CTEs for readability but beware of optimization fences in older Postgres