Data Modeling, Storage, and Database Management
Data Modeling, Storage, and Database Management covers how you shape data, choose storage engines, and keep queries fast as volume grows. The exam tests schema design, index reasoning, transaction isolation, and the operational side: migrations, partitioning, and backups.
Modeling rules
- Normalize transactional data to third normal form; denormalize deliberately for read paths and document the refresh mechanism.
- Choose keys that are stable and narrow. A random UUID primary key is fine, but a random UUID clustering key fragments B-tree inserts.
- Every table needs an explicit primary key, and every foreign key needs an index on the referencing side.
- Store timestamps in UTC with a timezone-aware type; store money as integer minor units or decimal, never floating point.
Index reasoning
- Composite indexes follow the leftmost-prefix rule: equality columns first, then the range column, then the sort column.
- A covering index answers a query without touching the table.
- Wrapping an indexed column in a function, as in
WHERE date(created_at) = ..., disables the index unless a matching expression index exists.
Transactions and isolation
Read Committed (the PostgreSQL default) prevents dirty reads but permits lost updates; use optimistic concurrency with a version column or SELECT FOR UPDATE. Serializable is correct but expects the application to retry on serialization failure.
Worked example
Query: WHERE tenant_id = ? AND created_at >= ? ORDER BY created_at DESC LIMIT 50 on a 500M-row table. An index on (created_at, tenant_id) forces a scan across all tenants' recent rows. An index on (tenant_id, created_at DESC) seeks straight to the tenant and walks the range in order, reading 50 rows. When a single tenant outgrows a comfortable partition, partition by tenant hash and archive cold partitions rather than deleting row by row.
Traps
- Adding an index per query until write amplification dominates.
- SELECT * in hot paths, breaking covering indexes and pulling large columns.
- Assuming a document store means no schema; implicit schemas still break clients.
- Long-running transactions holding locks while a migration runs.
- Backups that were never restored in a test environment.