Ultimate Database Engineering

Engine-agnostic principles first. For engine-specific patterns, indexes, and operational details see the reference/ files.

Reference files

Read the relevant reference file before making engine-specific decisions. Always check docs for the exact version in use.

Universal principles

These apply regardless of engine, paradigm, or scale:

1. Design for access patterns. Model data around the queries you run, not idealized normal form.
2. Indexes are the primary performance lever. A missing index on a large table turns milliseconds into seconds.
3. Measure before changing. Use EXPLAIN ANALYZE (SQL), .explain("executionStats") (MongoDB), or the engine equivalent before adding indexes or rewriting queries.
4. Smallest correct type. Over-provisioned types waste cache, IO, and index space.
5. Short transactions. Long-running transactions block vacuuming, lock acquisition, and replication everywhere.
6. Parameterized queries always. No string interpolation into SQL or query builders.
7. Mutations are expensive in append-optimized engines. In ClickHouse and similar, model to avoid UPDATE/DELETE; prefer reinsert or versioned rows.
8. Enforce invariants at the database layer. Constraints, domains, and check constraints catch bugs that application code misses.

Index decision guide

Follow ESR order for compound indexes (applies to SQL and MongoDB alike):

1. Equality predicates -- most selective first.
2. Sort columns -- eliminates in-memory sort.
3. Range predicates -- last.

-- Query: WHERE status = 'active' AND created_at > $1 ORDER BY priority DESC
CREATE INDEX idx_tasks ON tasks (status, priority DESC, created_at);

Query anti-patterns (universal)

Schema design checklist

• Timestamps store timezone (TIMESTAMPTZ, DateTime64('UTC'), ISO 8601).
• Monetary values use exact decimal type (NUMERIC, DECIMAL, Decimal128).
• Enumerations use a native enum type or a CHECK constraint, not bare strings.
• Foreign keys declared and indexed.
• Partitioning chosen for lifecycle (DROP PARTITION), not as index substitute.
• Zero-downtime migration path planned: additive first, backfill, then constrain.

Replication and HA summary

For configuration details, failover procedures, and monitoring queries see the relevant reference file.