This leads them to add a line to their crontab, which gives them a sense of control. Now, their computer runs the script automatically while they sleep.

For a while, that's enough. Then it isn't.

Maybe the backup script failed silently at 3 a.m., and you didn't find out until you needed it later that day, or you developed the perfect script that ran smoothly in your terminal to find it failing when scheduled in cron.

If any of that sounds familiar, congratulations: you've outgrown cron. You're not alone, and this article aims to help you feel understood and ready for better solutions.

This article is about what comes next. We'll look at exactly where cron runs out of road, what 'workflow orchestration' actually means underneath the buzzword, and then build a real workflow step by step so the concepts stick.

By the end, you'll feel more confident and empowered to choose the right tools for complex scheduling challenges.

What Cron is, and What it's Genuinely Good at

Cron is the system background service (daemon) that runs scheduled tasks. Crontab (cron table) is the configuration file or command utility used to write and manage those task schedules' time-based job scheduler that has shipped with Unix-like systems since the 1970s. It's available on today’s Linux distributions as well as on Macs.

With cron, you specify a schedule and a command, and it runs the command at that time. The schedule uses the famous five-field syntax:

┌───────────── minute (0–59)
│ ┌───────────── hour (0–23)
│ │ ┌───────────── day of month (1–31)
│ │ │ ┌───────────── month (1–12)
│ │ │ │ ┌───────────── day of week (0–6)
│ │ │ │ │
0  9  *  1 5  /usr/bin/python3 /home/me/daily_report.py

