Burying batch jobs inside PostgreSQL with pg_cron eliminates the need for an external scheduler.

When running a side project, you find yourself clearing expired sessions every morning, aggregating revenue every hour, and cleaning up accumulated audit logs every week. In a team project, add Materialized View refreshes, payment failure alerts, and partition maintenance on top of that. There have typically been three ways to handle this: attaching a script to OS crontab, adding a scheduled trigger to AWS Lambda or Cloud Functions, or spinning up an orchestrator like Airflow. I started with one of these three every time, and every time I asked myself, "Is this really the best way?"

pg_cron solves this problem at the database layer. It's an extension that schedules SQL commands directly inside PostgreSQL using cron expressions — no separate daemon, no server, no IAM roles. Because job definitions live inside the DB, they're automatically included in backups and survive failover intact.

In this article, we'll look at why pg_cron is getting attention again right now, when you should use it and when you shouldn't, along with the most commonly used patterns and their code.

Why pg_cron Is Getting Attention Again

The Hidden Costs of External Schedulers

crontab-based scripts are simple, but they have problems. You have to manage DB credentials somewhere, job definitions scatter across different servers, and unless you log failures yourself, you have no idea when something went wrong.

Lambda and Cloud Functions are even more complex. You have to deploy function code separately, you need VPC configuration for DB access, and cold starts can make the first execution slow. Airflow is powerful, but it becomes an operational target in itself.

pg_cron follows the simple principle of "scheduling SQL with SQL," without any of these additional costs.

Becoming the Standard in Managed DBs

As of 2026, AWS RDS/Aurora PostgreSQL, Supabase, Neon, Aiven, pgEdge, and Google Cloud SQL all officially support pg_cron. The minimum engine version supported in RDS PostgreSQL can be checked in the AWS official documentation, and in most managed services it can be enabled with just one or two parameter group settings. The impression of it being a "minor plugin" can probably be set aside now.

Installation: Simpler Than You'd Think

For self-hosted PostgreSQL, add one line to postgresql.conf and restart.

# postgresql.conf
shared_preload_libraries = 'pg_cron'
 
# Optional: specify the database pg_cron will use (default: postgres)
cron.database_name = 'myapp'

After restarting, activate the extension.

CREATE EXTENSION pg_cron;
 
-- Must be run in the DB that the pg_cron worker uses
-- To grant job registration privileges to other DB users:
GRANT USAGE ON SCHEMA cron TO myapp_user;

For managed environments like AWS RDS or Supabase, configure the parameter group in the console, reboot, then run CREATE EXTENSION pg_cron;.

Code for Common Scenarios

Data Cleanup: The First Job You'll Add

-- Delete expired session tokens every day at 2 AM
SELECT cron.schedule(
  'expire-sessions',
  '0 2 * * *',
  $$DELETE FROM user_sessions WHERE expires_at < now()$$
);
 
-- Retain audit logs for 90 days, every Sunday at 1 AM
SELECT cron.schedule(
  'purge-audit-logs',
  '0 1 * * 0',
  $$DELETE FROM audit_log WHERE created_at < now() - interval '90 days'$$
);
 
-- Clean up general logs older than 30 days
SELECT cron.schedule(
  'cleanup-old-logs',
  '0 0 * * *',
  $$DELETE FROM user_logs WHERE created_at < now() - interval '30 days'$$
);

If cron expressions are confusing, remember the order: minute hour day month weekday. I used to mix up whether 0 2 * * * meant "2:00 AM or 0:02 AM," but remembering that the leftmost field is minutes solved it.

Materialized View Refresh: The Key to Dashboard Performance

-- Refresh sales summary view every 5 minutes (minimize locks with CONCURRENTLY)
SELECT cron.schedule(
  'refresh-sales-summary',
  '*/5 * * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary'
);
 
-- Refresh dashboard stats every 15 minutes
SELECT cron.schedule(
  'refresh-dashboard',
  '*/15 * * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY dashboard_stats'
);
 
-- Aggregate hourly sales every hour on the hour (upsert approach)
SELECT cron.schedule(
  'aggregate-hourly-sales',
  '0 * * * *',
  $$
    INSERT INTO sales_hourly (hour, total_amount, order_count)
    SELECT date_trunc('hour', created_at), SUM(amount), COUNT(*)
    FROM orders
    WHERE created_at >= now() - interval '2 hours'
    GROUP BY 1
    ON CONFLICT (hour) DO UPDATE
      SET total_amount = EXCLUDED.total_amount,
          order_count  = EXCLUDED.order_count
  $$
);

To use REFRESH MATERIALIZED VIEW CONCURRENTLY, the view must have a unique index. If you run CONCURRENTLY without a unique index, PostgreSQL does not silently fall back — it returns an ERROR. This means the job will fail on every execution, so either add a unique index first, or if the view can't have one, register it as a separate job without CONCURRENTLY and schedule it with lock contention in mind.

Notification Triggers: Publishing Events Directly from the DB

-- Batch welcome emails for users who signed up the previous day (every day at 9 AM)
SELECT cron.schedule(
  'send-welcome-emails',
  '0 9 * * *',
  $$
    SELECT notify_new_users(user_id)
    FROM users
    WHERE created_at::date = current_date - 1
      AND welcome_sent = false
  $$
);
 
-- Check for payment failures every 10 minutes
SELECT cron.schedule(
  'alert-failed-payments',
  '*/10 * * * *',
  'CALL send_payment_failure_alerts()'
);

