pg_cron: Schedule PostgreSQL Tasks Inside the Database Without OS Cron

The Problem That Finally Broke My Patience

Three years ago, I was managing a PostgreSQL cluster for a SaaS product. We had cron jobs scattered across two application servers. One ran VACUUM ANALYZE every night. Another archived records older than 90 days into a cold storage table.

A third sent alerts when tables crossed a row threshold. Everything worked until we migrated to a blue-green deployment setup and someone forgot to update the cron jobs on the new server. The VACUUM job kept running on the dead server. Nobody noticed for two weeks, and table bloat killed query performance right before a major client demo.

I’ve worked with MySQL, PostgreSQL, and MongoDB across different projects. Each has genuine strengths. But what PostgreSQL lets you do with extensions — things you can’t get anywhere near with the others — is what keeps me coming back. The moment I discovered pg_cron, that whole “cron jobs living on the app server” pattern felt like a bad habit I was finally ready to drop.

Root Cause: Why OS Cron Is the Wrong Layer for This

Scheduling a VACUUM or archival query from the OS means managing a database concern at the infrastructure layer. That mismatch creates real problems that stack up over time:

• Visibility gap: There’s no record inside PostgreSQL that the job ran, failed, or skipped. You end up hunting through server logs or the cron daemon’s mail output.
• Deployment drift: Migrate servers, scale horizontally, or move to containers — OS cron jobs break silently unless someone explicitly remembers to carry them over.
• Credential sprawl: Every cron script needs a connection string with database credentials sitting in a shell script or environment file on the server.
• No transactional awareness: OS-level scripts fire on a timer with no knowledge of database state — current load, active locks, or whether the previous run even finished.

Moving this responsibility into the database fixes all four at once.

Comparing the Options

Option 1: OS-Level Cron

Works fine for simple single-server setups. Falls apart when you scale, migrate, or containerize. PostgreSQL has zero visibility into whether anything ran, and there’s no clean way to track job history inside the database.

Option 2: Application Schedulers (Celery Beat, APScheduler, etc.)

Better than OS cron — jobs are managed in code and versioned alongside the application. But you’re still running database maintenance from outside the database engine, and you’ve added another component that needs to stay running, get deployed, and get monitored separately.

Option 3: pg_cron

A PostgreSQL extension that runs scheduled SQL jobs inside the database process as a background worker. Jobs are stored in a table, run history is queryable, and there are zero external dependencies. For database-specific tasks, this is the right layer.

Installing and Configuring pg_cron

On Ubuntu/Debian with PostgreSQL 16:

sudo apt install postgresql-16-cron

On RHEL/Rocky/AlmaLinux:

sudo dnf install pg_cron_16

Tell PostgreSQL to load the extension on startup by editing postgresql.conf:

# /etc/postgresql/16/main/postgresql.conf
shared_preload_libraries = 'pg_cron'
cron.database_name = 'your_database_name'

Restart PostgreSQL, then create the extension inside your target database:

sudo systemctl restart postgresql

CREATE EXTENSION pg_cron;

A background worker starts up and the cron schema becomes available. Setup done.

Practical Use Cases

1. Automated VACUUM on High-Write Tables

PostgreSQL’s autovacuum handles most tables just fine. High-write tables with bulk deletes or mass updates are a different story — they can generate dead tuples faster than autovacuum’s cost-based throttling cleans them up. For those, a scheduled VACUUM at a predictable off-peak time is more reliable.

-- Run VACUUM ANALYZE on the orders table every day at 2:00 AM
SELECT cron.schedule(
  'vacuum-orders',
  '0 2 * * *',
  'VACUUM ANALYZE orders;'
);

The cron syntax is standard POSIX: minute, hour, day-of-month, month, day-of-week. Once the job is registered, check it and its history right from SQL:

-- List all scheduled jobs
SELECT jobid, jobname, schedule, command FROM cron.job;

-- Check recent run history
SELECT jobid, status, return_message, start_time, end_time
FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 10;

