Postgres cron jobs: pg_cron, scheduled queries, and external triggers (2026)

Postgres ships without a built-in scheduler, so once a week somebody asks the same question: how do I run nightly VACUUM ANALYZE on this 200GB orders table, roll a monthly partition at 00:05 on the 1st, refresh a materialized view every hour, or archive soft-deleted rows every Sunday at 03:00? There are four credible answers in 2026, and which one fits depends on whether you can install extensions, whether you already run a worker, and whether you want retries when the SQL fails. This guide walks all four with code on Postgres 16 and 17.

Path 1: pg_cron extension

pg_cron is the in-database scheduler. It adds a cron.job table and a background worker that runs SQL at the cron interval you set. Where it is available:

• Supabase: enabled by default on every project.
• Neon: supported, run CREATE EXTENSION pg_cron; after enabling it in the dashboard.
• Crunchy Bridge: enabled, runs in the postgres database.
• Amazon RDS and Aurora: supported, but you must add pg_cron to shared_preload_libraries via a custom parameter group and reboot.
• AlloyDB: supported, enable on the cluster.
• Self-managed: install the package (postgresql-16-cron on Debian), add it to shared_preload_libraries, restart.
• Heroku Postgres: not available. You need Path 2, 3, or 4.

Syntax:

SELECT cron.schedule('nightly-vacuum', '0 2 * * *', 'VACUUM ANALYZE my_table');
SELECT cron.schedule('refresh-mv', '5 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY hourly_metrics');

Limits worth knowing up front: jobs run only in the database that hosts pg_cron (usually postgres), so cross-database calls need dblink or postgres_fdw. There is no native retry. There is no native alert when a job fails. Failures land in cron.job_run_details and in pg_log, which means you still need an alerting pipeline on top.

Path 2: A long-running connection runner

When pg_cron is unavailable, most teams roll a small worker that holds a pool and runs SQL on a timer. Node with node-cron, Python with APScheduler, Go with robfig/cron. The shape:

import cron from "node-cron";
import { Pool } from "pg";
 
const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 4 });
 
cron.schedule("0 2 * * *", async () => {
  const client = await pool.connect();
  try {
    await client.query("VACUUM ANALYZE my_table");
  } finally {
    client.release();
  }
});

Be honest about the trade. You pay for an always-on dyno just to hold the timer, and if you scale the worker to 2+ replicas every job double-fires unless you elect a single leader (Redis lock, advisory lock, ZooKeeper, take your pick). Deploys that restart the box can also drop a tick.

Path 3: Platform schedulers calling SQL

Cloud Scheduler triggers a Cloud Run service that opens a pg pool and runs the query. EventBridge fires a Lambda with a pg client. Vercel cron hits a Next.js route handler that runs the SQL through Drizzle or Prisma. These shift the always-on cost into per-invocation billing, which is great when the workload is small or bursty. Watch for IAM (the runner needs network reach to the database, Cloud SQL Auth Proxy or a VPC connector), cold starts on the first call of the day, and per-platform timezone quirks. The conceptual sibling story lives in Firebase scheduled functions: you are wiring a cloud timer to a piece of code that talks SQL.

Path 4: External HTTP cron hitting a /cron/run endpoint

This is the Crontap pattern. Deploy a small HTTP route, protect it with a bearer token, and let an external cron service hit it on cadence.

app.post("/cron/run/nightly-vacuum", async (req, res) => {
  if (req.headers.authorization !== `Bearer ${process.env.CRON_SECRET}`) {
    return res.status(401).send();
  }
  try {
    await pool.query("VACUUM ANALYZE my_table");
    res.status(204).send();
  } catch (err) {
    res.status(500).send(err.message);
  }
});

Why this beats pg_cron for many cases: failed runs are retried automatically with exponential backoff before you get alerted, and the same setup is portable across Neon, Supabase, Crunchy, RDS, and self-hosted Postgres. Pro starts at $2.99/mo and scales as you grow, with a 1-minute cadence floor, so you have headroom as the maintenance list grows.

Run Postgres maintenance from outside the database. Free Starter tier with 2 combined schedules, uptime monitors, and heartbeats. Includes 1 uptime monitor at daily cadence and 1 heartbeat at hourly cadence or slower. Try Crontap →

Safe-overlap with advisory locks

Cron fires whether or not yesterday's run finished. If nightly VACUUM ANALYZE on a 200GB table runs long, the next tick should not pile up behind it. Guard with a session-scoped advisory lock at the top of the SQL:

DO $$
BEGIN
  IF NOT pg_try_advisory_lock(hashtext('nightly-vacuum')) THEN
    RAISE NOTICE 'another run is in progress, exiting clean';
    RETURN;
  END IF;
  VACUUM ANALYZE my_table;
  PERFORM pg_advisory_unlock(hashtext('nightly-vacuum'));
END $$;

pg_try_advisory_lock returns FALSE instantly when the lock is held, so the runner exits with a clean success and Crontap does not retry a "stuck" job into a stampede.

FAQ

When should I prefer pg_cron over external HTTP cron?

When the SQL is short, the host supports it, and the job does not need cross-region alerting. For longer jobs, anything that should retry on failure, or anything where you need the SQL error string in a Slack message, prefer Path 4.

Can I run pg_cron on managed Postgres hosts?

Supabase, Neon, Crunchy Bridge, RDS, Aurora, and AlloyDB all support it. Heroku Postgres does not. On RDS you must enable it through a custom parameter group and reboot.

How do I get an alert when a scheduled SQL fails?

pg_cron writes to cron.job_run_details but does not page anyone. With Path 4 the endpoint returns 5xx, Crontap retries automatically with exponential backoff, and alerts if the retries are used up.

What about Postgres + Supabase + Edge Functions?

Schedule the Edge Function from Crontap, let the function run the SQL through supabase-js. See the sibling Supabase cron jobs guide for the full walkthrough.

Related on Crontap

• Supabase cron jobs. pg_cron and Edge Functions side by side.
• Cron troubleshooting. Decision tree when a scheduled job does not fire.
• Cron job monitoring. Alerts, retries, and failure visibility for any HTTP target.
• Neon status. Live status of a popular pg_cron host.
• Data warehouse freshness. The closest spoke for scheduled SQL.