Note: The line 0 9 * 1 5  /usr/bin/python3 /home/me/daily_report.py means "run daily_report.py at 9:00 a.m. on weekdays." The syntax is terse, ubiquitous, and (credit where it's due) rock-solid for what it does.

And here's the important part: cron is not bad. For a single, self-contained, failure-tolerant task on one machine, it's the right tool. But for more complex workflows, a purpose-built orchestration tool can provide the reliability and visibility you need, helping you feel more in control of your automation.

The Four Walls You'll Hit with Cron

The trouble with cron starts when your automation stops being a single self-contained task. As your scripts grow, they form more complex systems where you run into the same four limitations, more or less in this order.

Wall 1: Dependencies Between Steps

Your morning routine grows from one script into three. Consider an ETL scenario with three scripts:

1. extract.py pulls yesterday's orders from an API.

2. transform.py cleans the data and computes totals.

3. load.py writes the result into the analytics database.

Each step depends on the one before it. The obvious cron approach is to guess at the timing:

0 2 * * *  python extract.py
0 3 * * *  python transform.py
0 4 * * *  python load.py

You're now hoping the extract finishes within an hour, so that the transform has something to work with. On the day the API is slow, and extract takes 70 minutes, transform runs against stale or missing data and quietly produces garbage. Cron has no concept of "run B only after A succeeds." It only knows wall-clock time.

Wall 2: Failure Handling and Retries

Networks blip. APIs return 503 status codes (Service Unavailable). Databases drop connections. A robust job needs to detect a failure and retry. Maybe three times, or with increasing delays between attempts, so you don't hammer a struggling service.

With cron, retry logic is your problem. You end up bolting it onto every script by hand: try/except blocks, sleep calls, a counter, and a flag file so the next cron tick knows whether the previous one finished. Multiply that across a dozen jobs, and you've written a small, buggy, undocumented orchestration engine.

Wall 3: Visibility

Ask yourself, did your jobs run last night? Which ones succeeded? How long did each take? Is the slowdown in the extract step or the load step?

With cron, the honest answer is "I'd have to go read some log files, if the script even wrote any." The first task is finding the cron job log files themselves.

Finding them can be complicated: cron's logs can be in /var/log/syslog on one system and /var/log/cron on another, assuming logging is enabled. Meanwhile, your script's output only exists if you explicitly redirect stdout and stderr. As a result, you have to search through system logs to verify the job ran, then locate any separate output files that captured its print statements.

When a job fails, identifying the issue is even harder, as you must sift through interleaved logs from multiple runs, trying to determine which timestamp corresponds to the last execution and where it went wrong, often relying only on a non-zero exit code as a clue. There's no dashboard, no run history, no record of how long things took, and no alert when something breaks.

Failures are silent by default, the single most dangerous property a background job can have. You find out your pipeline has been broken for a week when someone downstream notices the numbers stopped updating.

Wall 4: Backfills and Re-runs

Your analytics database has been live for two months when you discover a bug in transform.py that miscalculated totals. You've fixed the code. Now you need to rerun the pipeline for each day in those two months, processing its own slice of data.

This is a backfill, and with cron, it's a nightmare. Cron only ever runs "now." There's no built-in notion of "run this job as if it were March 14th, then March 15th, then..." So you write yet another throwaway script with a date loop, pray it's idempotent, and babysit it.

Notice the pattern across all four walls: each time, you end up reimplementing something poorly that a category of tools already solves well. That category is workflow orchestration.

Can Workflow Orchestration Save the Day?

A workflow orchestrator manages workflows, which are sets of tasks with specific relationships, triggers, and failure protocols, along with observability for tracking outcomes.

While many solutions like Airflow, Dagster, Prefect, and Temporal are available, we'll focus on Kestra, an open-source orchestrator. Kestra stands out from the pack by enabling developers to run, monitor, and manage workflows all from a single declarative layer compatible with any programming language and infrastructure, including public, private, or even air-gapped networks.

Kestra also provides enterprises with the control needed to ensure insights and regulatory compliance. With over 1,600 connectors available, you can build almost any data, infrastructure, or AI workflow you can imagine.

In this tutorial, we'll continue with the cron scheduler theme and build a simple ETL workflow that uses a cron-like schedule while addressing some of cron's shortcomings, such as execution sequence and error handling.

Kestra Primer

Kestra was born out of the engineering pain of creating Python code to achieve proper workflow orchestration. Rather than spend engineering hours crafting the right Python code for your Apache Airflow DAG, with Kestra, your workflow is a YAML file. Simply describe what should run in plain, declarative syntax. Store the file in Git, and deploy it like any other code.

There's no obscure UI logic building and no hidden state management. Just simple text that can be easily reviewed and diffed. The workflow you read is the workflow that runs.

The example YAML file in Figure 1 illustrates a scenario in which you want to move NoSQL data into an analytics-ready warehouse.

id: cassandra-to-bigquery
namespace: company.team

tasks:
  - id: query_cassandra
    type: io.kestra.plugin.cassandra.Query
    session:
      endpoints:
        - hostname: localhost
          port: 9042
      localDatacenter: datacenter1
    cql: |
      SELECT salary_id, work_year, experience_level, employment_type,
      job_title, salary, salary_currency, salary_in_usd, employee_residence,
      remote_ratio, company_location, company_size
      FROM test.salary
    fetchType: STORE

  - id: write_to_csv
    type: io.kestra.plugin.serdes.csv.IonToCsv
    from: "{{ outputs.query_cassandra.uri }}"

  - id: load_bigquery
    type: io.kestra.plugin.gcp.bigquery.Load
    from: "{{ outputs.write_to_csv.uri }}"
    destinationTable: my_project.my_dataset.my_table
    serviceAccount: "{{ secret('GCP_SERVICE_ACCOUNT_JSON') }}"
    projectId: my_project
    format: CSV
    csvOptions:
      fieldDelimiter: ","
      skipLeadingRows: 1

Figure 1: Cassandra to BigQuery Example

Without even knowing much about Kestra, the YAML file is simple and self-explanatory. Later in this post, we'll create a basic ETL flow and explain the significance of fields such as id and type. But first, let’s get an instance of Kestra running on your local machine.

How to Set Up Kestra

Kestra is available as an open-source platform licensed under the Apache 2.0 license as well as an Enterprise offering additional features and product support. In this tutorial, we'll use Docker to run Kestra locally using the latest version.

To spin up Kestra, run the following Docker command:

docker run --pull=always --rm -it -p 8080:8080 
  --user=root \
  --name kestra \
  -v kestra_data:/app/storage \
  -v kestra_db:/app/data \
  -v /var/run/docker.sock:/var/run/docker.sock \
  -v /tmp:/tmp \
  kestra/kestra:latest server local

For other platforms such as Windows and Linux check out https://kestra.io/get-started.

Once the containers are loaded, navigate to the Kestra UI at http://localhost:8080. The welcome screen will ask you to create an administrator. Create the user and finish the initial launch wizard.

Once that's complete, navigate to the Flows tab in the left panel, then click the Create button at the top right of the page. This will create a new flow using a sample template, as shown in the following figure:

Figure 2: Flows page showing the new flow template

Flows in Kestra

In Kestra you define your workflow orchestration through Flows. You can create these Flows using YAML syntax in the UI, through a no-code editor in the UI, or programmatically through an API. In this tutorial, we'll create flows using YAML.

In Figure 2 above, you'll notice that a sample flow is already created to get you started.

The _id, namespace, and tasks are three required fields and are used to identify the flow within the Kestra environment and the task the flow should execute. Each flow lives in one namespace. Namespaces are like folders in a filesystem and are used to group flows and provide structure. Note that you can't change a flow’s namespace after creation.

How to Use Kestra

Step 1: A Simple Task Executed on a Schedule

Let's start by erasing the sample flow provided and replacing it with the following:

id: morning_report
namespace: tutorial

tasks:
  - id: say_hello
    type: io.kestra.plugin.core.log.Log
    message: "Good morning — the pipeline ran at {{ execution.startDate }}"

This example has the required id, namespace, and has one tasks field that logs a message. The message displayed uses a pebble expression (the command inside the  {{ }} brackets) to show the startDate of the execution.

Pebble expressions are used to set values dynamically in flows. In this example, the start date of the execution will be inserted within the string.

To execute this flow, we need to first save it by clicking the Save button in the upper-right corner of the page. Once saved, click the Play button. You'll then be presented with the execute options page, as shown in Figure 3 below:

Figure 3: Execute flow options

If this workflow had inputs such as a filename or URL, you can manually enter them here to test your flow. The modal also provides the curl command if you’d like to run the flow via the API instead of the UI. Click the Execute button to run the flow:

Figure 4: Flow execution log

The flow was simple and wrote the info message to the log file. If the flow had errors or warnings, you'd be able to see detailed execution logs on this page.

Now that we’ve created our first task, let’s schedule it with a cron mask. Add the following to the flow by clicking on the “Edit Flow” button at the top of the page.

Next, add the triggers section to the flow:

triggers:
  - id: every_minute
    type: io.kestra.plugin.core.trigger.Schedule
    cron: "* * * * *"

Execute the flow.  After a few minutes, check out the execution history by clicking on the “Execution” tab on the left side navbar.

Figure 5: Flow execution page

Our flow now behaves similarly to a single task cron job. The flow has a triggers block with a Schedule trigger whose cron field is the exact same five-field syntax you already know. That last point matters: you're not throwing away what you learned. You're wrapping it in something that can grow as your needs change.

Notice that even in this simple Kestra example, you’ve gained something cron didn't offer. Every time this runs, it's recorded as an execution with a timestamp, a duration, and a status, and all logs are visible in the UI. That's Wall 3 (visibility) handled before we've even done anything interesting.

Step 2: Real Work, and a Dependency

Now let's replace the simple task with the three-step extract/transform/load and have the orchestrator enforce the ordering rather than timing offsets.

id: csv_to_parquet
namespace: company.team
description: Download orders CSV, transform it with a Python script, and write the result to a Parquet file.

tasks:
  # Download a public CSV file into Kestra's internal storage
  - id: download_csv
    type: io.kestra.plugin.core.http.Download
    uri: https://huggingface.co/datasets/kestra/datasets/raw/main/csv/orders.csv

  # Transform the CSV with a simple Python script and write it out as Parquet
  - id: transform_to_parquet
    type: io.kestra.plugin.scripts.python.Script
    containerImage: ghcr.io/kestra-io/pydata:latest
    inputFiles:
      input.csv: "{{ outputs.download_csv.uri }}"
    outputFiles:
      - orders.parquet
    script: |
      import pandas as pd

      # Read the downloaded CSV
      df = pd.read_csv("input.csv")

      # --- simple transformation ---
      # Ensure numeric types and add a computed column
      df["total"] = df["quantity"] * df["price"]

      # Keep only orders above a small threshold as an example filter
      df = df[df["total"] > 0]

      print(f"Rows after transform: {len(df)}")

      # Write the result to Parquet
      df.to_parquet("orders.parquet", index=False)

  # Log that the Parquet file was produced
  - id: log_output
    type: io.kestra.plugin.core.log.Log
    message: "Parquet file created: {{ outputs.transform_to_parquet.outputFiles['orders.parquet'] }}"
# Expose the Parquet file as a downloadable flow output.
# FILE-typed flow outputs appear on the execution's Overview tab with a download button.
outputs:
  - id: parquet_file
    type: FILE
    value: "{{ outputs.transform_to_parquet.outputFiles['orders.parquet'] }}"

Save the flow, then execute.

Figure 6: Execution results

Two things just happened. First, tasks listed in sequence run in sequence. Transform_to_parquet only starts after download_csv succeeds, and log_output only after transform_to_parquet succeeds. If a failure occurs, the execution stops, and transform_to_parquet never touches stale data. That's Wall 1 (dependencies) gone, with no guesswork about timing.

Second, notice the expression within the flow {{ outputs.download_csv.uri }}. Tasks can pass data and metadata to subsequent tasks using expressions like this. That wiring turns a list of scripts into an actual pipeline.

Step 3: Surviving Failure with Retries

Now consider the scenario where the download_csv task encounters a network issue, and the flow is unable to download the latest data. Let's make that task resilient declaratively by adding a retry section to the download_csv task:

- id: download_csv
    type: io.kestra.plugin.core.http.Download
    uri: https://huggingface.co/datasets/kestra/datasets/raw/main/csv/orders.csv
    retry:
      type: constant
      maxAttempts: 5
      interval: PT10S

That's the whole retry policy. If the download fails, Kestra waits and tries again up to 5 times, with a 10-second delay (PT10S is ISO-8601 for "10 seconds"). There are no counters, sleep calls, or flag files. Wall 2 (failure handling) is handled in 3 lines that read like a sentence.

To test this, drop the “s” from orders.csv and rerun the flow. You can see the execution showing retrying.

Figure 7: Execution showing retry

If all attempts fail, you'll probably want to be notified. Let’s add a flow-level error handler that runs only when something in the workflow fails:

errors:
  - id: notify_failure
    type: io.kestra.plugin.notifications.slack.SlackIncomingWebhook
    url: "{{ secret('SLACK_WEBHOOK') }}"
    payload: |
      {"text": "orders_pipeline failed on execution {{ execution.id }}"}

Now a broken pipeline pings a Slack channel instead of failing silently at 3 a.m. Note that secrets are protected within flows via the secret expression.

Step 4: Triggering on Events, Not Just Time

Schedules are only one kind of trigger. Suppose orders don't arrive on a fixed timetable. Instead, a file lands in cloud storage whenever an upstream system feels like it.

Polling on a cron schedule ("check every 5 minutes, exit if nothing's there") is wasteful and laggy. Event triggers are the better model: run the workflow when the thing happens.

Conceptually, instead of adding a scheduled trigger similar to the one added in the previous example:

triggers:
  - id: every_minute
    type: io.kestra.plugin.core.trigger.Schedule
    cron: "* * * * *"

You can add a trigger that fires when a new object appears in an S3 bucket:

triggers:
  - id: new_s3_object
    type: io.kestra.plugin.aws.s3.Trigger
    interval: "PT1M"
    accessKeyId: "{{ secret('AWS_ACCESS_KEY_ID') }}"
    secretKeyId: "{{ secret('AWS_SECRET_KEY_ID') }}"
    region: "eu-central-1"
    bucket: "my-bucket"
    prefix: "incoming/"
    on: CREATE
    action: NONE

Alternatively, you could have a trigger that exposes a webhook URL you can POST to start execution, or real-time triggers that listen to streaming services such as Kafka queues. In any of these trigger scenarios, the workflow body stays identical. Your automation can now respond to the world rather than just watching the clock.

Step 5: Backfilling the Past

Finally, consider the bug-in-transform scenario. You've fixed the calculation and need to re-run the pipeline for every day of the last two months. This would be painful in cron.

In an orchestrator, a backfill is a first-class operation on a scheduled workflow: you pick a start and end date, and it generates one execution per scheduled interval across that range. Each execution is aware of the date it represents, through an expression like {{ trigger.date }}. Your transform step can use that date to fetch and process the correct slice of data.

This is where idempotency stops being academic. Because a backfill re-runs days you may have already processed, your data load step should write with an "insert or replace" semantic keyed on the date, so running March 14th, for example, twice leaves the database in the same state as running it once.

Design for that and backfills become routine instead of terrifying. Wall 4 (backfills) is handled, but only if you've held up your end with idempotent tasks.

Where to Go Next

The best way to internalize all of this is to take an existing cron job and rebuild it as a proper workflow. Start with the one-task version, confirm it runs and appears in the run history, then add a second dependent task, a retry policy, and a failure alert.

Each step maps to one of the four walls, and you'll feel the difference immediately the first time a task fails, retries itself, and recovers without waking you up.

You don't need to write every line from scratch. Kestra provides a library of Blueprints that are hundreds of ready-made, copy-pasteable flows that you can browse at kestra.io/blueprints or access directly in your instance under the Blueprints tab.

Each Blueprint is a complete, executable example with an explanation of its functionality and how to extend it. This allows you to start with a close version of your goal and modify it rather than guessing at the syntax.

Cron showed that computers can operate while you're asleep. Orchestration takes it further, ensuring they do so reliably, in order, and visibly. This shift transforms the idea from merely writing a script to managing a full system.

The usual answer is to reach outside the database: a system crontab, a Kubernetes CronJob, a Celery beat worker, or a scheduler service. All of these work, but they add moving parts. Now you have credentials to manage, a separate process to monitor, and one more thing that can silently stop running.

pg_cron takes a different approach. It's a PostgreSQL extension that runs a cron-style scheduler inside the database itself. You schedule jobs with plain SQL, the database executes them, and the run history lands in a table you can query like anything else.

In this tutorial, you'll learn how pg_cron works, how to install and configure it, and how to use it for real maintenance tasks. You'll also learn how to monitor jobs, manage permissions, and decide when pg_cron is the right tool — and when it isn't.

Table of Contents

• Prerequisites

• What Is pg_cron?

• How pg_cron Works

• How to Install and Set Up pg_cron

• A Quick Refresher on Cron Syntax

• How to Schedule Your First Job

• Practical pg_cron Examples

• How to View and Monitor Your Jobs

• How to Update and Remove Jobs

• How to Run Jobs in Other Databases

• How to Let Other Users Schedule Jobs

• When to Use pg_cron (and When to Avoid It)

• Best Practices for Working with pg_cron

• Conclusion

Prerequisites

To follow along with the examples, you'll need:

• Basic knowledge of SQL (SELECT, INSERT, UPDATE, DELETE)

• A running PostgreSQL instance (version 13 or later is ideal, though pg_cron supports version 10 and up)

• Superuser or admin access to that instance, since installing the extension requires it

• A SQL client like psql, pgAdmin, or DBeaver

If you don't run your own server, that's fine too. Most managed PostgreSQL services — including Amazon RDS, Azure Database for PostgreSQL, Google Cloud SQL, Supabase, and Neon — support pg_cron. I'll cover how to enable it on those later in the tutorial.

What Is pg_cron?

pg_cron is an open source PostgreSQL extension, originally built by the team at Citus Data, that lets you schedule SQL commands using the familiar cron syntax.

Instead of writing a crontab entry on a server, you write a SQL statement:

SELECT cron.schedule(
  'nightly-cleanup',
  '0 3 * * *',
  $$DELETE FROM sessions WHERE expires_at < now()$$
);

That single statement tells PostgreSQL to delete expired sessions every day at 3 AM. No external process, no shell script, no extra credentials. The job definition lives in the database, version-controlled alongside your migrations if you want it to be.

Because the scheduler is just another extension, your jobs travel with the database. Anyone who can connect and query can see exactly what's scheduled, when it last ran, and whether it succeeded.

How pg_cron Works

When PostgreSQL starts with pg_cron enabled, the extension launches a background worker. This worker has one job: watch the cron.job table, which holds every scheduled job along with its schedule, command, target database, and the user it runs as.

When a job's scheduled time arrives, the worker executes the command. By default it does this by opening a new local connection to the database, just as your application would. You can also configure it to use PostgreSQL background workers instead of connections, which I'll show you in the setup section.

Two behaviors are worth knowing up front:

First, pg_cron can run many different jobs in parallel, but it never runs two instances of the same job at once. If a job is still running when its next scheduled time arrives, the new run waits in a queue and starts as soon as the current one finishes. This protects you from a slow cleanup job piling up on top of itself.

Second, pg_cron doesn't run jobs while a server is in hot standby mode. If you use streaming replication, jobs only execute on the primary. When a replica gets promoted, the scheduler starts up automatically — so failover doesn't leave you without your scheduled jobs.

How to Install and Set Up pg_cron

Setting up pg_cron on a self-managed server takes three steps: install the package, update the configuration, and create the extension.

Step 1: Install the Package

On Debian or Ubuntu using the official PostgreSQL apt repository, install the package that matches your PostgreSQL major version. For PostgreSQL 17, that's:

sudo apt-get install postgresql-17-cron

On Red Hat-based systems using the PGDG yum repository:

sudo yum install pg_cron_17

If you're on PostgreSQL 16 or 18, swap the version number accordingly. You can also build the extension from source if your platform doesn't have a package.

Step 2: Update postgresql.conf

pg_cron needs to start its background worker when PostgreSQL boots, so it must be preloaded. Add it to shared_preload_libraries in your postgresql.conf:

shared_preload_libraries = 'pg_cron'

If that setting already lists other libraries, add pg_cron to the comma-separated list rather than replacing them.

By default, the scheduler stores its metadata in the database named postgres. If your application lives in a different database and you want the jobs there, set:

cron.database_name = 'app_db'

One more setting worth knowing: pg_cron interprets all schedules in GMT by default. If you want your "3 AM cleanup" to actually run at 3 AM local time, set the timezone explicitly:

cron.timezone = 'Africa/Lagos'

These settings require a server restart to take effect:

sudo systemctl restart postgresql

Step 3: Create the Extension

Connect to the database you configured in cron.database_name and create the extension as a superuser:

CREATE EXTENSION pg_cron;

This creates the cron schema, the metadata tables, and the scheduling functions. You're ready to schedule jobs.

Note that pg_cron can only be installed in one database per PostgreSQL instance. That sounds limiting, but it isn't. You can still run jobs in any database on the instance using cron.schedule_in_database(), which we'll cover later.

A Note on How Jobs Connect

Since pg_cron opens local connections by default, your pg_hba.conf needs to allow them. The common approaches are enabling trust authentication for localhost connections for the job's user, or putting the password in a .pgpass file.

If you'd rather avoid connection authentication entirely, tell pg_cron to use background workers instead:

cron.use_background_workers = on
max_worker_processes = 20

With background workers, the number of jobs that can run concurrently is capped by max_worker_processes, so raise it if you schedule a lot of overlapping jobs.

Using pg_cron on Managed Database Services

If you're on a managed service, you usually can't edit postgresql.conf directly, but the providers expose the same settings through their own mechanisms:

• Amazon RDS and Aurora PostgreSQL: add pg_cron to the shared_preload_libraries parameter in your DB parameter group, reboot the instance, then run CREATE EXTENSION pg_cron; as a user with rds_superuser. The scheduler runs in the postgres database.

• Azure Database for PostgreSQL: enable pg_cron under server parameters (shared_preload_libraries and azure.extensions), restart, then create the extension.

• Google Cloud SQL: set the cloudsql.enable_pg_cron flag, restart, then create the extension.

• Supabase: enable the pg_cron extension with a single toggle in the dashboard under Database → Extensions.

• Neon: pg_cron is available as a supported extension you can enable per project.

The SQL you write afterward is identical everywhere, which is part of the appeal.

A Quick Refresher on Cron Syntax

pg_cron uses the same five-field schedule format as classic Unix cron:

┌──────────── minute (0–59)
│ ┌────────── hour (0–23)
│ │ ┌──────── day of month (1–31, or $ for the last day)
│ │ │ ┌────── month (1–12)
│ │ │ │ ┌──── day of week (0–6, Sunday = 0)
│ │ │ │ │
* * * * *

An asterisk means "every value". You can combine values with commas, ranges with hyphens, and steps with slashes. Some schedules you'll use constantly:

*/5 * * * *    every 5 minutes
0 * * * *      every hour, on the hour
0 3 * * *      every day at 3:00 AM
0 3 * * 1-5    3:00 AM on weekdays
30 1 * * 0     1:30 AM every Sunday
0 0 1 * *      midnight on the 1st of each month

pg_cron also adds two extensions to the standard syntax that regular cron doesn't have.

You can use $ in the day-of-month field to mean the last day of the month, which is genuinely painful to express in standard cron:

0 23 $ * *     11:00 PM on the last day of every month

And for jobs that need to run more often than once a minute, you can use a plain interval between 1 and 59 seconds:

'30 seconds'   every 30 seconds

The seconds syntax stands alone — you can't mix it with the five-field format.

If you ever doubt what a schedule means, crontab.guru translates cron expressions into plain English. Just remember that pg_cron evaluates schedules in the timezone set by cron.timezone, which defaults to GMT.

How to Schedule Your First Job

The core function is cron.schedule(). It comes in two forms: one with a name and one without.

The named form is the one you should use, because names make jobs easy to find, update, and remove:

SELECT cron.schedule(
  'delete-expired-sessions',                          -- job name
  '0 3 * * *',                                        -- schedule
  $$DELETE FROM sessions WHERE expires_at < now()$$   -- command
);

The function returns the job's ID:

 schedule
----------
        1
(1 row)

A few details worth noticing:

The command is wrapped in $$ ... $$, PostgreSQL's dollar quoting. This saves you from escaping the single quotes inside the SQL. For commands without quotes, regular string literals work fine.

The job runs in the database where you called cron.schedule(), as the user you called it with, using that user's normal permissions. There's no privilege escalation hiding in the scheduler — if your user can't delete from sessions, neither can the job.

And if you call cron.schedule() again with the same job name, pg_cron updates the existing job instead of creating a duplicate. That makes schedules idempotent, which is handy if you define jobs inside database migrations.

Practical pg_cron Examples

Let's walk through the patterns that cover most real-world use. Each example is something you can adapt directly.

Example 1: Clean Up Old Rows Every Night

Tables that collect transient data — sessions, tokens, audit events, notification logs — grow forever unless something prunes them. A nightly delete is the classic first pg_cron job:

SELECT cron.schedule(
  'purge-old-events',
  '0 2 * * *',
  $$DELETE FROM events WHERE created_at < now() - interval '90 days'$$
);

Every night at 2:00 AM, rows older than 90 days disappear. If the table is large, consider batching the delete inside a function so each run stays short, then schedule the function instead.

Example 2: Refresh a Materialized View Every Hour

Materialized views are a great way to cache expensive aggregations, but PostgreSQL never refreshes them on its own. pg_cron fixes that:

SELECT cron.schedule(
  'refresh-daily-sales',
  '5 * * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary'
);

This refreshes the view at five minutes past every hour. The CONCURRENTLY option lets reads continue during the refresh, as long as the view has a unique index.

Example 3: Build a Daily Summary Table

Rollup tables are another common pattern: instead of aggregating millions of raw rows on every dashboard load, you precompute the numbers once a day.

SELECT cron.schedule(
  'rollup-daily-orders',
  '15 0 * * *',
  $$
  INSERT INTO daily_order_stats (day, order_count, total_amount)
  SELECT created_at::date, count(*), sum(amount)
  FROM orders
  WHERE created_at >= current_date - 1
    AND created_at < current_date
  GROUP BY created_at::date
  ON CONFLICT (day) DO UPDATE
    SET order_count = EXCLUDED.order_count,
        total_amount = EXCLUDED.total_amount
  $$
);

At fifteen minutes past midnight, yesterday's orders get summarized into one row. The ON CONFLICT clause makes the job safe to re-run — if it executes twice, it overwrites rather than duplicates.

Example 4: Run a Job Every 30 Seconds

Some work needs to happen more often than cron's one-minute floor allows: flushing a buffer table, picking up rows from an outbox, advancing a lightweight queue. The seconds syntax handles this:

SELECT cron.schedule(
  'process-outbox',
  '30 seconds',
  'CALL process_outbox_batch()'
);

Remember the guarantee from earlier: pg_cron won't start a second instance of this job while the first is still running. If a batch occasionally takes 45 seconds, the next run simply waits its turn instead of stampeding.

Example 5: Run Maintenance on the Last Day of the Month

Month-end jobs are awkward in standard cron because months have different lengths. pg_cron's $ makes it trivial:

SELECT cron.schedule(
  'month-end-vacuum',
  '0 23 $ * *',
  'VACUUM ANALYZE orders'
);

This runs VACUUM ANALYZE on the orders table at 11:00 PM on the 28th, 29th, 30th, or 31st — whichever happens to be the last day of that month.

How to View and Monitor Your Jobs

Everything pg_cron knows lives in two tables in the cron schema, and you query them like any other tables.

To see what's scheduled, look at cron.job:

SELECT jobid, jobname, schedule, command, active
FROM cron.job;

 jobid |         jobname         |  schedule  |            command             | active
-------+-------------------------+------------+--------------------------------+--------
     1 | delete-expired-sessions | 0 3 * * *  | DELETE FROM sessions WHERE ... | t
     2 | refresh-daily-sales     | 5 * * * *  | REFRESH MATERIALIZED VIEW ...  | t
(2 rows)

To see how jobs have actually been running, query cron.job_run_details:

SELECT jobid, status, return_message, start_time, end_time
FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 10;

Each row records one execution: whether it succeeded or failed, the message it returned, and exactly when it started and ended. A failed job shows status = 'failed' along with the error message, so debugging usually starts and ends with this table.

One important catch: pg_cron never cleans this table up by itself. A job running every 30 seconds writes almost three thousand rows a day. The standard fix is delightfully recursive — schedule a pg_cron job to prune pg_cron's own history:

SELECT cron.schedule(
  'purge-cron-history',
  '0 12 * * *',
  $$DELETE FROM cron.job_run_details
    WHERE end_time < now() - interval '14 days'$$
);

If you don't want run history recorded at all, set cron.log_run = off in your configuration.

How to Update and Remove Jobs

To change an existing job, use cron.alter_job() with the job's ID. Only the parameters you pass get changed — everything else stays as it was:

-- Move job 1 from 3 AM to 4 AM
SELECT cron.alter_job(1, schedule := '0 4 * * *');

-- Pause a job without deleting it
SELECT cron.alter_job(1, active := false);

-- Resume it later
SELECT cron.alter_job(1, active := true);

Pausing with active := false is underrated. During an incident or a big migration, you can switch off a noisy job and switch it back on afterward, without losing its definition.

To remove a job permanently, use cron.unschedule() with either the name or the ID:

SELECT cron.unschedule('delete-expired-sessions');
-- or
SELECT cron.unschedule(1);

Both return true when the job was found and removed.

How to Run Jobs in Other Databases

Remember that pg_cron is installed in exactly one database per instance, usually postgres. If your instance hosts several databases, you don't install pg_cron in each one — you schedule cross-database jobs from the one place it lives, using cron.schedule_in_database():

SELECT cron.schedule_in_database(
  'analytics-nightly-vacuum',
  '0 4 * * *',
  'VACUUM ANALYZE page_views',
  'analytics_db'
);

The job is stored centrally but executes inside analytics_db. The function also accepts an optional username if the job should run as a different user, and an active flag if you want to create it paused.

This pattern keeps all scheduling in one schema on one database, which makes auditing simple: a single SELECT * FROM cron.job shows every scheduled job across the whole instance.

How to Let Other Users Schedule Jobs

Out of the box, only superusers can call the scheduling functions. To let an application role manage its own jobs, grant it usage on the cron schema:

GRANT USAGE ON SCHEMA cron TO app_user;

The permission model after that is sensible and safe:

• Jobs run with the permissions of the user who scheduled them, nothing more.

• A row-level security policy on cron.job means users only see and modify their own jobs. Superusers see everything.

• Each user can also delete their own rows from cron.job_run_details, so the cleanup job from earlier works without superuser rights.

In practice, I recommend creating a dedicated role for scheduled work rather than scheduling jobs as a personal account. When the engineer who scheduled everything leaves and their role gets dropped, you don't want the nightly rollups going with them.

When to Use pg_cron (and When to Avoid It)

pg_cron shines when the work is database work. Use it for:

• Data retention: pruning old rows from sessions, logs, events, and token tables.

• Aggregations: refreshing materialized views and building rollup tables.

• Maintenance: targeted VACUUM ANALYZE, rebuilding statistics, managing partitions (it pairs beautifully with pg_partman).

• Lightweight pipelines: moving rows between tables, processing outbox patterns, expiring soft-deleted records.

The common thread: the entire job is expressible as SQL or a stored procedure, and it touches nothing outside the database.

You should reach for something else when:

• The job needs to call external systems. pg_cron runs SQL. It can't send an HTTP request, push to a queue, or send an email on its own. Jobs like that belong in your application or a workflow engine.

• You need retries, backoff, and alerting built in. pg_cron records failures but won't retry them or page you. For workflows that must complete, tools like Temporal or a proper job queue earn their complexity.

• The work is heavy and long-running. A four-hour batch job running inside your primary OLTP database competes with your application for CPU, memory, and locks. Schedule heavy compute elsewhere.

• Jobs need complex dependencies. "Run B only after A succeeds, then fan out to C and D" is orchestration. That's Airflow territory, not cron territory.

A reasonable rule of thumb: pg_cron replaces the crontab entry that used to run psql -c "..." on some forgotten server. It doesn't replace your job queue or your workflow orchestrator.

Best Practices for Working with pg_cron

A handful of habits will keep your scheduled jobs boring, in the best sense of the word:

Name every job: Anonymous jobs identified only by an ID are painful to manage six months later. Names also make cron.schedule() idempotent, which lets you define jobs safely in migrations.

Set the timezone deliberately: The default is GMT, and "why does the 3 AM job run at 4 AM?" is a rite of passage you can skip by setting cron.timezone on day one.

Keep individual runs short: Wrap big deletes in batched stored procedures. A job that finishes in seconds holds locks briefly and queues less behind itself.

Make jobs idempotent: Servers restart, and a job can fail halfway. Use ON CONFLICT, time-window predicates, and other patterns that make a re-run harmless.

Prune cron.job_run_details: Schedule the cleanup job from the monitoring section before the table grows large enough that you notice it the hard way.

Monitor for silence, not just failure: A failed run appears in job_run_details, but a job that stopped being scheduled at all leaves no trace. A periodic check that each critical job has a recent successful run catches both cases:

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;

Any job this query returns hasn't succeeded in over a day, and deserves a look.

Conclusion

pg_cron turns PostgreSQL into its own scheduler. You define jobs in SQL, the database runs them, and the entire system — definitions, history, failures — is visible through ordinary queries.

In this tutorial, you learned how the extension works under the hood, how to install it on your own servers and on managed services, how to write schedules (including pg_cron's seconds and last-day-of-month extensions), and how to apply it to the maintenance work every real database accumulates: pruning, rollups, refreshes, and vacuums. You also saw how to monitor jobs, manage permissions, and recognize the point where a real job queue or orchestrator becomes the better tool.

If your infrastructure currently has a lonely server whose only purpose is running psql from a crontab, you now know how to retire it.

Thanks for reading! I write about PostgreSQL and backend engineering. You can connect with me on LinkedIn and X.