# pg_cron — Run Periodic Jobs Inside PostgreSQL

> A simple cron-based job scheduler that runs as a PostgreSQL extension, letting you schedule SQL commands, function calls, and maintenance tasks directly from your database.

## Install

Save in your project root:

# pg_cron — Run Periodic Jobs Inside PostgreSQL

## Quick Use
```sql
-- Enable the extension (requires superuser)
CREATE EXTENSION pg_cron;
-- Schedule a job to vacuum a table every night at 3 AM
SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM ANALYZE my_table');
-- List scheduled jobs
SELECT * FROM cron.job;
```

## Introduction
pg_cron is a PostgreSQL extension that provides cron-based job scheduling directly inside the database. Instead of managing external cron jobs or scheduler services, you define recurring tasks as SQL statements within PostgreSQL. It is developed by the team behind Citus Data (now part of Microsoft).

## What pg_cron Does
- Schedules recurring SQL commands using standard cron syntax (minute, hour, day, month, weekday)
- Runs jobs within the PostgreSQL process — no external scheduler or agent required
- Supports scheduling across multiple databases in the same PostgreSQL cluster
- Tracks job execution history with start time, duration, and status in the cron.job_run_details table
- Allows one-time scheduled execution with cron.schedule_in_database for ad-hoc tasks

## Architecture Overview
pg_cron runs as a background worker process within PostgreSQL. When the extension is loaded (via shared_preload_libraries), a dedicated background worker starts and periodically checks the cron.job table for jobs that are due. When a job's schedule matches the current time, the worker opens a new connection to the target database and executes the SQL command. Job metadata and run history are stored in the cron schema within the designated database.

## Self-Hosting & Configuration
- Install from your OS package manager (apt/yum) or compile from source
- Add pg_cron to shared_preload_libraries in postgresql.conf and restart PostgreSQL
- Set cron.database_name to the database where the cron schema will be created
- Grant usage on the cron schema to non-superuser roles that need to schedule jobs
- Configure cron.max_running_jobs to limit concurrent job execution (default is 32)

## Key Features
- Standard 5-field cron syntax familiar to any sysadmin
- Jobs run inside PostgreSQL transactions with full ACID guarantees
- Execution history with timing and error details in cron.job_run_details
- Cross-database job scheduling within a single PostgreSQL cluster
- Lightweight — adds minimal overhead as a background worker

## Comparison with Similar Tools
- **OS-level cron + psql** — requires shell access and psql client; pg_cron runs entirely within PostgreSQL
- **pgAgent** — pgAdmin's job scheduler with GUI; pg_cron is simpler and SQL-native
- **pg_timetable** — more feature-rich scheduler with chained tasks; pg_cron is simpler for basic recurring jobs
- **Celery Beat** — application-level scheduler in Python; pg_cron is database-native, no app code needed
- **Kubernetes CronJobs** — container-based scheduling; pg_cron avoids the overhead of spinning up pods

## FAQ
**Q: Can I schedule jobs from a non-superuser role?**
A: Yes. Grant USAGE on the cron schema and INSERT on the cron.job table to the role. The job will run with the permissions of the scheduling role.

**Q: What happens if a job fails?**
A: The failure is logged in cron.job_run_details with the error message. The job remains scheduled and will retry at the next matching time.

**Q: Can I run jobs more frequently than once per minute?**
A: No. pg_cron uses standard cron granularity with a minimum interval of one minute.

**Q: Is pg_cron available on managed PostgreSQL services?**
A: Yes. It is available on Azure Database for PostgreSQL, Amazon RDS, Google Cloud SQL, Supabase, and other managed services.

## Sources
- https://github.com/citusdata/pg_cron
- https://www.citusdata.com/blog/2016/09/09/pgcron-run-periodic-jobs-in-postgres/

---
Source: https://tokrepo.com/en/workflows/asset-cf2ad0a6
Author: AI Open Source