Postgres Scheduled Job: A Practical Guide to pg_cron and External Schedulers

Postgres Scheduled Job: What It Means and Why It Matters

A postgres scheduled job is any task that runs automatically inside or alongside a PostgreSQL database on a defined timetable. Common examples include nightly data cleanups, materialized view refreshes, aggregation rollups, and email or webhook notifications triggered by database state. Because the scheduler lives close to the data, these jobs avoid network hops, reduce race conditions, and keep logic out of application code where it is harder to audit. Whether you run a single instance or a cluster, a reliable scheduler turns ad hoc maintenance into repeatable, observable operations.

The right approach depends on your workload frequency, environment constraints, and operational preferences. PostgreSQL itself has evolved from offering nothing beyond the basic cron binary on the host to supporting a first-class, SQL-accessible scheduler via the pg_cron extension. External tools remain valuable when you need cross-service orchestration, dependency graphs, or richer retry semantics. This guide covers the core patterns, setup steps, and trade-offs so you can choose a postgres scheduled job strategy that fits your team.

Using pg_cron for In-Database Scheduling

pg_cron is a lightweight extension that runs inside the PostgreSQL cluster, leveraging a background worker to execute SQL or shell commands on schedule. It stores jobs in a system catalog, which means you can create, list, and remove them with standard SQL. Because the scheduler is part of the database process, it automatically respects database restarts, failovers, and connection pools in managed environments.

Key Features of pg_cron

• Jobs are defined with a cron-like schedule expression and a SQL command string.
• Each job runs with the privileges of a designated database role, making permission boundaries explicit.
• Execution logs are queryable through system views, giving you visibility without external log scraping.
• It supports both SQL statements and calls to external shell scripts via the database server host.

Setting Up a Basic Job

First, ensure the pg_cron extension is available in your cluster. In cloud-managed services like Google Cloud SQL and Azure Database for PostgreSQL, it is often exposed as a flag or built-in option. On self-managed servers, add shared_preload_libraries = 'pg_cron' to postgresql.conf and restart the instance. Then create the extension in your target database, and register a job:

CREATE EXTENSION pg_cron; SELECT cron.schedule('nightly vacuum', '0 3 * * *', 'VACUUM ANALYZE large_table');

The schedule follows standard five-field cron syntax. You can pass parameters safely using the $$ quoting mechanism, and you can schedule multiple jobs with different roles and intervals. To monitor them, query the cron.job and cron.job_run_details views, which show the last run time, exit status, and any error output.

When to Use an External Scheduler Instead

pg_cron excels for database-local tasks, but a postgres scheduled job is not always best kept inside the database. If your workflow spans multiple services, requires complex retry logic, or depends on external APIs, an external scheduler often provides better observability and control. External schedulers also avoid tying up database worker processes for long-running operations.

Common External Options

• System cron or systemd timers: Simple and universal. A script invoked by cron can use psql to run SQL over a local or remote connection, giving you shell-level flexibility.
• Airflow, Prefect, or Dagster: Python-native orchestrators that let you define dependencies between tasks, retry on failure, and visualize runs in a UI.
• Cloud-native schedulers: AWS EventBridge, GCP Cloud Scheduler, or Azure Logic Apps can trigger containerized functions or Lambda-like workers that interact with the database.

Choose external tools when you need to coordinate a postgres scheduled job with non-database steps, such as calling an API after a table is refreshed, sending alerts on failure, or running a multi-step ETL pipeline that touches several systems.

Comparing In-Database and External Scheduling

The decision hinges on where you want to manage state and failure handling. The table below summarizes the main trade-offs.

Best Practices for Reliable Postgres Scheduled Jobs

Regardless of where the scheduler lives, a postgres scheduled job should be idempotent, observable, and bounded in resource usage. Idempotency prevents duplicate work if a run overlaps or retries unexpectedly. Use advisory locks or conditional INSERT/UPDATE patterns to make jobs safe to rerun. Observability means capturing the outcome of each run in a dedicated log table or relying on the scheduler's built-in history, and alerting on repeated failures or long runtimes. Resource bounding is especially important for vacuum operations, index builds, and large DELETEs; use WHERE clauses to limit batch sizes, and avoid scheduling heavy maintenance during peak traffic.

For pg_cron specifically, assign a dedicated role with only the permissions the job needs, and avoid running untrusted shell commands. If you move logic into external scripts, store connection credentials securely, rotate them regularly, and test failover scenarios so that a database restart or primary promotion does not leave your jobs orphaned. A well-designed postgres scheduled job runs quietly, reports clearly, and recovers gracefully when something goes wrong.