PostgreSQL + pg_cron: Scheduling Jobs Inside Postgres

If you run PostgreSQL in production or in your homelab, you invariably end up needing recurring background tasks: vacuuming bloated tables, refreshing materialized views, purging expired sessions, or archiving audit logs.

Traditionally, people solve this with external tooling: Linux crontab, Celery/Redis workers, or Kubernetes CronJob pods. But having external runners requires managing credentials, network endpoints, and separate failure modes.

The pg_cron extension solves this cleanly by running the scheduler directly inside the PostgreSQL engine itself as a background worker process.

[!NOTE] Key Takeaways

• Self-Contained Scheduling: pg_cron operates inside PostgreSQL as a background worker, removing the need for external cron daemons, Kubernetes CronJobs, or credential-holding runner scripts.
• Standard Cron Syntax: Schedules jobs using standard 5-part cron expressions (min hour day month day-of-week) or interval shortcuts (@hourly, @daily).
• Zero-Network Overhead: Maintenance queries and materialized view refreshes run locally in-memory without network socket hops or connection timeout disconnects.

1. Enabling pg_cron

Because pg_cron runs as an internal background worker, it must be loaded when PostgreSQL starts.

Add it to your postgresql.conf:

# postgresql.conf
shared_preload_libraries = 'pg_cron'

# Database where pg_cron stores its metadata and job tables
cron.database_name = 'postgres'

# Optional: set timezone for cron scheduling (defaults to GMT/UTC)
cron.timezone = 'UTC'

Restart your PostgreSQL instance, then enable the extension in the designated database:

-- Connect to the cron database
\c postgres

CREATE EXTENSION pg_cron;

2. Scheduling Practical Jobs

The syntax is straightforward: cron.schedule(job_name, cron_expression, sql_command).

A. Nightly Table Vacuuming & Maintenance

To run a full VACUUM ANALYZE every night at 3:00 AM UTC:

SELECT cron.schedule(
    'nightly-vacuum',
    '0 3 * * *',
    'VACUUM ANALYZE;'
);

B. Periodic Materialized View Refreshes

If you maintain materialized reporting views that power application dashboards, refresh them concurrently every 15 minutes:

SELECT cron.schedule(
    'refresh-analytics-views',
    '*/15 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_active_user_metrics;'
);

C. Automated Data Retention & Log Purging

Purge soft-deleted rows or temporary audit logs older than 30 days every Sunday at midnight:

SELECT cron.schedule(
    'purge-expired-audit-logs',
    '0 0 * * 0',
    $$DELETE FROM audit_logs WHERE created_at < NOW() - INTERVAL '30 days';$$
);

(Tip: Use dollar-quoting $$ for commands containing quotes or semicolons.)

3. Monitoring & Managing Jobs

pg_cron maintains two essential system tables in the cron schema:

View Active Jobs

SELECT jobid, schedule, command, nodename, username, active
FROM cron.job;

Check Execution History & Status

Inspect the last 10 executions to verify success or diagnose failure error messages:

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

Unschedule or Delete a Job

You can remove a job by name or by its jobid:

-- Unschedule by name
SELECT cron.unschedule('refresh-analytics-views');

-- Or by job ID
SELECT cron.unschedule(1);

4. Best Practices & Caveats

1. Keep Jobs Short or Concurrent: Long-running transactions hold locks. Always use REFRESH MATERIALIZED VIEW CONCURRENTLY (which requires a unique index on the view) to prevent blocking read queries during refreshes.
2. Purge the Run Log: cron.job_run_details records every single execution. If you run a job every minute, schedule a meta-job to truncate older run records:

   SELECT cron.schedule('cleanup-cron-history', '0 0 * * *', $$DELETE FROM cron.job_run_details WHERE start_time < NOW() - INTERVAL '7 days'$$);

3. Database Scoping: By default, jobs execute in the database defined by cron.database_name. To run a job in a different database within the same PostgreSQL cluster, supply the database parameter:

   SELECT cron.schedule_in_database('app-vacuum', '0 4 * * *', 'VACUUM;', 'app_production');

Conclusion

pg_cron keeps your database maintenance declarative, version-controlled, and self-contained within your database migrations. If your application doesn’t already have an asynchronous worker fleet, pg_cron is the cleanest way to handle recurring SQL jobs.