Shelf · SQL

SQL & Databases

Postgres the way production uses it: joins, indexes, EXPLAIN, transactions and isolation.

9 chapters · 54 cards · 0 chapters written · #7 in your reading order

1

Relational thinking & schema design

Planned
Difficulty
Priority
  1. Choose stable row identities and keysEasyP1Know cold
  2. Model relationships with explicit cardinalityEasyP1Know cold
  3. Remove repeating groups and partial dependenciesMediumP2Know cold
  4. Reach 3NF and justify denormalisationMediumP2Talk about it
  5. Make constraints executable documentationEasyP1Know cold
  6. Turn a business paragraph into a schemaMediumP2Know cold
2

Query mechanics

Planned
Difficulty
Priority
  1. Explain logical order without dictating a planEasyP2Know cold
  2. Predict cardinality across all join typesMediumP1Know cold
  3. Reason with NULL and three-valued predicatesMediumP1Know cold
  4. Keep or remove duplicates deliberatelyEasyP2Know cold
  5. Choose subqueries or readable CTE boundariesMediumP2Know cold
  6. Traverse a hierarchy with recursive CTEsHardP4Talk about it
3

Windows & analytical SQL

Planned
Difficulty
Priority
  1. Aggregate at the intended grainEasyP1Know cold
  2. Rank rows with an explicit tie policyMediumP3Know cold
  3. Compare neighboring events with LAG and LEADMediumP3Know cold
  4. Control frames and running totalsHardP3Talk about it
  5. Dedupe with deterministic business rulesMediumP2Know cold
  6. Segment streaks and reshape reportsHardP3Talk about it
4

Indexing & the planner

Planned
Difficulty
Priority
  1. Indexes and query plans: planner trade-offsMediumP2Know cold
  2. Match GIN and BRIN to data shapeMediumP3Talk about it
  3. Design composite index column orderHardP2Know cold
  4. Evaluate covering indexes and heap fetchesMediumP3Talk about it
  5. Use partial indexes only for provable predicatesHardP3Talk about it
  6. Diagnose why an index is not usedHardP2Know cold
5

Transactions, isolation & concurrency

Planned
Difficulty
Priority
  1. Transactions and MVCC: ACID and visibilityMediumP1Know cold
  2. Map four isolation levels to actual anomaliesHardP3Talk about it
  3. Protect row decisions with pessimistic locksMediumP3Know cold
  4. Detect stale writes with optimistic lockingMediumP3Talk about it
  5. Idempotent database writes and upsertsHardP2Know cold
  6. Bound deadlocks and long transactionsHardP2Talk about it
6

Postgres in production

Planned
Difficulty
Priority
  1. Maintain vacuum health before bloat becomes an incidentHardP3Talk about it
  2. Pool connections without leaking session assumptionsMediumP2Talk about it
  3. Partition tables for bounded operational workHardP3Talk about it
  4. Keep JSONB and extensions within a schema boundaryMediumP3Talk about it
  5. Separate replication failover from recoverable backupsHardP3Talk about it
  6. Safe database migrations: expand-contractHardP2Talk about it
7

Data modelling for pipelines & warehouses

Planned
Difficulty
Priority
  1. Warehouse modelling: OLTP/OLAP and star/snowflakeMediumP3Talk about it
  2. Keep fact grain and dimension identity consistentMediumP2Know cold
  3. Choose SCD behavior before loading historyHardP3Talk about it
  4. Incremental extraction: watermarks and CDCHardP3Talk about it
  5. Make destination loads idempotent and replayableHardP2Know cold
  6. Separate staging, core and mart responsibilitiesMediumP3Talk about it
8

Beyond relational

Planned
Difficulty
Priority
  1. Match KV, document, wide-column and vector modelsEasyP3Talk about it
  2. DynamoDB keys: access-pattern-first single-table modellingHardP3Talk about it
  3. Audit an order access-pattern matrixMediumP3Talk about it
  4. Apply Redis patterns to a SQL-backed read pathMediumP2Talk about it
  5. Decide whether similarity search needs another storeMediumP4Know it exists
  6. Explain when relational still winsEasyP2Talk about it
9

SQL performance war stories & drills

Planned
Difficulty
Priority
  1. Diagnose an N+1 request from query countsMediumP2Know cold
  2. Replace row-by-row work with set-based operationsMediumP2Know cold
  3. Choose restartable chunks rather than arbitrary batchesHardP3Talk about it
  4. Load with COPY and keep transaction semantics explicitMediumP3Know cold
  5. Rewrite queries under explicit timeout budgetsHardP2Know cold
  6. Run timed SQL interview drills with a correctness rubricMediumP5Know it exists