pg_cron Basics

Learning Focus

Use this lesson to schedule safe, observable recurring jobs in PostgreSQL using pg_cron, and to recognize when an external scheduler (cron/systemd/Kubernetes) is the better choice.

Concept Overview

PostgreSQL does not include a built-in SQL scheduler. In many deployments, the most common “database-internal cron” option is the pg_cron extension.

With pg_cron you can schedule SQL commands such as:

• maintenance: ANALYZE, VACUUM (ANALYZE), REINDEX, refreshing materialized views
• retention: deleting or archiving old rows
• rollups: daily aggregates into summary tables

Why is it important?

• Operational hygiene: regular maintenance keeps plans and storage healthy
• Consistency: jobs run the same way regardless of which app/service is writing
• Reduced manual work: fewer ad hoc scripts and runbook steps
• Repeatability: job definitions live in the database (with migrations)

Where does it fit?

Scheduling sits between application code and database operations:

• If the job is “pure SQL in one database”, pg_cron is often a good fit.
• If the job needs OS/network/API calls, retries, distributed locking, or cross-system orchestration, use an external scheduler.

---

Syntax & Rules

Core Syntax

Install extension (per database):

CREATE EXTENSION IF NOT EXISTS pg_cron;

Schedule a job:

SELECT cron.schedule(
  'job_name',
  '0 2 * * *',
  $$VACUUM (ANALYZE) public.orders$$
);

List jobs:

SELECT jobid, jobname, schedule, command
FROM cron.job
ORDER BY jobid;

Unschedule:

SELECT cron.unschedule('job_name');
-- or
SELECT cron.unschedule(job_id);

Prerequisites / Server Requirements

pg_cron usually requires:

Requirement	What it means
Server has pg_cron installed	extension binaries present on the host
shared_preload_libraries includes pg_cron	loaded at server start
Configuration (often)	e.g. cron.database_name, depending on deployment
Permissions	ability to CREATE EXTENSION and schedule jobs

Notes:

• In many managed services, enabling shared_preload_libraries is a platform setting.
• If you cannot install pg_cron, you can still schedule SQL via external cron + psql.

Schedule Format (Cron Expression)

Most pg_cron setups use the standard 5-field cron syntax:

minute hour day-of-month month day-of-week

Examples:

Schedule	Meaning
*/5 * * * *	every 5 minutes
0 2 * * *	daily at 02:00
0 2 * * 1	Mondays at 02:00

Key Rules and Considerations

• Jobs run in their own sessions. Treat them like normal SQL clients.
• Jobs should be idempotent (safe to rerun) and bounded (avoid unbounded deletes/updates).
• Prefer jobs that are a single SQL statement (or a small, predictable sequence).
• Observe and log: rely on cron.job_run_details (when available) and/or your own log tables.

---

Step-by-Step Examples

Example 1: Install pg_cron and Verify It’s Available (Beginner)

CREATE EXTENSION IF NOT EXISTS pg_cron;

SELECT extname
FROM pg_extension
WHERE extname = 'pg_cron';

Expected output:

CREATE EXTENSION
 extname
---------
 pg_cron
(1 row)

Explanation:

• CREATE EXTENSION installs the SQL objects into the current database.
• If server-level prerequisites are missing, the extension creation may fail (platform-dependent).

---

Example 2: Schedule a Job and See the Job ID (Beginner)

Schedule a daily job:

SELECT cron.schedule(
  'nightly-analyze-orders',
  '0 1 * * *',
  $$ANALYZE public.orders$$
) AS job_id;

Expected output (example):

 job_id
--------
 42
(1 row)

List it:

SELECT jobid, jobname, schedule, command
FROM cron.job
WHERE jobname = 'nightly-analyze-orders';

Expected output (example):

 jobid |        jobname         | schedule  |          command
-------+------------------------+-----------+----------------------------
 42    | nightly-analyze-orders | 0 1 * * * | ANALYZE public.orders
(1 row)

Explanation:

• cron.schedule(...) returns a job id.
• Storing the job name makes scripts and operations easier.

---

Example 3: Make a Job Observable with a Log Table (Intermediate)

Create a small job log table:

DROP TABLE IF EXISTS maintenance_job_log;

CREATE TABLE maintenance_job_log (
  log_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  job_name text NOT NULL,
  ran_at timestamptz NOT NULL DEFAULT now(),
  notes text
);

Schedule an hourly job that inserts a log row:

SELECT cron.schedule(
  'hourly-log-heartbeat',
  '0 * * * *',
  $$INSERT INTO maintenance_job_log (job_name, notes)
    VALUES ('hourly-log-heartbeat', 'ok')$$
) AS job_id;

Expected output (example):

DROP TABLE
CREATE TABLE
 job_id
--------
 43
(1 row)

Explanation:

• A log table gives you a cheap “did it run?” signal even when you do not have central logging.
• Keep this lightweight; avoid logging huge payloads.

---

Example 4: Inspect Run History (If Available) (Intermediate)

Many pg_cron installations include run details:

SELECT jobid, status, start_time, end_time, return_message
FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 5;

Expected output (example):

 jobid | status  |      start_time      |       end_time       | return_message
-------+---------+----------------------+----------------------+----------------
 43    | succeeded | 2026-03-05 10:00+00 | 2026-03-05 10:00+00 | INSERT 0 1
(1 row)

Explanation:

• Use run details to confirm success, duration, and failure messages.
• If your installation does not expose this view/table, rely on application logs or your own job log table.

---

Practical Use Cases

1) Nightly Statistics Refresh

Context: keep planner stats fresh for changing tables.

