Databases in depth

Tables, keys and SQL.

1. Interactive

   Coming soon

   Tables, keys and relations

   Primary keys, foreign keys and why data is split into tables.

2. Interactive

   Coming soon

   How joins work

   Inner, left and full joins, row by row.

3. Interactive

   Coming soon

   GROUP BY and aggregation

   Collapse rows into groups and summarize them.

4. Interactive

   Live

   Window functions

   Running totals and rankings without collapsing rows.

   Open session →

5. Interactive

   Coming soon

   Normal forms

   Remove duplicate data step by step, and when to stop.

From SQL text to rows.

1. Interactive

   Coming soon

   Parse, plan, execute

   What the database does with your SQL string.

2. Interactive

   Coming soon

   Reading EXPLAIN

   Scans, joins, costs and the row estimates that drive them.

3. Interactive

   Live

   Nested loop, hash and merge joins

   Three ways to join tables, and when each wins.

   Open session →

4. Interactive

   Coming soon

   Statistics and bad plans

   Why the planner guesses wrong, and how to fix it.

5. Interactive

   Coming soon

   Query tuning in practice

   Rewrite, index or restructure: a slow query, fixed step by step.

How data sits on disk and how it's found.

1. Interactive

   Coming soon

   Pages, rows and the heap

   The 8 KB pages a table is really made of.

2. Interactive

   Live

   B-tree indexes

   How an index finds one row among millions.

   Open session →

3. Interactive

   Live

   Composite and covering indexes

   Column order, and answering a query from the index alone.

   Open session →

4. Interactive

   Coming soon

   Hash, GIN and GiST indexes

   Indexes for equality, arrays, full text and geometry.

5. Interactive

   Live

   LSM trees

   Fast writes with memtables, SSTables and compaction.

   Open session →

6. Interactive

   Coming soon

   Row vs columnar storage

   Why analytics databases store columns together.

Many users, one consistent database.

1. Interactive

   Coming soon

   ACID in practice

   What each letter actually guarantees.

2. Interactive

   Live

   The lost update

   Two deposits at once and one disappears: four ways to stop it.

   Open session →

3. Interactive

   Live

   MVCC

   Readers don't block writers: row versions and snapshots.

   Open session →

4. Interactive

   Coming soon

   Row locks and lock waits

   Who waits for whom, and SELECT FOR UPDATE.

5. Interactive

   Coming soon

   Deadlocks

   Two transactions waiting on each other, and how the database breaks the tie.

6. Interactive

   Coming soon

   Optimistic vs pessimistic locking

   Lock first, or check for conflicts at the end.

Not losing committed data.

1. Interactive

   Live

   The write-ahead log

   Write the change to the log first, then to the table.

   Open session →

2. Interactive

   Coming soon

   Checkpoints and crash recovery

   Replay the log after a crash.

3. Interactive

   Coming soon

   Backups and point-in-time recovery

   Restore the database to 10:42 this morning.

4. Interactive

   Coming soon

   Vacuum and bloat

   Cleaning up dead row versions in Postgres.

When one database isn't enough.

Already live in other tracks: Replication and stale reads, Sharding: choosing the shard key

1. Interactive

   Live

   Connection pooling

   Why 1,000 app servers can't each open 100 connections.

   Open session →

2. Interactive

   Live

   Read replicas and lag

   Spread reads, and handle data that's a few seconds old.

   Open session →

3. Interactive

   Live

   Table partitioning

   Split one huge table by date or key.

   Open session →

4. Interactive

   Coming soon

   Caching in front of the database

   Redis as a cache, and keeping it in sync.

Other ways to store data.

1. Interactive

   Coming soon

   Key-value stores: inside Redis

   Data structures, persistence and single-threaded speed.

2. Interactive

   Coming soon

   Document databases

   Store whole objects, query by fields, and model relations.

3. Interactive

   Coming soon

   Wide-column stores

   Cassandra-style tables built for huge write volumes.

4. Interactive

   Coming soon

   Graph databases

   Query relationships directly: friends of friends.

5. Interactive

   Coming soon

   Inverted indexes for search

   How full-text search finds words fast.

6. Interactive

   Coming soon

   Vector indexes: HNSW

   Approximate nearest-neighbour search through a layered graph.

Living with a database in production.

1. Interactive

   Coming soon

   Schema migrations

   Change tables safely while the app is running.

2. Interactive

   Coming soon

   Finding slow queries

   Slow query logs, pg_stat_statements and where to look first.

3. Interactive

   Coming soon

   ORMs and N+1 queries

   What your ORM sends to the database.

4. Interactive

   Coming soon

   Database security

   Roles, row-level security and keeping credentials safe.