TodayILearned
Databases lessons
23 of 53 published lessons in this track - taught deeply, then shared publicly.
23 entries · 21 with video
Row-level security - tenant walls inside one table
GRANT leaks all tenants; RLS policies scope rows. Lab: open 100/100 vs RLS 40/60, cross INSERT 0 on til-postgres.
Optimistic concurrency - version columns beat blind RMW
Blind read-modify-write loses concurrent decrements; version WHERE keeps stock correct with retries. Lab: final 99 vs 92 on til-postgres.
LISTEN / NOTIFY - wake workers without polling the table
DB-native doorbell after COMMIT; not a durable queue. Lab: 30/30 commit deliver, 30/30 rollback silent, coalesce 30/30.
lock_timeout and NOWAIT - fail fast instead of waiting forever
Unbounded lock waits vs lock_timeout ceiling vs FOR UPDATE NOWAIT; lab medians 441 / 114 / 13.3 ms on til-postgres.
Advisory locks - a mutex that is not a row lock
Leader election without a job row. pg_try_advisory_xact_lock. Lab: 8 false leaders vs 1 winner 30/30 on Podman PG 18.
Isolation Levels - What Each Level Permits
RC permits non-repeatable reads (30/30); RR blocks them but write-skew 30/30; SSI blocks skew with serialization failures (retry).
SKIP LOCKED - Claim the Next Free Job
FOR UPDATE queues workers; SKIP LOCKED lets them claim different jobs. Lab: 40/0 vs ~18/22; 1.25× drain (1595→1271 ms).
Deadlocks - When Lock Waits Form a Cycle
Opposite lock order creates a wait-for cycle; PG aborts one victim. Lab: opposite 30/30 DL; ordered 0. Defense: sorted lock helper + 40P01 retry.
Deferred Constraints - Check at COMMIT
DEFERRABLE FKs let multi-statement txs temporarily break order; integrity still holds at COMMIT. Timing SVG + lab: cycle 1↔2 OK deferred; bulk child-first 3.467 vs parent-first 6.146 ms (0.564×).
Exclusion Constraints - No Two Rows May Conflict
EXCLUDE USING gist blocks multi-row conflicts (double-book). Lab PG 18.4: 2 rows/1 pair without; reject with; bulk 1.035×; range-race craft.
CHECK Constraints - Last Line of Defense
CHECK enforces domain predicates on every write. Lab PG 18.4: 2 bad rows without CHECK, 0 with; bulk tax 0.986× noise. Gate-slam craft.
WAL - Commit Hits Disk Before the Table Does
Write-ahead log: append, fsync, then COMMIT; heap later. Lab PG 18.4: durable 3587.703 ms vs async 2392.958 ms (1.50×) vs UNLOGGED 2440.399 ms (1.47×). Crash-timeline craft.
MVCC - How PostgreSQL Does Concurrency
UPDATE never overwrites a row. It creates a new version. Readers use snapshots. Dead versions pile up until VACUUM reclaims them - and restores Heap Fetches: 0.
Partial Indexes - When Less Is More
Index only the rows you actually query. A partial index uses a WHERE clause to index a subset of rows - smaller, faster, and sometimes the only way to make a unique constraint make sense.
Covering indexes - when the index is enough
An index finds rows fast. A covering index returns the data too - PostgreSQL skips the table entirely. Heap Fetches: 0 is the magic line in EXPLAIN.
Keyset Pagination - Why OFFSET Breaks at Scale
Your API returns page 500 in 50ms. Page 5000 takes half a second. Page 10000 takes two seconds. The user is just clicking 'next page' - so why does each page get slower? The answer is OFFSET, and the fix is a cursor.
JOINs - when two tables become one
Normalization split your data into honest tables. JOINs stitch it back together - at a cost. The planner picks the algorithm, but you control the indexes that make it fast.
Database normalization - 1NF, 2NF, 3NF
Three normal forms, three problems they solve. Normalization prevents anomalies; denormalization trades safety for speed.
Transactions and ACID - when all or nothing is the point
A connection pool gives you a connection. A transaction is what you do inside it - a unit of work that either fully succeeds or fully fails.
Connection pooling - sharing database connections
Every database connection is expensive. A pool keeps a few warm and reuses them - the load balancer's cousin, one layer deeper.
Composite indexes - the leftmost prefix rule
When you index multiple columns, order matters. Here's why - with a real benchmark.
Reading EXPLAIN output
How to see what the database is actually doing - and whether your index is being used.
Why does an index speed up a query?
And the part nobody mentions: when it actually makes things worse.