Database Fundamentals for Architects

Architects who understand databases make better trade-offs. You do not need to become a database administrator, but you do need a solid model of how data is stored, queried and protected.

Learning goals

By the end of this track you will be able to do the following.

Explain how storage engines and indexes affect performance
Reason about transactions, isolation levels and anomalies
Choose between relational and non-relational stores with evidence
Design schemas that can evolve safely

The plan

Week 1: Storage and indexes

Learn how pages, B-trees and log-structured storage work. Create a table with a million rows, query it with and without an index and read the query plan to see the difference.

Week 2: Query planning

Study joins, statistics and cost-based optimization. Take three slow queries from a real system and explain each plan in your own words before attempting to tune them.

Week 3: Transactions

Study atomicity, durability and isolation levels. Reproduce a lost update and a write skew with two concurrent sessions, then fix each with the right isolation or locking approach.

Week 4: Modeling

Compare normalized models with denormalized ones and document stores. Model the same domain three ways and list the queries each makes easy or painful.

Week 5: Scale and replication

Learn about read replicas, sharding and the cost of cross-shard operations. Sketch how your current system would grow tenfold and where the database would hurt first.

Week 6: Operations and evolution

Practice online schema migrations, backups and restore drills. A backup you have never restored is only a hope, so schedule an actual restore test.

How to use this plan

Treat each step as a block of roughly four to six focused hours per week, which fits alongside normal work. Keep a running journal with three headings: what I learned, what surprised me and what I will try next. Review the journal at the end of every step before moving on. If a step takes longer, extend it rather than skipping ahead, because later steps assume the earlier ones. Find a study partner if you can, since explaining ideas aloud exposes gaps quickly.

Common pitfalls

Adding indexes for every query without measuring write cost
Choosing a data store because it is fashionable
Never rehearsing a restore from backup

Measuring progress

Check yourself against signals rather than feelings.

You read query plans comfortably
You can name the anomalies your isolation level permits
Your migrations run without downtime

Learning sticks when it leaves your head. At the end of each step, post a short summary in a team channel or internal wiki, covering what you tried, what worked and what did not. Offer a thirty minute walkthrough to colleagues once per month. Feedback from people with different backgrounds will correct misconceptions early, and your notes become a starting point for the next person who follows the same path. Revisit this plan every quarter and adjust it to your changing role.

Exercises

Explain to a teammate why an index can slow down writes.
Write a migration plan that adds a required column without downtime.
Choose a data store for three workloads and justify each choice.

Resources

Rather than pointing at specific links that may change, look for these kinds of material.

Documentation of one mainstream relational database, read cover to cover in parts
A book on data-intensive applications for the conceptual layer
Query plan visualization tools available for most databases

Key takeaways

Indexes and query plans explain most performance behavior
Isolation levels define which anomalies you accept
Model data around the queries you must serve
Test restores and migrations before you rely on them