SQL — Staff · Resources

1Data modelling & schema design

0/5▶

Staff-level data work is about schemas that scale and evolve, and choosing the right store per access pattern.

Model for the long run

Normalization (1NF–3NF) vs deliberate denormalization; when each wins.

Database normalization.

Choosing a store per access pattern: relational, KV, document, wide-column, search, time-series.

Designing Data-Intensive Applications — ch. 3.

OLTP vs OLAP; star/snowflake schemas; when to split analytics into a warehouse.

Star schema.

Modelling for evolution: additive changes, nullable columns, backward-compatible schemas.

Stripe — Online migrations.

Surrogate vs natural keys; UUID vs auto-increment tradeoffs at scale.

UUID vs serial keys.

2Scaling the database

0/5▶

When one box isn't enough. Know the options and, crucially, the pain each introduces.

Scale reads and writes

Read replicas & replication lag; read-your-writes concerns.

System Design Primer — Replication.

Partitioning / sharding: hash vs range vs directory; consistent hashing; resharding.

Consistent hashing.

Hot partitions / hot keys — detection and mitigation.

ByteByteGo — search "hot partition".

Caching in front of the DB; cache-aside; invalidation & stampede protection.

AWS — Caching.

Connection pooling & why too many connections kill a DB.

PgBouncer.

3Distributed data & pipelines

0/5▶

Staff engineers reason about data across systems: consistency, capture, and analytics pipelines.

Data across systems

Distributed transactions & the saga pattern; why 2PC is usually avoided.

microservices.io — Saga.

Change Data Capture (CDC) & the outbox pattern to sync DB → stream.

microservices.io — Outbox.

Batch vs streaming; a warehouse/lake for analytics (ETL/ELT).

DDIA — ch. 10–11.

Idempotent writes & exactly-once effects across services.

Stripe — Idempotency.

Eventual consistency & reconciliation jobs.

DDIA — ch. 5.

4Operations & migrations

0/4▶

You own the database's reliability and safe evolution across the org.

Run and evolve it

Zero-downtime schema migrations: expand → backfill → dual-write → switch → contract.

Stripe — Online migrations.

Backups, PITR, and DR: RTO/RPO and failover drills.

AWS Builders' Library.

Query governance: slow-query monitoring, statement timeouts, noisy-neighbour limits.

Use The Index, Luke.

Data retention, archival, and compliance (PII, right-to-erasure).

GDPR — Right to erasure.