Functions like notify_new_users() or send_payment_failure_alerts() are defined as DB functions, and a common pattern is to connect them to external systems by calling pg_notify inside them or inserting records into an outbox table.

Outbox Pattern + pg_cron: Ensuring Reliable Event Delivery

In microservice environments, the Transactional Outbox pattern combined with pg_cron is widely used when you want reliable event delivery without a separate Kafka or SQS.

One point worth noting here: because pg_cron is a pure SQL executor, it cannot send HTTP requests directly. The path to External System in the diagram below requires choosing one of two options: (1) send HTTP from inside the DB using the pg_net extension, or (2) have an external application poller read from the outbox. The diagram assumes an external poller approach.

If you want to send HTTP directly from within the DB, the form would be calling pg_net inside process_outbox_batch(), in which case the pg_cron + pg_net combination can handle it without a poller.

-- Process outbox batch every 30 seconds (pg_cron 1.4+)
SELECT cron.schedule(
  'process-outbox',
  '30 seconds',
  'CALL process_outbox_batch()'
);

Sub-minute intervals can be specified with an integer between 1 and 59, like '30 seconds'. However, this sub-minute syntax is only supported from pg_cron 1.4, so it will produce a syntax error on older versions. It's worth checking the pg_cron version provided by your managed service first.

Automating DB Maintenance

-- VACUUM ANALYZE on key tables (every Saturday at 3 AM)
-- Multi-table VACUUM syntax is officially supported in PostgreSQL 14+
-- For 13 and below, split into per-table jobs or use a DO $$ ... $$ block
SELECT cron.schedule(
  'weekly-vacuum',
  '0 3 * * 6',
  'VACUUM ANALYZE orders, order_items, products'
);
 
-- Connection status snapshot (every 5 minutes)
SELECT cron.schedule(
  'snapshot-connections',
  '*/5 * * * *',
  $$
    INSERT INTO conn_stats
    SELECT now(), count(*), state
    FROM pg_stat_activity
    GROUP BY state
  $$
);
 
-- Auto-clean cron execution history (every day at midnight)
-- If you don't register this job, job_run_details will grow indefinitely
SELECT cron.schedule(
  'purge-cron-history',
  '0 0 * * *',
  $$DELETE FROM cron.job_run_details WHERE end_time < now() - interval '7 days'$$
);

Honestly, I once forgot to register that last purge-cron-history job when I first set up pg_cron, and later discovered the cron.job_run_details table had quietly grown large. It's a good idea to register it together at install time.

Job Monitoring: Inspecting Execution History with SQL

One of the most convenient things about pg_cron is that all execution history lives inside the DB.

-- Check recently failed jobs
SELECT jobid, runid, return_message, start_time
FROM cron.job_run_details
WHERE status = 'failed'
ORDER BY start_time DESC;
 
-- Detect jobs that haven't succeeded in over a day (when a job quietly dies)
SELECT j.jobname, max(d.end_time) AS last_success
FROM cron.job j
LEFT JOIN cron.job_run_details d
  ON d.jobid = j.jobid AND d.status = 'succeeded'
GROUP BY j.jobname
HAVING max(d.end_time) < now() - interval '1 day'
    OR max(d.end_time) IS NULL;
 
-- View list of registered jobs
SELECT jobid, jobname, schedule, command, active
FROM cron.job
ORDER BY jobname;

Connect these queries to a monitoring dashboard or alert system and it's not hard to receive a Slack notification when a particular job hasn't succeeded in over a day.

Trade-offs: Where pg_cron Fits and Where It Doesn't

Pros and Cons at a Glance

Tool Selection Guide

Scheduler Comparison

Common Mistakes in Practice

1. Attaching heavy ETL to pg_cron pg_cron is suited for lightweight SQL operations. If a batch processing millions of records takes longer than the next execution interval, a second instance starts in parallel, potentially causing lock contention on the same table or exhausting the max_running_jobs limit. In these cases, it's better to split the batch into smaller chunks run multiple times, or consider an external orchestrator from the start.

2. Not registering the job_run_details cleanup job As mentioned earlier, this job must be registered together. Skip it and the history table grows quietly.

3. Timezone confusion pg_cron operates on UTC by default. If you want something to run at 9 AM Korean time, you need to write 0 0 * * * (UTC 0:00 = KST 9:00) in the cron expression. It's safer not to rely on the server timezone setting.

Closing Thoughts

pg_cron is a simple idea: "bring what was outside into the DB." That simplicity reduces operational complexity and gives job definitions the same lifecycle as your data.

To summarize: pg_cron does its job best with simple, repetitive SQL tasks — data cleanup, Materialized View refreshes, statistics aggregation, DB maintenance, and lightweight notification triggers. Conversely, for complex pipelines with DAG dependencies, multi-system orchestration, or situations requiring sophisticated retry policies, it's right to look at Airflow or Temporal from the start.

If you're already using PostgreSQL, check whether pg_cron is sufficient before adding an external scheduler.

References

• GitHub - citusdata/pg_cron — Official source code and README
• pg_cron - PostgreSQL Extension Analysis, CMU VLDB 2025
• Scheduling maintenance with pg_cron - Amazon RDS Docs
• The pg_cron extension - Neon Docs
• Postgres as a CRON Server - Supabase Blog
• How to Schedule Jobs in PostgreSQL with pg_cron - freeCodeCamp
• Evolution of PostgreSQL Job Schedulers — PGConf 2025
• PostgreSQL schedulers: comparison table - CYBERTEC
• Using and monitoring pg_cron - Postgres Hashnode
• How to Use pg_cron in Cloud SQL PostgreSQL (2026) - OneUptime