How to Schedule Queries in PostgreSQL

How do I schedule recurring SQL queries directly inside PostgreSQL?

Scheduling lets PostgreSQL run SQL statements automatically at defined times without manual execution.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why schedule queries in PostgreSQL?

Automated schedules generate reports, refresh aggregates, and purge old data without human intervention, saving time and preventing missed jobs.

Which extensions can I use?

pg_cron and pgAgent are the two most-used tools.pg_cron is lightweight and lives inside the database; pgAgent is part of pgAdmin and runs as an external service.

When should I choose pg_cron?

Choose pg_cron for cloud-hosted clusters (Amazon RDS, Crunchy Bridge) or when you need simple cron-style schedules and fewer dependencies.

When should I choose pgAgent?

Pick pgAgent for complex, conditional workflows, multi-step jobs, or when your organization already relies on pgAdmin.

How to install pg_cron?

1. CREATE EXTENSION pg_cron;
2. Set shared_preload_libraries = 'pg_cron' in postgresql.conf and restart.
3.Grant usage: GRANT USAGE ON SCHEMA cron TO reporting_role;

How do I create a scheduled job?

Use SELECT cron.schedule(schedule_name, cron_expression, sql_text[, run_as]);. The job runs under the specified role and logs to cron.job_run_details.

Can I modify or drop a job?

Update with cron.alter_job(jobid, new_schedule, new_command). Remove with cron.unschedule(jobid).

Best practices for scheduled queries

Scope each job to a low-privileged role, add explicit timeouts, and log results to a dedicated table for monitoring.

Why How to Schedule Queries in PostgreSQL is important

How to Schedule Queries in PostgreSQL Example Usage


-- Weekly inventory snapshot every Sunday at 03:00
SELECT cron.schedule(
  'weekly_inventory_snapshot',
  '0 3 * * 0',
  $$INSERT INTO inventory_snapshots (snapshot_date, product_id, stock)
    SELECT CURRENT_DATE, id, stock FROM Products;$$
);

How to Schedule Queries in PostgreSQL Syntax


-- Create a daily sales summary at 02:00 AM
SELECT cron.schedule(
  'daily_sales_summary',      -- job name
  '0 2 * * *',                -- cron expression
  $$INSERT INTO daily_sales
        (summary_date, total_orders, total_revenue)
    SELECT CURRENT_DATE - 1,
           COUNT(*),
           SUM(total_amount)
    FROM Orders
    WHERE order_date = CURRENT_DATE - 1;$$
);

-- View job metadata
SELECT * FROM cron.job;
-- View run history
SELECT * FROM cron.job_run_details WHERE jobid = 1 ORDER BY end_time DESC;

-- Change schedule to 01:30 AM
SELECT cron.alter_job(1, '30 1 * * *', NULL);

-- Remove job
SELECT cron.unschedule(1);

Common Mistakes

Using superuser for every job: This exposes the entire database. Instead, create a role with only the privileges needed for the query and set run_as to that role.
Forgetting shared_preload_libraries: pg_cron won’t load if you skip adding it to shared_preload_libraries and restarting. Always verify SHOW shared_preload_libraries; after changes.