Postgres Cron Jobs with pg_cron: 8 Practical Examples

To run a Postgres cron job, enable the pg_cron extension and call cron.schedule('job-name', '0 3 * * *', $$ your SQL $$). The schedule uses standard five-field cron syntax (minute, hour, day of month, month, day of week), and pg_cron also accepts intervals like '30 seconds'. The job then runs inside the database on that schedule, with no external server or crontab to maintain.

Below: the syntax in one table, eight real examples you can paste, how to check whether jobs ran, and the mistakes that cause silent failures. The examples use the pg_cron API and run on Postgres 17.

#Set up pg_cron

On a self-managed server, pg_cron needs to be in shared_preload_libraries and the server restarted, then:

create extension pg_cron;

On StitchDB, cron is ready in every project. Turn on pg_cron under Database, then Extensions in Studio, or run create extension pg_cron (it is enabled for you and reports that no CREATE EXTENSION is needed). You can then manage jobs in SQL or under Integrations, then Cron jobs (Cron docs).

#Schedule syntax

 ┌───────────── minute (0-59)
 │ ┌─────────── hour (0-23)
 │ │ ┌───────── day of month (1-31)
 │ │ │ ┌─────── month (1-12)
 │ │ │ │ ┌───── day of week (0-6, Sunday = 0)
 │ │ │ │ │
 * * * * *

Times are in UTC on StitchDB. If your users care about "9 AM in New York", convert to UTC and remember that daylight saving time shifts the local time by an hour twice a year.

The three functions you will use most:

-- create or replace (same name replaces the existing job)
select cron.schedule('job-name', '0 3 * * *', $$ select 1 $$);

-- change schedule or pause
select cron.alter_job((select jobid from cron.job where jobname = 'job-name'), schedule => '0 4 * * *');
select cron.alter_job((select jobid from cron.job where jobname = 'job-name'), active => false);

-- remove
select cron.unschedule('job-name');

Always name your jobs. Named jobs are easy to replace from migrations, and scheduling the same name again updates the job instead of creating a duplicate.

#Example 1: delete old rows every night

The most common job. Keep a table from growing forever:

select cron.schedule('purge-old-events', '0 3 * * *', $$
  delete from public.events where created_at < now() - interval '30 days'
$$);

Make sure created_at is indexed, or the delete scans the whole table every night. If the table gets millions of new rows per day, delete in batches (Example 7) or use partitions and drop whole partitions instead.

#Example 2: expire trials and sessions

Business rules that depend on time are easy to express in SQL:

select cron.schedule('expire-trials', '*/15 * * * *', $$
  update public.subscriptions
  set status = 'expired'
  where status = 'trialing' and trial_ends_at < now()
$$);

Running every 15 minutes means a trial ends at most 15 minutes late. If you need exact cutoffs, also check trial_ends_at in your access policies; the job then only tidies up the status.

#Example 3: hourly rollups for dashboards

Aggregating raw events into a summary table keeps dashboards fast. Combine with an upsert so re-running the job is safe:

create table public.hourly_signups (
  hour timestamptz primary key,
  signups int not null
);

select cron.schedule('rollup-signups', '5 * * * *', $$
  insert into public.hourly_signups (hour, signups)
  select date_trunc('hour', created_at), count(*)
  from public.profiles
  where created_at >= date_trunc('hour', now()) - interval '2 hours'
  group by 1
  on conflict (hour) do update set signups = excluded.signups
$$);

It runs at five past each hour and recomputes the last two hours, so late rows are counted. The upsert pattern is explained in Postgres upsert with ON CONFLICT.

#Example 4: refresh a materialized view

create materialized view public.top_products as
  select product_id, sum(qty) as sold
  from public.order_items
  where created_at > now() - interval '7 days'
  group by product_id;

create unique index on public.top_products (product_id);

select cron.schedule('refresh-top-products', '*/10 * * * *', $$
  refresh materialized view concurrently public.top_products
$$);

concurrently lets reads continue during the refresh, but it requires a unique index on the materialized view. Without concurrently, readers block until the refresh finishes. Note that materialized views have no Row Level Security, so do not expose one through the API if it holds per-user data.

#Example 5: call an Edge Function or webhook

pg_cron runs SQL, and pg_net sends HTTP from SQL, so together they trigger any external code on a schedule. This calls a StitchDB Edge Function every night with the service role key stored in Vault:

select cron.schedule('nightly-report', '0 3 * * *', $$
  select net.http_post(
    url := 'https://<project-ref>.p.stitchdb.com/functions/v1/nightly-report',
    headers := jsonb_build_object(
      'Authorization', 'Bearer ' || (select decrypted_secret from vault.decrypted_secrets where name = 'service_role_key'),
      'Content-Type', 'application/json'),
    body := '{}'::jsonb)
$$);

