SQL & PostgreSQL
Re-sharpen the SQL the ORM has been hiding: joins that filter, NULLs that break NOT IN, window frames, isolation anomalies, and reading an EXPLAIN plan on real Postgres.
73 concepts436 live tasks
Start practicing freeWhat's inside
Joins & Set Operations
An inner join is a filtered Cartesian product
LEFT JOIN keeps every left row, null-extending the misses
On an outer join, ON and WHERE are not interchangeable
CROSS JOIN is the Cartesian product, on purpose
A self-join is one table wearing two aliases
FULL OUTER JOIN keeps the unmatched rows from both sides
Anti-joins: finding rows with no match
UNION, INTERSECT, EXCEPT, and the ALL that skips deduplication
Aggregation & Grouping
GROUP BY collapses rows into one per distinct key
Aggregates skip NULLs, which changes the answer
WHERE filters rows; HAVING filters groups
FILTER and CASE: pivoting inside a single aggregate
GROUPING SETS, ROLLUP, and CUBE: many groupings in one scan
Why every SELECT column must be grouped or aggregated
DISTINCT ON: the top row per group, the Postgres way
array_agg and string_agg collect rows; order them explicitly
Subqueries, CTEs & Lateral
A scalar subquery stands in for a single value
Correlated subqueries reference the outer row
IN, EXISTS, and how each handles the empty and the null
EXISTS is a semi-join: it filters without multiplying rows
CTEs name a subquery so the query reads top-to-bottom
CTEs: inlined by default, an optimization fence with MATERIALIZED
Recursive CTEs walk hierarchies and graphs
LATERAL lets a subquery in FROM see the row beside it
Window Functions
Window functions compute across rows without collapsing them
PARTITION BY slices the window; ORDER BY sequences it
ROW_NUMBER, RANK, and DENSE_RANK differ on ties
LAG and LEAD reach into neighboring rows
Running totals need a frame, ROWS BETWEEN
The default frame is RANGE, and it groups peers
NTILE, PERCENT_RANK, and CUME_DIST bucket a distribution
The WINDOW clause names a spec so several functions share it
NULL Semantics & Three-Valued Logic
SQL booleans have three values: TRUE, FALSE, and UNKNOWN
x = NULL is never true; use IS NULL
WHERE and JOIN keep rows only when the predicate is TRUE
NOT IN with a NULL in the list returns nothing
IS DISTINCT FROM is the null-safe comparison
COALESCE supplies a fallback; NULLIF manufactures a NULL
GROUP BY and DISTINCT treat NULLs as equal; UNIQUE doesn't
NULLs sort together, at an end you must choose
Indexes & Query Plans
A b-tree index is a sorted structure for lookups and ranges
EXPLAIN shows the plan; EXPLAIN ANALYZE runs it
Reading a plan: nodes, costs, and estimated vs actual rows
A sequential scan is sometimes the right choice
In a multicolumn index, column order is everything
Wrapping a column in a function defeats its index
Index-only scans still check the visibility map
Nested loop, hash join, merge join: three shapes, three costs
The planner is only as good as its statistics
Transactions & Isolation
A transaction is all-or-nothing, and it's implicit by default
MVCC: readers see a snapshot, writers make new row versions
READ COMMITTED takes a fresh snapshot per statement
REPEATABLE READ freezes one snapshot for the whole transaction
Write skew: the anomaly REPEATABLE READ doesn't stop
Lost updates and the read-modify-write race
Row locks, lock modes, and how deadlocks happen
SERIALIZABLE guarantees no anomalies, at the cost of retries
Constraints, Types & Postgres Specifics
Primary and unique keys are constraints backed by indexes
Foreign keys enforce references and react to deletes
CHECK constraints and domains validate at the data layer
numeric is exact; float is fast and wrong for money
INSERT ... ON CONFLICT is atomic upsert
jsonb stores parsed JSON you can index and query
Arrays are a first-class type, with operators and GIN indexes
Identity and generated columns compute values for you
Performance & Operational Realities
VACUUM reclaims the dead tuples MVCC leaves behind
Autovacuum is automatic but not always sufficient
Bloat: when a table is mostly empty space
Transaction ID wraparound is the emergency vacuum exists to prevent
DDL takes strong locks that can stall the whole table
Diagnosing a slow query with EXPLAIN (ANALYZE, BUFFERS)
Postgres connections are heavy; pool them
OFFSET pagination degrades; keyset pagination stays flat