pg_cron — Scheduled Tasks

pg_cron runs SQL statements on a recurring schedule. Jobs are persisted in TiKV, executed by a background worker, and tracked with full execution history.

pg_cron is a default extension — it is pre-installed when a database is created. You can verify with:

SQL

SELECT * FROM pg_extension WHERE extname = 'pg_cron';

If needed, install manually:

SQL

CREATE EXTENSION IF NOT EXISTS pg_cron;

Scheduling Jobs

3-argument form (recommended)

The 3-argument form assigns a name to the job. If a job with the same name already exists for the current user, it updates the existing job instead of creating a duplicate (upsert).

SQL

SELECT cron.schedule('cleanup', '0 3 * * *',
  $$DELETE FROM logs WHERE created_at < now() - interval '7 days'$$);
-- Returns: job_id (e.g., 1)

-- Call again with the same name to update schedule or command
SELECT cron.schedule('cleanup', '0 4 * * *',
  $$DELETE FROM logs WHERE created_at < now() - interval '30 days'$$);
-- Returns: same job_id (1)

2-argument form

Creates an anonymous job (no name, no upsert). Returns the assigned job ID.

SQL

SELECT cron.schedule('*/5 * * * *', 'SELECT check_alerts()');

Cron Expression Format

pg_cron uses standard 5-field cron expressions. Six-field expressions (with seconds) and special strings (@daily, @hourly) are not supported.

Output

┌───────────── minute (0–59)
│ ┌───────────── hour (0–23)
│ │ ┌───────────── day of month (1–31)
│ │ │ ┌───────────── month (1–12)
│ │ │ │ ┌───────────── day of week (0–6, Sunday = 0)
│ │ │ │ │
* * * * *

Supported operators: * (any), , (list), - (range), / (step).

Expression	Runs
* * * * *	Every minute
*/5 * * * *	Every 5 minutes
0 * * * *	Every hour
0 0 * * *	Daily at midnight
0 3 * * 1-5	Weekdays at 3 AM
0,30 9-17 * * *	Every 30 min during business hours
0 0 1 * *	First day of every month
0 0 * * 0	Every Sunday at midnight

Managing Jobs

View scheduled jobs

SQL

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

Modify a job

cron.alter_job updates individual fields. Pass NULL for fields you do not want to change.

SQL

-- Change schedule only
SELECT cron.alter_job(1, '0 4 * * *', NULL, NULL, NULL, NULL, NULL);

-- Disable a job
SELECT cron.alter_job(1, NULL, NULL, NULL, NULL, false, NULL);

-- Set max runtime to 30 minutes
SELECT cron.alter_job(1, NULL, NULL, NULL, NULL, NULL, '30min');

Signature: cron.alter_job(job_id, schedule, command, database, username, active, max_runtime)

All arguments after job_id accept NULL to leave unchanged. The database argument must always be NULL (cross-database scheduling is not supported). Only superusers can change the username (job owner).

max_runtime formats: '30min', '2h', '60s', '5000ms', or an integer (milliseconds). Use '0' for no limit.

Delete a job

SQL

-- By name
SELECT cron.unschedule('cleanup');

-- By ID
SELECT cron.unschedule(1);

Returns true if the job existed and was deleted, false if it did not exist. Deleting a job also removes its execution history.

Cancel a running job

SQL

SELECT cron.cancel(5);

Returns true if a running execution was found and a cancel signal was sent. Superuser only.

Execution History

View past runs

SQL

SELECT jobid, runid, status, return_message, start_time, end_time
FROM cron.job_run_details
WHERE jobid = 1
ORDER BY runid DESC
LIMIT 20;

Status values

Status	Meaning
starting	Job is about to execute
running	Execution in progress
succeeded	Completed successfully
failed	SQL error, timeout, or orphan recovery
cancelled	Cancelled via cron.cancel()

View currently running jobs

SQL

SELECT run_id, job_id, username, command, elapsed_ms
FROM cron.running_jobs;

Superuser only — non-superusers see an empty result set.

Execution history retention

Run records are automatically cleaned up after 7 days (configurable via DB9_CRON_RUN_RETENTION_DAYS).

Virtual Tables

cron.job

Column	Type	Description
jobid	BIGINT	Unique job identifier
schedule	TEXT	5-field cron expression
command	TEXT	SQL command
nodename	TEXT	Always localhost
nodeport	BIGINT	Always 5433
database	TEXT	Database name
username	TEXT	Job owner
active	BOOLEAN	Whether the job is enabled
jobname	TEXT	Job name (NULL for anonymous jobs)
max_runtime	TEXT	Formatted max execution time (e.g., 30min) or default
next_run_at	TEXT	Next scheduled execution (epoch format)

Non-superusers see only their own jobs.

cron.job_run_details

