Database Engineering

Sub-documents

1. Choosing the Right Database — Relational, document, key-value, column-family, graph, and search databases. Decision framework for when to use each.
2. Schema Design & Migrations — Normalization, denormalization, constraints, migration tools in Rust, and zero-downtime migration patterns.
3. SQL Deep Dive — Joins, window functions, CTEs, subqueries, and advanced SQL techniques with practical examples.
4. Query Optimization — EXPLAIN ANALYZE, indexing strategies (B-tree, GIN, partial, covering), the N+1 problem, and connection pooling.
5. NoSQL Data Modeling — DynamoDB single-table design, Redis patterns (caching, pub/sub, sorted sets), MongoDB document modeling. Discord's migration from Mongo to Cassandra to ScyllaDB.
6. ORMs vs Raw SQL — sqlx, diesel, and sea-orm compared with identical queries. Trade-offs, N+1 problem, and when to use which.

Diagrams

Key Takeaways

1. PostgreSQL is the right default for most applications. It handles relational data, JSON, full-text search, and scales further than most companies need.
2. Design schemas for your read patterns. Write patterns are usually simpler than read patterns.
3. Always use database migrations. Manual schema changes in production are a recipe for disaster.
4. Index foreign keys and columns used in WHERE/JOIN/ORDER BY clauses. A missing index is the most common performance problem.
5. Use EXPLAIN ANALYZE to understand query performance. Don't guess — measure.
6. Connection pooling is mandatory for production. Size the pool based on database capacity, not application demand.
7. Choose between ORM and raw SQL based on your team and complexity. sqlx's compile-time checking gives you the best of both worlds in Rust.
8. Model NoSQL tables around access patterns, not entities. List every query before designing the schema.
9. Use Redis as a cache and coordination layer, never as a primary database.

Further Reading

• Books:

  • Designing Data-Intensive Applications — Martin Kleppmann (2017) — The definitive guide to database concepts and trade-offs
  • PostgreSQL: Up and Running — Regina Obe & Leo Hsu (3rd edition) — Practical PostgreSQL guide
  • The Art of PostgreSQL — Dimitri Fontaine (2020) — Advanced SQL techniques

• Papers & Articles:

  • Use The Index, Luke — Comprehensive guide to database indexing
  • Figma's PostgreSQL Scaling — Real-world PostgreSQL scaling
  • Discord's Database Migrations — Migration journey through three databases

• Crates:

  • sqlx — Async SQL with compile-time checking
  • diesel — Type-safe ORM and query builder
  • sea-orm — Async ORM built on sqlx
  • deadpool-postgres — Connection pooling