PostgreSQL 18 Mastery
80 hands-on labs and 240 live SQL missions across 10 tracks — from SELECT, JOIN, and window frames to JSONB, MVCC storage, pg_catalog, and DBRE clinics.
- 1Read the scenario & internals guide
- 2Write PostgreSQL 18 SQL in Studio
- 3Verify all 3 steps
Track 01 SQL Query Foundations, Filtering, Sorting & PG 18 Expressions Master relational projection, logical clause evaluation order, three-valued logic, pattern matching, PostgreSQL DISTINCT ON, null-safe predicates, type casting, TIMESTAMPTZ arithmetic, GENERATE_SERIES, uuidv7(), and virtual generated columns. 0/8 labs · 0/24 steps
-
Practice
Project computed columns, assign clean identifier aliases, and avoid silent integer-division truncation using PostgreSQL NUMERIC casts.
-
Practice
Filter heap tuples before projection using exact relational predicates and navigate why WHERE cannot reference SELECT aliases directly.
-
Practice
Prevent operator-precedence bugs where AND binds tighter than OR, and master TRUE / FALSE / UNKNOWN truth tables with IS NOT TRUE.
-
Practice
Express set membership, inclusive range bounds, case-insensitive ILIKE wildcards, and PostgreSQL POSIX regular expression predicates.
-
Practice
Guarantee deterministic multi-key sorting, control NULL sort placement, slice pages with FETCH/LIMIT, and pick the latest row per entity via DISTINCT ON.
-
Practice
Replace missing attributes with COALESCE fallbacks, guard division-by-zero with NULLIF, and compare nullable columns safely with IS DISTINCT FROM.
-
Practice
Classify ledger tiers with searched CASE WHEN expressions, apply PostgreSQL ::type casts, and parse structured codes with SPLIT_PART and TRIM.
-
Practice
Bucket UTC timestamps with DATE_TRUNC, synthesize zero-gap timelines with GENERATE_SERIES, mint k-sortable PostgreSQL 18 uuidv7() keys, and define VIRTUAL generated columns.
Track 02 DDL Schema Design, Constraints & Modern PG 17/18 DML Design production PostgreSQL tables with IDENTITY keys, zero-downtime ALTER TABLE evolutions, NULLS NOT DISTINCT & DEFERRABLE constraints, single-roundtrip RETURNING mutations, ON CONFLICT upserts, and PostgreSQL 18 MERGE. 0/8 labs · 0/24 steps
-
Practice
Author clean PostgreSQL schemas using SQL-standard IDENTITY columns instead of legacy SERIAL, TEXT over arbitrary VARCHAR(n), and exact NUMERIC currency.
-
Practice
Evolve live tables with fast catalog-only column defaults, two-phase CHECK ... NOT VALID constraints, and understand DROP CASCADE vs TRUNCATE.
-
Practice
Enforce relational invariants in storage using foreign key actions, domain CHECKs, PG 15+ UNIQUE NULLS NOT DISTINCT, and DEFERRABLE INITIALLY DEFERRED swaps.
-
Practice
Insert multi-row batches, backfill summary tables via INSERT ... SELECT, and stream server-generated keys back in one roundtrip with RETURNING.
-
Practice
Perform atomic in-place price/inventory updates and join reference tables cleanly using PostgreSQL UPDATE ... SET ... FROM syntax.
-
Practice
Purge rows matched against joined tables with DELETE ... USING, and capture before/after mutation images directly with PostgreSQL 18 RETURNING OLD.*, NEW.*.
-
Practice
Eliminate check-then-insert race conditions using atomic ON CONFLICT DO UPDATE with conditional WHERE guards against EXCLUDED.
-
Practice
Synchronize ledger staging feeds in a single pass with MERGE INTO ... RETURNING merge_action(), OLD.*, NEW.*, and precompute fast reporting snapshots with MATERIALIZED VIEW.
Track 03 All Joins, Set Operations, Subqueries, LATERAL & CTEs Master every relational join operator, prevent 1-to-N fan-out inflation, avoid the LEFT JOIN WHERE and NOT IN (NULL) traps, unlock correlated per-group Top-N with LATERAL, and traverse graphs with WITH RECURSIVE. 0/8 labs · 0/24 steps
-
Practice
Join normalized entities across foreign keys and fix subtle revenue double-counting caused by joining two independent 1-to-many child tables.
-
Practice
Preserve unmatched parent rows, find zero-activity entities with LEFT JOIN ... WHERE child.id IS NULL, and keep right-side filters inside ON so LEFT JOIN does not silently collapse into INNER JOIN.
-
Practice
Reconcile two independent data sources to surface orphans on both sides with FULL OUTER JOIN, and generate dense reporting matrices with CROSS JOIN.
-
Practice
Compare employees against their own managers and peer cohorts via aliased SELF JOINs, and match compensation bands using non-equi range predicates.
-
Practice
Combine result sets without unnecessary sort/hash deduplication via UNION ALL, and compute mutual intersections and set differences with INTERSECT and EXCEPT.
-
Practice
Write short-circuiting semi-joins and anti-joins with EXISTS / NOT EXISTS, and witness why a single NULL inside NOT IN (...) causes zero rows to return.
-
Practice
Execute parameterized inner subqueries per outer row with LEFT JOIN LATERAL to fetch each customer’s top-N orders using tight index probes.
-
Practice
Control PostgreSQL 12+ CTE inlining with MATERIALIZED, chain atomic DELETE ... RETURNING into archive INSERTs via writable CTEs, and walk org trees with WITH RECURSIVE + CYCLE.
Track 04 Aggregations, Rollups & Window Functions Mastery Go from core GROUP BY & HAVING semantics to PostgreSQL FILTER conditional aggregates, JSONB/ARRAY collection builders, continuous percentiles, OLAP ROLLUP/CUBE subtotals, and surgical window framing. 0/8 labs · 0/24 steps
-
Practice
Distinguish tuple counting COUNT(*) from non-null attribute counting COUNT(col), and leverage PostgreSQL primary-key functional dependency in GROUP BY.
-
Practice
Push row-level filters into WHERE to shrink hash-aggregate input while reserving HAVING strictly for post-group aggregate thresholds.
-
Practice
Compute multi-stage conversion funnels and status breakdowns in a single table scan using clean SQL:2003 / PostgreSQL FILTER (WHERE ...) clauses.
-
Practice
Shape denormalized lists and ready-to-serve nested JSON API payloads directly inside PostgreSQL with deterministic intra-aggregate ORDER BY.
-
Practice
Measure p50, p95, and p99 endpoint latency SLOs accurately using interpolated PERCENTILE_CONT vs discrete PERCENTILE_DISC ordered-set aggregates.
-
Practice
Replace brittle multi-query UNION ALL subtotal trees with single-pass GROUPING SETS / ROLLUP / CUBE and disambiguate real NULLs using GROUPING().
-
Practice
Rank peers within departments without collapsing rows, compute period-over-period deltas with LAG/LEAD, and deduplicate window specs via NAMED WINDOW.
-
Practice
Avoid the default RANGE UNBOUNDED PRECEDING peer-tie trap, fix LAST_VALUE frame bounds, compute rolling windows, and solve sessionization via Gaps-and-Islands.
Track 05 PostgreSQL Rich Native Data Types & Search Exploit PostgreSQL’s unfair advantage: binary JSONB + GIN containment, SQL/JSON JSON_TABLE(), native arrays, temporal TSTZRANGE + GiST exclusion constraints, weighted Full-Text Search, pg_trgm typo tolerance, and ltree hierarchies. 0/8 labs · 0/24 steps
-
Practice
Query nested audit event documents using binary JSONB paths and write GIN-indexable @> containment predicates instead of full-scan ->> text comparisons.
-
Practice
Patch deeply nested JSONB attributes atomically with jsonb_set, redact sensitive keys with #- path deletion, and merge objects with ||.
-
Practice
Filter nested arrays with SQL/JSON path predicates ($ ? (@.fee_cents > 1000)) and project nested JSON arrays into typed relational rows using JSON_TABLE().
-
Practice
Check OAuth permission scopes with GIN-backed @> and && array operators, and expand arrays while preserving 1-based element order via WITH ORDINALITY.
-
Practice
Model half-open [start, end) intervals without off-by-one boundary bugs, detect schedule collisions with &&, and compute free availability gaps via range_agg().
-
Practice
Combine scalar equality (=) and range overlap (&&) in a single GiST exclusion constraint so double-bookings are impossible even under concurrent transactions.
-
Practice
Search engineering runbooks at sub-millisecond speed with GIN-indexed weighted tsvectors, user-safe websearch_to_tsquery syntax, cover-density ranking, and highlighted snippets.
-
Practice
Recover misspelled user search queries with pg_trgm trigram distance (<->), and roll up subtree budgets instantaneously using GiST-indexed ltree paths (<@).
Track 06 Indexing Architecture, Optimizer & EXPLAIN (ANALYZE, BUFFERS) Think like the PostgreSQL query planner: write SARGable predicates, order composite B-Tree columns, eliminate heap fetches with covering INCLUDE indexes, choose GIN/GiST/BRIN, read EXPLAIN (ANALYZE, BUFFERS), and fix correlated skew with CREATE STATISTICS. 0/8 labs · 0/24 steps
-
Practice
Understand why wrapping indexed columns in EXTRACT(), LOWER(), or arithmetic blinds B-Tree probes, and rewrite filters into SARGable range predicates.
-
Practice
Design multi-column B-Tree indexes that satisfy equality predicates and ORDER BY LIMIT in a single ordered scan with zero external Sort nodes.
-
Practice
Attach non-key payload columns to B-Tree leaf pages via INCLUDE without bloating upper internal pages, and verify Heap Fetches: 0 after VACUUM.
-
Practice
Shrink hot queue and tenant lookup indexes by 95% with Partial WHERE clauses, and accelerate case-insensitive or JSONB scalar lookups via Expression Indexes.
-
Practice
Match workload geometry to PostgreSQL’s pluggable index access methods—including tiny append-only BRIN min/max block summaries for time-series tables.
-
Practice
Read execution plans from innermost leaves outward, multiply per-loop timings by loops, spot cardinality misestimates, and prioritize shared buffer reads/hits over wall-clock jitter.
-
Practice
Inspect how the cost-based optimizer chooses between indexed Nested Loops, in-memory Hash Joins, and pre-sorted Merge Joins—and when cardinality errors pick the wrong one.
-
Practice
Inspect planner MCVs and histograms in pg_stats, cure correlated-column row underestimates with CREATE STATISTICS (dependencies, ndistinct), and replace O(N) OFFSET scans with O(log N) row-value keyset pagination.
Track 07 Programmability (PL/pgSQL), Triggers, Partitioning & RLS Build server-side guarantees cleanly inside PostgreSQL: planner-inlined SQL functions, exception-safe PL/pgSQL, injection-proof dynamic SQL, transition-table triggers, declarative partitioning, and multi-tenant Row-Level Security. 0/8 labs · 0/24 steps
-
Practice
Write reusable SQL scalar and table functions, label volatility accurately so expression indexes and planner inlining work, and avoid black-box VOLATILE slowdowns.
-
Practice
Author transactional ledger transfer functions in PL/pgSQL with strict balance guards, structured diagnostics, and awareness of EXCEPTION block subtransaction overhead.
-
Practice
Build administrative maintenance procedures using CALL and construct SQL-injection-proof dynamic statements via format('%I', ident) and EXECUTE ... USING.
-
Practice
Normalize and guard incoming writes in BEFORE FOR EACH ROW triggers, and record tamper-evident OLD/NEW JSONB diffs in AFTER audit triggers.
-
Practice
Replace 10,000 per-row trigger invocations on bulk updates with a single set-based AFTER STATEMENT trigger querying NEW TABLE / OLD TABLE transition relations.
-
Practice
Design monthly range-partitioned telemetry tables, understand composite partition-key constraints, and prove compile-time and run-time partition pruning in EXPLAIN.
-
Practice
Onboard historical archive tables with ATTACH PARTITION and retire expired time windows instantaneously via DETACH/DROP instead of generating millions of dead tuples with DELETE.
-
Practice
Isolate SaaS tenant data at the storage engine layer using ENABLE/FORCE ROW LEVEL SECURITY policies bound to session context current_setting('app.tenant_id', true).
Track 08 Transactions, Locks, MVCC & Storage Internals Open up the PostgreSQL storage engine: savepoints, snapshot isolation anomalies, SKIP LOCKED queues, advisory locks, live ctid/xmin/xmax tuple headers, 8 KB slotted pages via pageinspect, VACUUM/HOT/visibility maps, and pg_buffercache. 0/8 labs · 0/24 steps
-
Practice
Recover gracefully from constraint errors mid-batch using SAVEPOINT / ROLLBACK TO SAVEPOINT and observe lazy virtual-to-real XID assignment via pg_current_xact_id_if_assigned().
-
Practice
Understand per-statement vs per-transaction MVCC snapshots, prevent lost updates under REPEATABLE READ, and guard cross-row invariants against write skew with SERIALIZABLE.
-
Practice
Choose FOR NO KEY UPDATE to avoid blocking child foreign-key inserts, and build a non-blocking worker dequeue query using UPDATE ... WHERE job_id = (SELECT ... FOR UPDATE SKIP LOCKED).
-
Practice
Coordinate per-tenant cron jobs and webhook deduplication in shared memory without row bloat using transaction-scoped pg_try_advisory_xact_lock(hashtext(...)) and verify in pg_locks.
-
Practice
Observe PostgreSQL’s append-only UPDATE model firsthand by watching physical (block, offset) ctid jumps and xmin/xmax transaction stamps across mutations.
-
Practice
Crack open live 8192-byte heap pages and B-Tree index pages with pageinspect to inspect lp_off line pointers, pd_lower/pd_upper free space, t_infomask bits, and B-Tree root height.
-
Practice
Measure per-block reusable bytes in pg_freespacemap, inspect all-visible / all-frozen bits via pg_visibility, and tune fillfactor to unlock zero-index-churn HOT updates.
-
Practice
Query live RAM buffer pool residency, dirty block counts, and clock-sweep usagecount via pg_buffercache, and quantify exact WAL bytes emitted by DML via pg_wal_lsn_diff().
Track 09 PostgreSQL System Catalogs & Live DB Introspection Query the database’s own brain: master pg_class, pg_namespace, pg_attribute, pg_type, TOAST & relation sizing functions, pg_constraint unindexed FK audits, pg_index internals, pg_stat_activity, pg_settings, pg_stat_user_tables, and PG 18 pg_stat_io. 0/8 labs · 0/24 steps
-
Practice
Join pg_class and pg_namespace to inventory tables, partitions, indexes, materialized views, and TOAST relations by relkind alongside planner reltuples/relpages.
-
Practice
Inspect physical column order (attnum > 0), dropped-column ghosts (attisdropped), exact SQL types via format_type(), and CPU struct alignment padding (attlen, attalign).
-
Practice
Decompose every table’s storage footprint across main heap, FSM/VM forks, secondary indexes, and out-of-line TOAST compression tables.
-
Practice
Write the canonical DBRE catalog query joining pg_constraint (contype = 'f') against pg_index.indkey prefixes to catch unindexed foreign keys before ON DELETE CASCADE locks stall production.
-
Practice
Audit live index access methods (pg_am), partial predicates (indpred), expression trees (indexprs), included payload columns (indnkeyatts), and broken indisvalid = false indexes.
-
Practice
Inspect your live backend session in pg_stat_activity and audit memory, WAL, autovacuum, and timeout GUCs in pg_settings by unit, context, and non-default overrides.
-
Practice
Analyze live sequential-vs-index scan ratios, HOT update efficiency (n_tup_hot_upd / n_tup_upd), dead tuple accumulation, and index scan utilization.
-
Practice
Compute per-table heap and index buffer cache hit ratios safely without zero-division errors, and inspect PostgreSQL 18 unified backend I/O and WAL counters.
Track 10 Real-Life Production PostgreSQL Troubleshooting Clinics Step into PagerDuty SEV-1 war rooms modeled on real FAANG database incidents: diagnose lock convoys, zombie transactions, 99% CPU queries, temp-disk sort spills, autovacuum starvation, XID wraparound emergencies, redundant indexes, and orphaned replication slots. 0/8 labs · 0/24 steps
-
Practice
Checkout latency just spiked to timeouts after a migration started. Build a blocker-chain query to separate victim readers, the queued AccessExclusiveLock DDL waiter, and the true root blocker.
-
Practice
Application pods are failing with remaining connection slots reserved. Pinpoint leaked idle in transaction sessions holding row locks and pinning the cluster xmin horizon.
-
Practice
Primary CPU is pinned at 99%. Rank normalized query fingerprints by % of total cluster execution time, buffer read amplification, and call-frequency death-by-a-thousand-cuts.
-
Practice
Scratch NVMe IOPS tripled at 14:00 UTC. Convert 8 KB temp_blks_written counters into spilled megabytes per call and size safe transaction-scoped SET LOCAL work_mem budgets.
-
Practice
Find hot tables whose dead tuples haven’t crossed PostgreSQL’s default 20% autovacuum_vacuum_scale_factor threshold, compute exact trigger thresholds, and identify stale planner stats.
-
Practice
PagerDuty fired: cluster XID age passed 1.89 billion (94% to emergency read-only shutdown). Triage tables closest to the 2B cliff and correlate every xmin blocker preventing VACUUM FREEZE.
-
Practice
Audit production indexes to reclaim gigabytes of RAM and cut UPDATE write amplification: catch failed INVALID CONCURRENTLY indexes, non-unique 0-scan indexes, and exact leftmost-prefix duplicates.
-
Practice
Primary pg_wal volume will hit ENOSPC PANIC in 28 minutes. Compute retained WAL bytes per slot using pg_wal_lsn_diff(current_wal_lsn, restart_lsn) and isolate the abandoned CDC slot.