cron.job_run_details is what makes pg_cron operationally useful. Did the job succeed? How long did it take? Did it silently error last Tuesday at 2 AM? All of that is a SQL query. No log file archaeology.

2. Archiving Old Data on a Schedule

Say you have an events table and anything older than 90 days should move to events_archive. Wrap the logic in a PL/pgSQL function, then schedule it:

CREATE OR REPLACE FUNCTION archive_old_events() RETURNS void AS $$
BEGIN
  INSERT INTO events_archive
  SELECT * FROM events
  WHERE created_at < NOW() - INTERVAL '90 days';

  DELETE FROM events
  WHERE created_at < NOW() - INTERVAL '90 days';

  RAISE NOTICE 'Archived events older than 90 days at %', NOW();
END;
$$ LANGUAGE plpgsql;

-- Run every Sunday at 3:00 AM
SELECT cron.schedule(
  'archive-old-events',
  '0 3 * * 0',
  'SELECT archive_old_events();'
);

Here’s what a shell script can’t give you: if the INSERT succeeds but the DELETE fails, nothing commits. No half-archived tables, no orphaned records to hunt down. The function runs inside PostgreSQL’s transaction system, so it either fully succeeds or fully rolls back.

3. Sending Internal Notifications with pg_notify

PostgreSQL has a built-in pub/sub system via NOTIFY and LISTEN. Schedule a job that checks a condition and fires a channel notification to any listening application process — no external message queue needed:

CREATE OR REPLACE FUNCTION check_pending_order_alert() RETURNS void AS $$
DECLARE
  row_count bigint;
BEGIN
  SELECT COUNT(*) INTO row_count
  FROM orders
  WHERE status = 'pending';

  IF row_count > 10000 THEN
    PERFORM pg_notify(
      'alerts',
      json_build_object(
        'type', 'high_pending_orders',
        'count', row_count,
        'timestamp', NOW()
      )::text
    );
  END IF;
END;
$$ LANGUAGE plpgsql;

-- Check every 15 minutes
SELECT cron.schedule(
  'check-pending-orders',
  '*/15 * * * *',
  'SELECT check_pending_order_alert();'
);

Your application subscribes with LISTEN alerts; on a persistent connection and reacts as notifications arrive. Useful for threshold alerts that need to be database-driven without pulling Kafka or Redis into your stack for this one use case.

Managing Jobs Day-to-Day

-- List all jobs with their schedules
SELECT jobid, jobname, schedule, active FROM cron.job;

-- Disable a job without removing it
UPDATE cron.job SET active = false WHERE jobname = 'archive-old-events';

-- Remove a job permanently
SELECT cron.unschedule('archive-old-events');
-- Or by job ID
SELECT cron.unschedule(3);

What Actually Holds Up in Production

Running pg_cron across several PostgreSQL deployments over time surfaces some patterns that separate the jobs that quietly work from the ones that quietly break:

• Wrap everything in functions, not raw SQL strings. Scheduling SELECT my_function(); instead of inline SQL makes jobs testable outside the scheduler, versionable in migrations, and much easier to debug when something fails at 3 AM.
• Alert on cron.job_run_details failures. Add a monitoring query that flags failed or suspiciously long-running jobs. Treat pg_cron failures the same way you’d treat application errors — with alerts, not manual log checks a week later.
• Don’t fight autovacuum, supplement it. pg_cron VACUUM jobs work best for tables with known bulk write patterns at predictable times. Let autovacuum handle routine maintenance everywhere else.
• Write idempotent functions. Archive and cleanup functions should produce the same result whether they run once or twice. Jobs can overlap or get retried on restarts.
• Use cron.database_name carefully. pg_cron runs jobs in the database specified in postgresql.conf. Need jobs across multiple databases? Use cron.schedule_in_database(), introduced in pg_cron 1.4.

After switching away from OS cron, the thing that stood out most was simple: database maintenance finally lived inside the database. Not in a shell script on a server someone might forget to update. Not in a deployment checklist that falls through the cracks during a migration. Job history is a SQL query, jobs travel with the database, and when something goes wrong, you find out in seconds — not after an hour of log spelunking.