pg_cron: Scheduling Automated Jobs Inside PostgreSQL

What if your database could take care of itself — vacuuming stale rows, refreshing materialized views, and pruning expired sessions — all without a single external process? Most teams reach for shell cron jobs or third-party schedulers the moment they need recurring database work, never realizing that PostgreSQL has had a native answer for years. Could the simplest solution really be the one living right inside your database? With pg_cron, the answer is yes.

What is pg_cron?

pg_cron is a PostgreSQL extension that runs scheduled jobs directly inside the database engine. Rather than relying on an OS-level cron daemon that shells out to psql, pg_cron registers itself as a background worker process that wakes up every minute, checks the cron.job table, and executes any due SQL statements or stored procedure calls.

Originally developed by Citus Data (now part of Microsoft), pg_cron is open source and widely deployed across managed PostgreSQL services including Amazon RDS, Amazon Aurora, Supabase, and Citus Cloud. The key design goals are simplicity and reliability: the scheduler uses the same cron syntax that every Unix operator already knows, and because job definitions live in a database table, they are durable — jobs survive server restarts and point-in-time recovery restores.

Under the hood, pg_cron stores all job metadata in cron.job and writes a detailed execution log to cron.job_run_details after every run. This means your on-call engineer can query job history directly with SELECT, rather than digging through /var/log/cron or CloudWatch Logs.

When to choose pg_cron

• You need lightweight recurring SQL tasks: cleanup, refresh, vacuum, partition creation.
• You are on a managed service (RDS, Aurora, Supabase) where shell access is unavailable.
• You want job history stored relationally, queryable alongside your application data.
• You prefer fewer moving parts — one less cron daemon to manage and monitor outside the database.

Installing and Configuring pg_cron

Step 1: Install the package

On self-managed PostgreSQL, install the pg_cron OS package matching your PostgreSQL major version:

# Ubuntu/Debian (PostgreSQL 17)
sudo apt-get install postgresql-17-cron

# RHEL/CentOS
sudo yum install pg_cron_17

On Amazon RDS / Aurora, no package installation is needed. The extension is included in the engine. On Supabase, pg_cron is pre-installed and enabled by default.

Step 2: Configure postgresql.conf

pg_cron must be loaded at server start as a shared preload library. Edit postgresql.conf and restart PostgreSQL:

# postgresql.conf
shared_preload_libraries = 'pg_cron'

# pg_cron metadata (cron.job, cron.job_run_details) is stored in this database
cron.database_name = 'postgres'

# Optional (PostgreSQL 14+): allow per-job role specification
# without running every job as superuser
cron.use_background_workers = on

The cron.database_name setting controls where the extension's metadata tables live. This is almost always postgres. Jobs can still execute SQL against any other database in the cluster using cron.schedule_in_database().

Step 3: Create the extension

-- Connect to the cron.database_name database
\c postgres

-- Create the extension (requires superuser)
CREATE EXTENSION pg_cron;

-- Optionally grant non-superuser access to schedule jobs
GRANT USAGE ON SCHEMA cron TO app_admin;

Scheduling Jobs

Cron syntax primer

pg_cron uses standard five-field cron syntax. Each field is separated by a space:

All times are interpreted in UTC by default. There is no built-in timezone conversion in pg_cron — to schedule a job at 9 AM Eastern (UTC-5), use '0 14 * * *'.

Common job scheduling patterns

Use cron.schedule() to register a job in the current database, or cron.schedule_in_database() to target a different database (required on RDS/Aurora/Supabase where the extension lives in postgres):

-- Schedule a simple SQL statement (runs in the cron.database_name database)
SELECT cron.schedule('vacuum-orders', '0 3 * * *', 'VACUUM orders');

-- Schedule against a specific database (recommended on managed services)
SELECT cron.schedule_in_database(
  'vacuum-myapp-orders',
  '0 3 * * *',
  'VACUUM orders',
  'myapp'  -- target database name
);

-- Schedule a DELETE using dollar-quoting
SELECT cron.schedule('process-expired-sessions', '*/15 * * * *',
  $$DELETE FROM sessions WHERE expires_at < NOW() - INTERVAL '1 hour'$$
);

-- Schedule a stored procedure call
SELECT cron.schedule('daily-report', '0 9 * * *',
  $$CALL generate_daily_report()$$
);
-- Note: 9 AM UTC; adjust hour offset for your target timezone

The first argument to cron.schedule() is a unique job name — this name is what you will use to unschedule or disable the job later. Choose descriptive names like cleanup-deleted-users rather than generic ones like job1.

Real-World Job Examples

1. Automated partition maintenance

Declarative partitioning requires that future partitions exist before data arrives. Rather than adding partition creation to application deployment scripts, delegate it to pg_cron. The following job runs on the 20th of each month and creates next month's partition:

SELECT cron.schedule('create-monthly-partition', '0 0 20 * *',
  $$
  DO $inner$DECLARE
    next_month DATE := date_trunc('month', NOW()) + INTERVAL '1 month';
    partition_name TEXT := 'orders_' || to_char(next_month, 'YYYY_MM');
  BEGIN
    EXECUTE format(
      'CREATE TABLE IF NOT EXISTS %I PARTITION OF orders
       FOR VALUES FROM (%L) TO (%L)',
      partition_name, next_month, next_month + INTERVAL '1 month'
    );
  END;$inner$
  $$
);

This approach pairs well with pg_partman for range-partitioned tables; the two tools can coexist, with pg_cron handling custom partition logic and pg_partman managing the standard monthly/weekly ranges.

2. Stale data cleanup