Column	Type	Description
jobid	BIGINT	Job ID
runid	BIGINT	Unique run identifier
job_pid	BIGINT	Process ID (NULL if not started)
database	TEXT	Database name
username	TEXT	Job owner
command	TEXT	SQL command executed
status	TEXT	starting, running, succeeded, failed, or cancelled
return_message	TEXT	Output or error message
start_time	TEXT	Start timestamp (epoch ms)
end_time	TEXT	End timestamp (epoch ms)

Non-superusers see only runs from their own jobs.

cron.running_jobs

Column	Type	Description
run_id	BIGINT	Current run identifier
job_id	BIGINT	Job ID
keyspace	TEXT	TiKV keyspace
db_id	BIGINT	Internal database ID
username	TEXT	Job owner
command	TEXT	SQL command being executed
started_at	TEXT	Start timestamp (epoch ms)
elapsed_ms	BIGINT	Milliseconds since start

Superuser only.

CLI Commands

All commands use db9 db cron <subcommand> <database>.

List jobs

Terminal

db9 db cron list <database>
db9 db cron list <database> --json

Create a job

Terminal

# Inline SQL
db9 db cron create <database> '0 3 * * *' --command 'VACUUM'

# Named job (enables upsert)
db9 db cron create <database> '*/15 * * * *' --name refresh --command 'REFRESH MATERIALIZED VIEW mv_stats'

# SQL from file
db9 db cron create <database> '0 0 * * *' --name daily-report --file report.sql

Delete a job

Terminal

db9 db cron delete <database> <job_id_or_name>

View execution history

Terminal

db9 db cron history <database>
db9 db cron history <database> --job cleanup --limit 50

Enable or disable a job

Terminal

db9 db cron enable <database> <job_id_or_name>
db9 db cron disable <database> <job_id_or_name>

Check job status

Terminal

db9 db cron status <database>
db9 db cron status <database> <job_id_or_name>

Execution Model

• Each job runs in its own temporary database connection with the job owner’s credentials
• Commands auto-commit (no wrapping transaction)
• Output and errors are captured in return_message
• The worker polls for due jobs every 60 seconds
• A claim guard prevents the same job from running twice in the same minute

Timeouts

Timeout	Default	Description
Job execution timeout	30 minutes	Global default; overridable per job via max_runtime
Orphan recovery	5 minutes	Running jobs with no heartbeat are marked failed

If a job exceeds its timeout, it is terminated and marked failed with the message “orphan recovery: execution timed out”.

Permissions

Operation	Non-superuser	Superuser
Schedule jobs	Own jobs	Any
View jobs (cron.job)	Own jobs only	All
Alter jobs	Own jobs only	Any; can also change owner
Unschedule jobs	Own jobs only	Any
Cancel running jobs	No	Yes
View running jobs	No (empty result)	Yes
View execution history	Own runs only	All

Practical Patterns

Cleanup old data

SQL

SELECT cron.schedule('cleanup-logs', '0 3 * * 0', $$
  DELETE FROM audit_logs
  WHERE created_at < now() - interval '90 days'
$$);

Periodic API polling with HTTP

SQL

SELECT cron.schedule('sync-rates', '0 * * * *', $$
  INSERT INTO exchange_rates (fetched_at, data)
  SELECT now(), (http_get('https://api.example.com/rates')).content::jsonb
$$);

Write periodic reports to fs9

SQL

SELECT cron.schedule('daily-metrics', '0 6 * * *', $$
  SELECT extensions.fs9_write(
    '/reports/metrics-' || to_char(now(), 'YYYY-MM-DD') || '.csv',
    (SELECT string_agg(metric || ',' || value::text, E'\n')
     FROM app_metrics WHERE ts > now() - interval '1 day')
  )
$$);

Refresh a materialized view

SQL

SELECT cron.schedule('refresh-mv', '*/30 * * * *',
  'REFRESH MATERIALIZED VIEW mv_dashboard_stats');

Limits

Limit	Value
Max jobs per database	50
Max concurrent jobs (global)	32
Cron expression fields	5 (no seconds)
Default job timeout	30 minutes
Orphan recovery timeout	5 minutes
Execution history retention	7 days
Worker poll interval	60 seconds

See Limits and Quotas for the complete list.

Error Messages

Error	Cause
extension pg_cron is not installed	Run CREATE EXTENSION pg_cron first
invalid cron expression: 6-field expressions (with seconds) are not supported	Use 5-field syntax
invalid cron expression: special strings like '@daily' are not supported	Use 0 0 * * * instead
cron.schedule: {arg} must not be NULL	Required argument is NULL
cron.unschedule: must be superuser or owner of the job	Permission denied
cron.cancel: must be superuser	Only superusers can cancel
cron.alter_job: cross-database scheduling not supported	database arg must be NULL

Related Docs

• Scheduled Jobs with pg_cron — tutorial: create, monitor, and manage periodic jobs
• HTTP from SQL — call external APIs from SQL (combine with cron for polling)
• fs9 — File System — write periodic reports to the file system
• Extensions Overview — all 9 built-in extensions
• Limits and Quotas — all operational limits
• CLI Reference — db9 db cron command details