PostgreSQL + Drizzle Performance Rules for Production SaaS

Production-ready PostgreSQL and Drizzle ORM performance rules for scalable SaaS applications.

Database Query Performance

• Always use indexed columns in WHERE clauses.
• Prefer composite indexes when filtering by multiple frequently used columns.
• Avoid SELECT * in production queries. Explicitly select only required columns.
• Use EXPLAIN ANALYZE before deploying complex queries.
• Never run unbounded queries in API endpoints.

Pagination & Large Data Handling

• Use LIMIT and OFFSET carefully.
• Prefer keyset pagination for large datasets.
• Avoid loading large datasets into memory.
• Always paginate API responses returning collections.

Indexing Strategy

• Use partial indexes for frequently filtered subsets (e.g., active records).
• Create GIN indexes for full-text search (tsvector).
• Monitor index bloat and reindex periodically.
• Avoid over-indexing rarely queried columns.

Drizzle ORM Best Practices

• Avoid N+1 query patterns.
• Prefer server-side filtering over client-side filtering.
• Use transactions for multi-step writes.
• Keep ORM queries predictable and optimized.
• Avoid dynamic raw SQL unless necessary and always sanitize inputs.

Full-Text Search Optimization

• Use tsvector with weighted fields.
• Rank results using ts_rank.
• Create a GIN index on the search vector.
• Normalize search queries using plainto_tsquery.

Data Modeling

• Use appropriate data types (UUID instead of text for IDs when possible).
• Avoid excessive JSONB nesting.
• Keep schema normalized unless denormalization is justified.
• Use foreign key constraints for relational integrity.

Production Safety

• Use connection pooling in production environments.
• Avoid blocking queries inside request lifecycles.
• Implement caching for frequently accessed rule lists.
• Move heavy data processing to background jobs.
• Monitor slow queries using PostgreSQL logs.

Observability & Monitoring

• Enable query logging in staging environments.
• Track performance regressions after migrations.
• Audit long-running transactions.
• Monitor connection pool exhaustion.