Topic · 24 lessons in 8 series
Databases and SQL
Choosing a database, designing a schema, indexes, transactions and replication, plus the SQL and PostgreSQL series, gathered into one reading list.
DevOpsWhole series →
System DesignWhole series →
- SQL Basics Every System Designer NeedsBefore sharding, replicas or NoSQL, learn to use one relational database well. Tables and keys, the queries you will write every day, indexes and EXPLAIN, the N+1 trap, transactions, and the connection limit that takes databases down.10 min
- SQL vs NoSQL: Picking the Right Data Model“SQL or NoSQL?” is the wrong question; the right one is “what shape is my data, and how will I read and write it?” The relational model and the four NoSQL families — key-value, document, wide-column and graph — what each is good at, the myths to drop, and a sensible default.12 min
- ACID Transactions & Isolation LevelsTwo people book the last seat; a transfer crashes halfway. What ACID really promises, the write-ahead log behind durability, the five anomalies isolation levels do (and don't) prevent, and the practical fixes for real race conditions.12 min
- Database Indexing: B-Trees and LSM-TreesWhy an index turns 812 ms into 0.09 ms, and why it slows every write. How B-trees keep sorted pages updated in place, how LSM-trees append and compact for huge write rates, and how to design indexes from real queries.12 min
- Database Replication: Leader, Follower and BeyondOne database server will eventually die, drown in reads, or be too far from your users. How leader-follower replication works, what replication lag does to real users, why failover is so hard, and how multi-leader and quorum systems handle conflicts.12 min
- How to Choose a Database for Your SystemHundreds of databases all claim to be fast and scalable. A practical seven-step process: the questions to ask, a sensible default, the signals that justify something else, prototyping with real load, and migrating safely when you got it wrong.8 min
Backend in DepthWhole series →
- Database Fundamentals for API Engineers: Indexes, Transactions, IsolationB-tree indexes, reading EXPLAIN ANALYZE, transactions and isolation levels, and the race conditions they stop, like selling too many items.20 min
- Prisma in Production: N+1, include, Batching and Connection PoolsHow N+1 appears in loops and GraphQL resolvers, how Prisma batches findUnique, how it loads relations, and why you share a single client.18 min
Practical Design ProblemsWhole series →
Full-Stack QuestionsWhole series →
- MongoDB Interview QuestionsSchema design, indexes, aggregation, transactions, replica sets, sharding and common anti-patterns.31 min
- Prisma & SQL Interview QuestionsPrisma 7, migrations, relations, transactions, N+1, raw queries, and the SQL behind it.30 min
- Full-Stack Performance: From Database to BrowserFind the bottleneck first, then fix the database, caching, compression, streaming and bundle size, and measure each change with p50, p95 and p99.42 min
- Vector Databases, pgvector, Retrieval Evals and LLM CostDistance measures, HNSW and IVFFlat, the filter trap, hybrid search, golden sets, judges, prompt caching and tracing.40 min
SQL & PostgreSQLWhole series →
- Tables, SELECT and WHEREWhat a relational database is, running PostgreSQL, a small shop schema, SELECT, WHERE, NULL logic, and INSERT, UPDATE and DELETE with RETURNING.18 min
- Joins: Combining TablesPrimary and foreign keys, inner, left, right, full and cross joins with drawn result tables, self joins and the NULL trap in left joins.18 min
- Aggregates, GROUP BY and HAVINGCOUNT, SUM and AVG, GROUP BY rules, HAVING versus WHERE, FILTER, date_trunc reports and the order a SELECT is evaluated.20 min
- Subqueries, CTEs and Window FunctionsScalar, IN and EXISTS subqueries, WITH queries, a recursive category tree, ranking, LAG and LEAD, running totals and top-N per group.22 min
- Designing a Schema: Keys, Normalisation and ConstraintsIdentity versus UUID keys, normal forms with a flat-to-normalised redesign, ON DELETE, enums, money, timestamptz and soft deletes.22 min
- Indexes and EXPLAINHow a B-tree helps, composite, covering and partial indexes, and reading real EXPLAIN ANALYZE plans on a million rows.24 min
- Transactions, Isolation Levels and LocksAtomicity, read committed versus repeatable read versus serializable with two real sessions, row locks, SKIP LOCKED, deadlocks and retries.22 min
- PostgreSQL Features: JSONB, Full-Text Search, Arrays and Safe MigrationsJSONB with GIN indexes, full-text search, arrays, upserts, and expand-and-contract migrations that do not lock a busy table.24 min