net.http_post is asynchronous: it queues the request and returns an id; the response shows up in net._http_response. The HTTP from SQL docs list the limits (timeout up to 30 seconds, 1 MB bodies, private addresses blocked). Never paste secrets into the job command itself; anyone who can read cron.job can see it.

#Example 6: work through a queue every 30 seconds

select cron.schedule('drain-emails', '30 seconds', $$
  select public.process_email_queue()
$$);

The function reads a batch from a pgmq queue, sends the messages and archives them. Sub-minute schedules are useful for near-real-time background work without a worker process. The full queue pattern is in Using Postgres as a job queue.

#Example 7: batch large deletes

A single delete of 10 million rows holds locks and generates a burst of WAL. Spread it out:

create or replace function public.purge_logs_batch()
returns void language plpgsql as $$
begin
  delete from public.logs
  where id in (
    select id from public.logs
    where created_at < now() - interval '90 days'
    order by id
    limit 10000
  );
end $$;

select cron.schedule('purge-logs', '* * * * *', 'select public.purge_logs_batch()');

Every minute it removes up to 10,000 old rows in a short transaction. Once it catches up, each run does almost nothing.

#Example 8: maintain partitions

For time-partitioned tables managed by pg_partman, schedule its maintenance so future partitions exist before data arrives and expired ones are dropped:

select cron.schedule('partman-maintenance', '*/15 * * * *', $$call partman.run_maintenance_proc()$$);

See Partitioning for the full setup.

#Check that jobs ran

-- jobs
select jobid, jobname, schedule, active from cron.job;

-- recent runs, newest first
select jobid, status, return_message, start_time, end_time
from cron.job_run_details
order by start_time desc
limit 20;

-- failed runs in the last day
select j.jobname, d.return_message, d.start_time
from cron.job_run_details d
join cron.job j using (jobid)
where d.status = 'failed' and d.start_time > now() - interval '1 day';

On StitchDB, Integrations, then Cron jobs in Studio shows the last 200 runs with status and output, and lets you create, edit, pause and delete jobs without SQL. To get alerted when a job does not run on time or fails, add a monitor for it in Job monitoring; it follows the job's own schedule and alerts by e-mail, webhook or Telegram.

#Limits on StitchDB

From the cron docs:

• Up to 20 jobs per project.
• A run that takes longer than 30 minutes is cancelled.
• Run history in cron.job_run_details is kept for 7 days.
• Jobs run in your project's database as your project role; cron.schedule_in_database only accepts your own database.
• Jobs move with your project when it changes server. Disabling pg_cron removes all jobs.

If you need more than 20 schedules, combine related work into one function that a single job calls.

#Common mistakes

• Overlapping runs. A job scheduled every minute that takes three minutes will overlap with itself unless your SQL guards against it. Use pg_try_advisory_xact_lock at the start of the function and return early if it is held.
• Timezone confusion. Schedules are UTC. 0 9 * * * is 9 AM UTC, not your local time.
• Long transactions. One huge statement holds locks for its whole run. Batch it (Example 7).
• Missing indexes. A cleanup job that filters on an unindexed column does a full scan every run. Check it in Query performance.
• Silent failures. Nobody reads job_run_details until something breaks. Add a monitor.
• Secrets in commands. Read them from Vault inside the command instead.

A guard against overlap:

create or replace function public.process_email_queue()
returns void language plpgsql as $$
begin
  if not pg_try_advisory_xact_lock(hashtext('process_email_queue')) then
    return;  -- previous run still busy
  end if;
  -- ... work ...
end $$;

#FAQ

#How do I schedule a Postgres function to run daily?

Enable pg_cron and run select cron.schedule('daily-job', '0 3 * * *', 'select public.my_function()');. That runs at 03:00 UTC every day.

#Can pg_cron run every few seconds?

Yes. Use an interval like '30 seconds' as the schedule. pg_cron accepts 1 to 59 seconds.

#How do I see pg_cron errors?

Query cron.job_run_details for rows with status = 'failed'; return_message holds the error. On StitchDB, Studio's Cron jobs page shows the recent runs, and Job monitoring can alert you.

#What timezone does pg_cron use?

On StitchDB, schedules are in UTC. Convert local times to UTC when you write the schedule.

#Can a cron job call an API?

Yes, combine it with pg_net: the job calls net.http_post or net.http_get, and the request is sent in the background.

Schedule your first job in minutes: read the cron docs and create a free project.