Postgres Patterns We Reuse Across Client Projects

Postgres remains our default for transactional products because it is predictable, observable, and well understood. We standardize migration tooling and naming so any engineer can onboard in an afternoon.

We avoid exotic data stores unless the specific use case demands it. For 95% of applications, a properly tuned Postgres instance is more than enough.

Indexes are not set-and-forget. We capture slow queries in staging, explain plans before shipping, and avoid premature JSONB unless access patterns truly need it.

• Composite indexes for filter + sort patterns that appear together
• Partial indexes for sparse booleans like is_archived = false
• Avoid indexing low-cardinality columns in isolation
• Revisit indexes after every tenfold traffic jump

Row-level security and tenancy boundaries are designed up front for multi-tenant SaaS. Retrofitting isolation after launch is painful and risky.

1. 01tenant_id on every tenant-scoped table — no exceptions
2. 02RLS policies tested with integration tests, not hope
3. 03Background jobs always set session context before queries

API layers never leak raw SQL shapes to clients. DTOs and validation at the edge keep contracts stable while schemas evolve underneath.

By decoupling the database schema from the API response, we can optimize queries without breaking mobile apps or public API consumers.