Skip to content

sql-postgres

SQL & PostgreSQL, deep

The SQL language and Postgres internals for senior engineers — joins, windows, CTEs, transactions, the planner.

10 units·47 lessons·~12 h

Start track →

00

Start from zero

Before the deep material: what SQL is and how Postgres executes a query.

01 Why SQL still runs everything 12 min 02 How Postgres executes a query 14 min

01

SQL foundations

The mental model: SQL describes sets, not steps.

01 The SELECT mental model: logical processing order 14 min 02 Filtering rows: WHERE, precedence, and sargability 15 min 03 NULL and three-valued logic 15 min 04 DISTINCT, DISTINCT ON, and set operations 15 min 05 Sorting and limiting: ORDER BY, LIMIT, and keyset pagination 16 min

02

Joins, deeply

Every join is a filtered Cartesian product — see that and outer/semi/anti/lateral follow.

01 A join is a filtered product 14 min 02 Inner vs outer joins 15 min 03 Semi and anti joins 15 min 04 Self and cross joins 14 min 05 LATERAL joins 15 min 06 Join traps and row explosions 16 min

03

Aggregation & grouping

GROUP BY collapses rows into buckets; everything in SELECT must be per-bucket.

01 The GROUP BY model: rows into buckets 14 min 02 HAVING vs WHERE: filter before or after grouping 14 min 03 Aggregate functions: COUNT, SUM, FILTER, and the traps 15 min 04 GROUPING SETS, ROLLUP, CUBE: many groupings, one pass 15 min 05 Conditional aggregation: pivot long rows into wide columns 15 min

04

Window functions

Windows aggregate without collapsing rows — the senior SQL superpower.

01 Windows vs GROUP BY 14 min 02 PARTITION BY and ORDER BY 15 min 03 Window frames: ROWS vs RANGE vs GROUPS 16 min 04 Ranking functions and top-N-per-group 15 min 05 Running totals, moving averages, LAG/LEAD 16 min 06 Real-world window patterns: gaps, islands, sessions 16 min

05

CTEs & recursion

WITH names a subquery; WITH RECURSIVE walks trees and graphs in pure SQL.

01 CTE basics: naming a subquery 13 min 02 CTE vs subquery vs view 14 min 03 CTE materialization: the fence that moved 15 min 04 Recursive CTEs: anchor, iterate, stop 15 min 05 Walking graphs in SQL 16 min

06

Types & modeling

Postgres has a rich type system — model with it, not around it.

01 Core types: money, text, time, identity 15 min 02 Constraints: integrity the engine enforces 15 min 03 JSONB, deeply: operators, GIN, and the boundary 16 min 04 Arrays and enums: when each beats a string 15 min 05 Generated and identity columns 15 min 06 Schema design: normalize, then denormalize on purpose 16 min

07

Transactions & concurrency

Isolation levels are promises about what concurrent transactions can see.

01 ACID in practice 14 min 02 Isolation levels in Postgres 16 min 03 SELECT FOR UPDATE and the job queue 16 min 04 Advisory locks 15 min 05 Deadlocks 15 min

08

Internals & tuning

Read the plan, fix the estimate, watch the bloat — the tuning loop.

01 Reading EXPLAIN ANALYZE 15 min 02 The cost-based planner 15 min 03 Statistics and the misestimate 16 min 04 VACUUM and table bloat 16 min 05 WAL and durability 16 min 06 The tuning workflow 16 min

09

Putting it together

Design and tune a real query system end to end.

01 Capstone: an analytics query system end to end 16 min

Build with this track

Guided projects that exercise what you learn here.

At-least-once job queue

Build a durable job queue on Postgres with visibility timeouts and idempotent consumers, so a crashed worker never drops a job.

Idempotent ETL Pipeline

Pipelines don't fail gracefully — they fail at 3 a.m., halfway through a load, and someone re-runs them. This project teaches the one property that separates a hobby script from production data engineering: a run you can repeat any number of times and still land exactly one copy of each row. You'll build batch ingestion, an idempotent load, a watermark for incremental pulls, and the data-quality gates that stop bad data before it poisons everything downstream.

Query plan visualizer

Paste an EXPLAIN (ANALYZE, FORMAT JSON) and render the plan tree with per-node timing and row-estimate error, so a bad join jumps out visually.

Reporting Schema Optimizer

Reporting is where a database earns or loses its keep: the queries are wide, the tables are big, and 'it's slow' is the most common bug in production analytics. You'll model a sales-and-events domain, write the honest slow versions of the dashboard queries, then make them fast with indexes and materialized views — and you'll read EXPLAIN ANALYZE to prove the speedup instead of guessing at it. This is the heart of the track: turning a vague 'the report is laggy' into a measured, defensible plan change.

Crash-safe key-value store with a WAL

Build a tiny on-disk KV store that survives a kill -9 mid-write by appending to a write-ahead log before touching the main file.

JavaScript Engine internals

How V8 runs your code — parsing, bytecode, hidden classes, the JIT, garbage collection, and how to stay fast.