Databases need recurring upkeep: refreshing materialised views, purging old data, rolling new partitions, running periodic aggregations, and housekeeping that keeps performance steady. Traditionally these jobs run from an external scheduler — system cron or an application timer — that connects to the database. pg_cron moves the schedule inside PostgreSQL itself, so the database runs its own maintenance on a cron-style timetable.
pg_cron is an extension that adds a job scheduler running as a background worker. You schedule a job with a familiar cron expression and a SQL command, and PostgreSQL executes it on time, every time, with no external daemon to configure, monitor, or keep in sync with the database. Job runs and their outcomes are recorded in catalog tables you can query.
Keeping the schedule beside the data has real advantages — fewer moving parts, no external credentials, and maintenance that travels with the database — but it also means the database is doing this work, so jobs must be written to be safe, bounded, and observable. This lesson covers scheduling jobs, what to run, and how to operate them responsibly.
Analogy🏏Cricket
🏏 Think of it like cricket: Just as a selection decision can depend on a derived benchmark — 'pick batters whose average exceeds the squad's average', which itself must first be computed — a subquery computes an inner result that the outer query then uses. The insight is that some questions are inherently two-stage: you must establish the benchmark before you can judge against it, and composing queries is how SQL expresses that dependency. Watch the selector actually do it: first he tallies every batter's runs and computes the squad average — that inner computation stands alone, needing nothing from the final decision — and only then does he walk the list judging each player against the number he just derived. That independence is what makes it an uncorrelated subquery: the database can compute the benchmark once, keep it, and reuse it for every row, exactly as the selector does not recompute the squad average per player. The composition also comes in shapes: a benchmark producing one number slots in where a value goes (a scalar subquery in WHERE), while a computed shortlist of qualifying players is itself a table the outer query can select from — a subquery in FROM. Two-stage question, two nested queries, dependency flowing inward-out.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.