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: retries fire automatically when the endpoint returns 5xx, the failure email contains the actual SQL error in the response body (not "exit code 1"), and the same setup is portable across Neon, Supabase, Crunchy, RDS, and self-hosted Postgres. Pro is $3.25/mo annual flat for unlimited HTTP schedules at minute cadence on a 1-minute floor, so you do not run out of schedules when the maintenance list grows.

Run Postgres maintenance from outside the database. Free forever tier with one schedule. 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 with backoff, and the failure email carries the SQL error string.

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.