SELECT cron.schedule('nightly-analyze-orders', '0 1 * * *', $$ANALYZE public.orders$$);

2) Regular Materialized View Refresh

Context: keep dashboards fast.

SELECT cron.schedule('refresh-daily-revenue', '15 3 * * *', $$REFRESH MATERIALIZED VIEW daily_revenue$$);

3) Retention Cleanup

Context: keep audit/log tables bounded.

SELECT cron.schedule('cleanup-audit-log', '0 4 * * *', $$DELETE FROM audit_log WHERE changed_at < now() - interval '90 days'$$);

4) External Scheduler Fallback

Context: you cannot install pg_cron (or need OS tasks).

-- Run via OS cron:
-- psql "postgresql://..." -c "ANALYZE public.orders"

---

Common Mistakes & Troubleshooting

Mistake 1: Non-Idempotent Jobs

Wrong (creates duplicates on rerun):

SELECT cron.schedule(
  'daily-rollup',
  '0 1 * * *',
  $$INSERT INTO daily_summary(day, total)
    SELECT current_date - 1, SUM(amount) FROM orders WHERE created_at::date = current_date - 1$$
);

What happens: if the job runs twice, yesterday’s row inserts twice.

Fix (idempotent upsert):

INSERT INTO daily_summary(day, total)
SELECT current_date - 1, SUM(amount)
FROM orders
WHERE created_at >= (current_date - 1)
  AND created_at < current_date
ON CONFLICT (day)
DO UPDATE SET total = EXCLUDED.total;

---

Mistake 2: Scheduling Heavy Work at Peak Traffic

What happens: higher latency and lock contention.

Fix: schedule off-peak, keep work bounded, and avoid long transactions.

---

Mistake 3: No Observability

Wrong:

-- schedule deletes with no logging and no run inspection

What happens: failures go unnoticed.

Fix: check cron.job_run_details (if available) and/or write lightweight logs.

Debugging tips:

1. List jobs: SELECT * FROM cron.job ORDER BY jobid;.
2. Inspect recent runs: SELECT * FROM cron.job_run_details ORDER BY start_time DESC LIMIT 20;.
3. Confirm permissions: scheduled SQL runs as a role; ensure it can read/write target tables.
4. If a job is slow, run the SQL manually with EXPLAIN (ANALYZE, BUFFERS) on a staging copy.

---

Best Practices

• ✅ Prefer idempotent patterns (ON CONFLICT DO UPDATE); ❌ insert-only rollups that double on reruns.
• ✅ Keep jobs bounded (batch deletes, partition drops); ❌ delete millions of rows in one run.
• ✅ Schedule off-peak; ❌ run heavy maintenance during peak traffic.
• ✅ Add observability (run history/log tables); ❌ rely on hope.
• ✅ Use external schedulers for orchestration and non-SQL work; ❌ force cross-system logic into SQL jobs.

---

Hands-On Practice

Exercise 1 (Easy): Schedule an hourly heartbeat

Task: Schedule a pg_cron job that inserts one row into a log table every hour.

-- Your SQL here

Solution:

CREATE TABLE IF NOT EXISTS practice_job_log (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  job_name text NOT NULL,
  ran_at timestamptz NOT NULL DEFAULT now()
);

SELECT cron.schedule(
  'practice-hourly-heartbeat',
  '0 * * * *',
  $$INSERT INTO practice_job_log (job_name) VALUES ('practice-hourly-heartbeat')$$
);

---

Exercise 2 (Medium): List scheduled jobs

Task: List job id, name, schedule, and command.

-- Your SQL here

Solution:

SELECT jobid, jobname, schedule, command
FROM cron.job
ORDER BY jobid;

---

Exercise 3 (Advanced): Unschedule safely

Task: Unschedule the job by name (and make it safe to rerun).

-- Your SQL here

Solution:

SELECT cron.unschedule('practice-hourly-heartbeat');

---

Connection to Other Concepts

Concept	Why it matters
Performance optimization	ANALYZE/VACUUM scheduling keeps plans stable
Transactions and concurrency	jobs take locks; long jobs increase contention
Views/materialized views	refresh schedules affect dashboard freshness
Backup and recovery	scheduled backups/verifications are operationally critical
Triggers	scheduled jobs often clean up audit tables produced by triggers

---

Visual Learning Diagram

flowchart TD
  A[Define schedule\ncron.schedule] --> B[pg_cron worker]
  B --> C[New DB session]
  C --> D[Execute SQL command]
  D --> E[Success/Failure]
  E --> F[Run history\njob_run_details]
  E --> G[Optional app log table]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F,G allNodes
  class D highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
Unbounded jobs	long locks and bloat	process in batches / partition
No run visibility	silent failures	job_run_details + logging
Wrong timezone assumptions	jobs run at unexpected time	define time zone and schedule explicitly
Privilege mismatch	jobs fail	schedule with a role that has required grants
Overusing pg_cron	brittle workflows	use external schedulers for orchestration

---

Quick Reference

CREATE EXTENSION IF NOT EXISTS pg_cron;
SELECT cron.schedule('job', '0 2 * * *', $$ANALYZE public.t$$);
SELECT jobid, jobname, schedule FROM cron.job;
SELECT cron.unschedule('job');
SELECT jobid, status, start_time FROM cron.job_run_details ORDER BY start_time DESC LIMIT 5;

What's Next

• Previous: Triggers - Triggers often produce audit data that needs retention jobs.
• Next: Recurring Maintenance Jobs - Design safe, low-impact maintenance routines.
• Module Overview - Return to Scheduling and Automation overview.