Skip to content
EL

Database Engineering

How data is stored, found, kept correct under concurrency, and run in production.

1 · Foundation
Assumes no database knowledge
2 · Core
Assumes you write SQL
3 · Advanced
How the engine works inside
4 · Expert
Running and designing it under real load and failure

Data and storage basics

Explain what a database does that files do not, show it with your own experiments, and choose a kind of database for a workload.

  1. 01What data is, and why we need a databaseFoundation

    Explain why a program's state needs more than memory and files, and name what a database system adds.

  2. 02Plain files, CSV, JSON and the cost of DIY storageFoundation

    Build a file-based store, measure how it slows down and breaks, and fix the failures you can, so you know exactly what a database is saving you from.

    Helps to know first: What data is, and why we need a database

  3. 03Database types and how to choose oneFoundation · coming soon

Relational thinking and SQL fundamentals

Model data as related tables and write correct SQL to read and change it, including the NULL, join and grouping cases that trip people up.

  1. 04Relational database conceptsFoundation

    Read a relational schema, explain what its keys guarantee, and spot designs that will corrupt or hide data.

    Helps to know first: What data is, and why we need a database

  2. 05Install PostgreSQL and learn the environmentFoundation

    Run PostgreSQL locally, connect with psql, load the practice database, and know what you are connected to.

    Helps to know first: Relational database concepts

  3. 06SQL basics: reading dataFoundation

    Write SELECT queries that filter, sort and limit correctly, explain the order a query is evaluated in, and predict what NULL does to a condition.

    Helps to know first: Install PostgreSQL and learn the environment

  4. 07SQL data modificationFoundation

    Insert, update, delete and upsert rows, see exactly which rows changed, and make every data change safe to check before it is kept.

    Helps to know first: SQL basics: reading data

  5. 08SQL types and the type systemFoundation

    Choose column types that store values exactly as the business means them, especially money, text and time, and predict where the wrong type silently changes data.

    Helps to know first: SQL data modification

  6. 09Constraints and data integrityFoundation

    Turn every rule your data must obey into a constraint the database enforces, and predict how each one treats NULL and deletes.

    Helps to know first: SQL types and the type system

  7. 10Joins and relational queryingFoundation

    Combine tables with the right kind of join, keep or drop unmatched rows on purpose, and recognise when a join is multiplying rows.

    Helps to know first: SQL basics: reading data, Relational database concepts

  8. 11Aggregation and groupingFoundation

    Summarise data with aggregates and GROUP BY, filter groups with HAVING, and catch the NULL and fan-out cases that make totals wrong.

    Helps to know first: Joins and relational querying

  9. 12Subqueries and query compositionFoundation

    Build answers from smaller queries (scalar, list, correlated and derived-table subqueries) and avoid the NOT IN trap that silently returns nothing.

    Helps to know first: Aggregation and grouping

Data modeling and advanced SQL

  1. 13Normalization and schema designCore · coming soon
  2. 14Common table expressions (CTEs)Core · coming soon
  3. 15Set operations and advanced expressionsCore · coming soon
  4. 16Window functionsCore · coming soon
  5. 17Pagination and large result setsCore · coming soon
  6. 18Views, materialized views and derived dataCore · coming soon
  7. 19Functions, procedures and triggersCore · coming soon
  8. 20JSON and semi-structured data in PostgreSQLCore · coming soon

How Postgres stores and finds data

Explain what happens on disk when a row is written or found, and predict what an index will cost and buy.

  1. 21How a table is stored on diskAdvanced · coming soon
  2. 22Buffer cache, I/O and memoryAdvanced · coming soon
  3. 23Write-ahead loggingAdvanced · coming soon
  4. 24B-tree indexesAdvanced

    Predict when a B-tree index helps, when PostgreSQL ignores it, and what it costs on every write.

    Helps to know first: How a table is stored on disk

  5. 25PostgreSQL index typesAdvanced · coming soon
  6. 26Composite indexes and index designAdvanced · coming soon
  7. 27Heap access, the visibility map and MVCC storageAdvanced · coming soon

Query planning and performance

  1. 28How PostgreSQL executes a queryAdvanced · coming soon
  2. 29EXPLAIN and EXPLAIN ANALYZEAdvanced · coming soon
  3. 30Query optimization patternsAdvanced · coming soon
  4. 31Statistics and the query plannerAdvanced · coming soon
  5. 32Benchmarking and performance measurementAdvanced · coming soon
  6. 33Production observability and query diagnosisAdvanced · coming soon

Transactions, MVCC and concurrency

  1. 34Transactions and ACIDAdvanced · coming soon
  2. 35MVCC: multi-version concurrency controlAdvanced · coming soon
  3. 36Isolation levels and anomaliesAdvanced · coming soon
  4. 37Locks and locking strategiesAdvanced · coming soon
  5. 38Race conditions and correct state transitionsAdvanced · coming soon
  6. 39Deadlocks, timeouts and failure recoveryAdvanced · coming soon

Running Postgres in production

  1. 40Database migrations and schema evolutionExpert · coming soon
  2. 41Vacuum, autovacuum and bloatExpert · coming soon
  3. 42PartitioningExpert · coming soon
  4. 43Advanced data types and specialized queryingExpert · coming soon
  5. 44PostgreSQL security and multi-tenancyExpert · coming soon
  6. 45Backups, restore and point-in-time recoveryExpert · coming soon
  7. 46Replication, high availability and failoverExpert · coming soon
  8. 47Connection management and poolingExpert · coming soon

Databases inside backend systems

  1. 48Database integration in backend applicationsExpert · coming soon
  2. 49Idempotency, retries and exactly-once effectsExpert · coming soon
  3. 50Background jobs, queues and work distributionExpert · coming soon
  4. 51Caching and derived stateExpert · coming soon
  5. 52Multi-tenant data architectureExpert · coming soon

Outgrowing one database

  1. 53Database bottlenecks and capacity planningExpert · coming soon
  2. 54Read scaling and write scalingExpert · coming soon
  3. 55Replication and consistency modelsExpert · coming soon
  4. 56Sharding and partitioning across machinesExpert · coming soon
  5. 57Distributed transactions and coordinationExpert · coming soon
  6. 58Consensus, leader election and distributed coordinationExpert · coming soon
  7. 59Event sourcing, CQRS and change data captureExpert · coming soon
  8. 60Distributed data system designExpert · coming soon

Postgres internals, deeper

  1. 61PostgreSQL architecture and source codeExpert · coming soon
  2. 62WAL, crash recovery and durability internalsExpert · coming soon
  3. 63PostgreSQL concurrency internalsExpert · coming soon
  4. 64Extensions and the PostgreSQL ecosystemExpert · coming soon

Other databases, compared to Postgres

  1. 65SQLite and embedded databasesExpert · coming soon
  2. 66MySQL and MariaDBExpert · coming soon
  3. 67MongoDB and document databasesExpert · coming soon
  4. 68Redis and key-value databasesExpert · coming soon
  5. 69ScyllaDB and Cassandra: wide-column databasesExpert · coming soon
  6. 70Graph databases and graph queryingExpert · coming soon
  7. 71Distributed SQL databasesExpert · coming soon
  8. 72Analytical databases and data warehousesExpert · coming soon
  9. 73Search engines and vector databasesExpert · coming soon

Reliability and senior judgment

  1. 74Database reliability engineeringExpert · coming soon
  2. 75Secure database engineeringExpert · coming soon
  3. 76Database migrations at scaleExpert · coming soon
  4. 77Database testing and correctnessExpert · coming soon
  5. 78Design reviews and trade-off analysisExpert · coming soon