Shelf · SQL
SQL & Databases
Postgres the way production uses it: joins, indexes, EXPLAIN, transactions and isolation.
1
Relational thinking & schema design
PlannedDifficulty
Priority
- Choose stable row identities and keysEasyP1Know cold
- Model relationships with explicit cardinalityEasyP1Know cold
- Remove repeating groups and partial dependenciesMediumP2Know cold
- Reach 3NF and justify denormalisationMediumP2Talk about it
- Make constraints executable documentationEasyP1Know cold
- Turn a business paragraph into a schemaMediumP2Know cold
2
Query mechanics
PlannedDifficulty
Priority
- Explain logical order without dictating a planEasyP2Know cold
- Predict cardinality across all join typesMediumP1Know cold
- Reason with NULL and three-valued predicatesMediumP1Know cold
- Keep or remove duplicates deliberatelyEasyP2Know cold
- Choose subqueries or readable CTE boundariesMediumP2Know cold
- Traverse a hierarchy with recursive CTEsHardP4Talk about it
3
Windows & analytical SQL
PlannedDifficulty
Priority
- Aggregate at the intended grainEasyP1Know cold
- Rank rows with an explicit tie policyMediumP3Know cold
- Compare neighboring events with LAG and LEADMediumP3Know cold
- Control frames and running totalsHardP3Talk about it
- Dedupe with deterministic business rulesMediumP2Know cold
- Segment streaks and reshape reportsHardP3Talk about it
4
Indexing & the planner
PlannedDifficulty
Priority
- Indexes and query plans: planner trade-offsMediumP2Know cold
- Match GIN and BRIN to data shapeMediumP3Talk about it
- Design composite index column orderHardP2Know cold
- Evaluate covering indexes and heap fetchesMediumP3Talk about it
- Use partial indexes only for provable predicatesHardP3Talk about it
- Diagnose why an index is not usedHardP2Know cold
5
Transactions, isolation & concurrency
PlannedDifficulty
Priority
- Transactions and MVCC: ACID and visibilityMediumP1Know cold
- Map four isolation levels to actual anomaliesHardP3Talk about it
- Protect row decisions with pessimistic locksMediumP3Know cold
- Detect stale writes with optimistic lockingMediumP3Talk about it
- Idempotent database writes and upsertsHardP2Know cold
- Bound deadlocks and long transactionsHardP2Talk about it
6
Postgres in production
PlannedDifficulty
Priority
- Maintain vacuum health before bloat becomes an incidentHardP3Talk about it
- Pool connections without leaking session assumptionsMediumP2Talk about it
- Partition tables for bounded operational workHardP3Talk about it
- Keep JSONB and extensions within a schema boundaryMediumP3Talk about it
- Separate replication failover from recoverable backupsHardP3Talk about it
- Safe database migrations: expand-contractHardP2Talk about it
7
Data modelling for pipelines & warehouses
PlannedDifficulty
Priority
- Warehouse modelling: OLTP/OLAP and star/snowflakeMediumP3Talk about it
- Keep fact grain and dimension identity consistentMediumP2Know cold
- Choose SCD behavior before loading historyHardP3Talk about it
- Incremental extraction: watermarks and CDCHardP3Talk about it
- Make destination loads idempotent and replayableHardP2Know cold
- Separate staging, core and mart responsibilitiesMediumP3Talk about it
8
Beyond relational
PlannedDifficulty
Priority
- Match KV, document, wide-column and vector modelsEasyP3Talk about it
- DynamoDB keys: access-pattern-first single-table modellingHardP3Talk about it
- Audit an order access-pattern matrixMediumP3Talk about it
- Apply Redis patterns to a SQL-backed read pathMediumP2Talk about it
- Decide whether similarity search needs another storeMediumP4Know it exists
- Explain when relational still winsEasyP2Talk about it
9
SQL performance war stories & drills
PlannedDifficulty
Priority
- Diagnose an N+1 request from query countsMediumP2Know cold
- Replace row-by-row work with set-based operationsMediumP2Know cold
- Choose restartable chunks rather than arbitrary batchesHardP3Talk about it
- Load with COPY and keep transaction semantics explicitMediumP3Know cold
- Rewrite queries under explicit timeout budgetsHardP2Know cold
- Run timed SQL interview drills with a correctness rubricMediumP5Know it exists
No cards match these filters.