Soft-delete patterns accumulate rows in the users table that are logically deleted but still physically present. A nightly pg_cron job permanently removes records deleted more than 30 days ago:

-- Hard-delete soft-deleted users after 30-day retention window
SELECT cron.schedule('cleanup-deleted-records', '0 4 * * *',
  $$
  DELETE FROM users
  WHERE deleted_at IS NOT NULL
    AND deleted_at < NOW() - INTERVAL '30 days';
  $$
);

-- Expire old notification records every 30 minutes
SELECT cron.schedule('expire-notifications', '*/30 * * * *',
  $$DELETE FROM notifications WHERE created_at < NOW() - INTERVAL '7 days'$$
);

Running cleanup jobs during off-peak hours (2 AM – 5 AM UTC) reduces lock contention with OLTP traffic. For very large tables, consider batching deletes inside a loop with LIMIT and a short pg_sleep() to throttle I/O.

3. Refresh materialized views

Materialized views that back dashboards or reporting queries need periodic refreshes. CONCURRENTLY allows reads to continue during the refresh — but requires a unique index on the view:

-- Refresh hourly order statistics at 5 minutes past each hour
SELECT cron.schedule('refresh-hourly-stats', '5 * * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY hourly_order_stats'
);

Scheduling the refresh at 5 minutes past the hour (rather than 0) staggers it away from other top-of-hour jobs and avoids contention on the PostgreSQL background writer.

4. Table bloat management with pg_repack

While VACUUM reclaims dead tuple space for reuse, it does not return space to the OS. For heavily updated tables, a weekly pg_repack during a low-traffic maintenance window eliminates table bloat without an exclusive lock:

-- Placeholder job — actual pg_repack is invoked via shell/Lambda
-- Use this to document the maintenance window in the cron.job table
SELECT cron.schedule('weekly-repack-placeholder', '0 2 * * 0',
  $$SELECT pg_catalog.pg_sleep(0)$$
);
-- Pair with an external orchestration tool (e.g., AWS Lambda, systemd timer)
-- that calls: pg_repack -d myapp -t orders

Note that pg_repack is a client-side binary and cannot be called directly from pg_cron SQL. Use the placeholder pattern to capture the maintenance window in one place while the actual repack runs externally.

Monitoring and Managing Jobs

Every aspect of pg_cron job management is SQL-accessible. There are no dashboards to log into and no log files to tail.

-- List all scheduled jobs
SELECT jobid, schedule, command, nodename, nodeport, database, active
FROM cron.job;

-- Review recent execution history (last 20 runs across all jobs)
SELECT jobid, job_pid, database, command, status, return_message, start_time, end_time
FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 20;

-- Find all failed job runs
SELECT *
FROM cron.job_run_details
WHERE status = 'failed'
ORDER BY start_time DESC;

-- Unschedule a job by its registered name
SELECT cron.unschedule('vacuum-orders');

-- Unschedule a job by its numeric ID
SELECT cron.unschedule(42);

-- Temporarily disable a job without removing it
UPDATE cron.job SET active = false WHERE jobname = 'vacuum-orders';

-- Re-enable it
UPDATE cron.job SET active = true WHERE jobname = 'vacuum-orders';

The cron.job_run_details table is your first stop when a job silently stops working. The return_message column captures the full PostgreSQL error text from failed runs. It is good practice to set up an alert that queries this table for status = 'failed' rows within the past hour — a simple pg_cron job that inserts into a job_alerts table (or calls a pg_notify channel) can implement this without any external tooling.

Keep in mind that cron.job_run_details grows indefinitely. Add a companion cleanup job to trim old history:

SELECT cron.schedule('purge-job-run-details', '0 5 * * *',
  $$DELETE FROM cron.job_run_details WHERE end_time < NOW() - INTERVAL '30 days'$$
);

pg_cron vs External Cron vs pg_timetable

Choosing the right scheduler depends on where your jobs run, how complex they are, and whether you are operating on managed infrastructure.

The verdict: use pg_cron for single-SQL recurring jobs on any PostgreSQL — especially managed services. Use shell cron when jobs need to orchestrate multiple tools (pg_repack, pg_dump, external API calls). Use pg_timetable when you need DAG-style job chains with retry logic, conditional execution, or rich timezone support within the database.

Key Takeaways

Working with JusDB on PostgreSQL Automation

Deploying pg_cron is straightforward, but building a reliable automation layer around PostgreSQL maintenance requires more than a few scheduled SQL statements. At JusDB, we help engineering and SRE teams design, implement, and monitor database automation strategies that scale with their workloads.

Our PostgreSQL engagements routinely include:

• Auditing existing pg_cron schedules for security posture (superuser exposure, excessive privilege) and reliability (error alerting, history retention).
• Designing partition maintenance pipelines that combine pg_cron with pg_partman for fully automated range partition creation and detachment.
• Building materialized view refresh strategies that balance data freshness against query concurrency and I/O load.
• Setting up job monitoring dashboards that surface cron.job_run_details failures in your existing observability stack (Datadog, Grafana, PagerDuty).
• Advising on pg_cron vs pg_timetable vs external scheduler architectures for complex multi-step database workflows.

Whether you are migrating from shell cron jobs to in-database scheduling or designing automation for a greenfield PostgreSQL deployment on Aurora or Supabase, we bring production-grade patterns and hard-won operational experience to the table.

Explore our PostgreSQL services or get in touch to discuss your automation requirements.

Related reading

• PostgreSQL Declarative Partitioning with pg_partman
• PostgreSQL Autovacuum Tuning and Table Bloat
• PostgreSQL Extensions Guide: pg_stat_statements and pg_trgm