Modern applications frequently require background jobs such as cleaning expired sessions, refreshing materialized views, archiving historical data, sending notifications, or generating reports. Traditionally, these tasks are executed using operating system cron jobs or external schedulers. PostgreSQL provides a much simpler alternative through pg_cron, an extension that allows scheduled SQL commands directly from the database itself. In this article, you’ll learn how pg_cron works, how to install and configure it, when it should (and should not) be used, best practices, and a complete working example.
1. Overview
Scheduling recurring jobs is a common requirement in production PostgreSQL databases to automate routine maintenance and operational tasks without manual intervention. Typical examples include deleting expired user sessions, archiving historical records, refreshing materialized views, updating analytics or summary tables, performing regular database maintenance operations, generating scheduled reports, and cleaning up temporary or stale data. Automating these repetitive tasks helps improve reliability, ensures consistent execution, reduces administrative overhead, and keeps the database performing efficiently.
Without pg_cron, recurring database tasks are typically scheduled using external tools such as Linux cron, Windows Task Scheduler, Kubernetes CronJobs, Apache Airflow, Jenkins, or application-level schedulers built into frameworks like Spring Boot or Quartz. While these solutions are effective, they require additional infrastructure, configuration, and maintenance. By allowing SQL commands to be scheduled and executed directly within PostgreSQL, pg_cron simplifies database automation and reduces operational complexity for SQL-centric workloads.
While external schedulers work well, they introduce additional infrastructure and operational complexity. pg_cron keeps scheduling logic inside PostgreSQL, making scheduled SQL jobs much easier to manage.
2. What is pg_cron?
pg_cron is an official PostgreSQL extension developed by Citus Data (now part of Microsoft). It enables PostgreSQL to execute SQL commands on a schedule using the familiar Unix cron syntax. Instead of relying on external scripts, jobs are stored inside PostgreSQL itself. A background worker continuously checks scheduled jobs and executes them at the appropriate time.
2.1 Key Features
- Native PostgreSQL extension
- Uses standard cron expressions
- Runs SQL statements directly
- Supports stored procedures and functions
- Supports one-time and recurring jobs
- Maintains job execution history
- Easy to monitor
2.2 How Does pg_cron Work?
Internally, pg_cron launches a background worker process when PostgreSQL starts. The worker periodically checks the scheduled jobs stored in the cron.job table. When the scheduled execution time arrives:
- The background worker checks pending jobs.
- A new database connection is created.
- The SQL statement is executed.
- The execution result is recorded.
- The worker waits for the next scheduled interval.
2.3 Cron Expression Format
| Field | Description | Example |
|---|---|---|
| Minute | 0-59 | 15 |
| Hour | 0-23 | 3 |
| Day of Month | 1-31 | * |
| Month | 1-12 | * |
| Day of Week | 0-6 | 1 |
2.4 When Should You Use pg_cron?
pg_cron is an ideal solution for automating recurring tasks that execute entirely within PostgreSQL. It is particularly well-suited for routine database maintenance and SQL-driven workloads, such as cleaning up obsolete or expired data, archiving historical records, refreshing materialized views, scheduling VACUUM operations, generating aggregated analytics, performing ETL processes within the database, executing stored procedures or functions, and periodically updating cache or summary tables. By keeping these operations inside PostgreSQL, pg_cron simplifies automation while eliminating the need for external scheduling tools.
2.5 When Should You Avoid pg_cron?
Although pg_cron is a powerful extension for scheduling SQL-based tasks within PostgreSQL, it is not designed to replace enterprise workflow orchestration platforms. It should be avoided for scenarios that require complex dependency management, automatic retries with exponential backoff, distributed workflows, integration with external services through REST APIs, email delivery, coordination across multiple applications or microservices, or long-running business processes. In such cases, dedicated workflow and scheduling solutions like Apache Airflow, Temporal, Quartz Scheduler, or Kubernetes CronJobs provide more advanced capabilities for orchestration, monitoring, error handling, and scalability.
2.6 Best Practices
To get the best results from pg_cron, follow a few proven best practices. Keep scheduled jobs short and efficient to minimize database resource usage, and encapsulate complex business logic within stored procedures or functions instead of embedding lengthy SQL statements directly in scheduled jobs. Avoid long-running transactions that can hold locks and impact concurrent operations, and ensure appropriate indexes exist for queries executed by scheduled tasks to maintain good performance. Regularly monitor job execution history to identify failures or performance issues, use descriptive job names for easier management, thoroughly test jobs manually before scheduling them, prevent overlapping executions for tasks that are not designed to run concurrently, log critical operations for auditing and troubleshooting, and grant only the minimum required database privileges to improve security.
2.7 Advantages of pg_cron
- Simple installation
- No external scheduler required
- Native PostgreSQL integration
- Easy monitoring
- Cron syntax is familiar to most administrators
- Supports stored procedures and SQL
- Centralized scheduling inside the database
2.8 Limitations of pg_cron
- Only schedules SQL commands.
- Not suitable for distributed workflows.
- Cannot orchestrate external services.
- Long-running jobs may consume database resources.
- Limited workflow capabilities compared to Airflow or Temporal.
2.9 Setting Up pg_cron in PostgreSQL Using Docker
If you’re running PostgreSQL inside Docker, you must use a PostgreSQL image that includes the pg_cron extension or build a custom image with the extension installed. In addition, PostgreSQL must be configured to preload the extension’s background worker by setting the shared_preload_libraries parameter. The following example demonstrates a complete Docker Compose configuration for PostgreSQL with pg_cron enabled.
2.9.1 Create a docker-compose.yml File
Create a Docker Compose file that starts a PostgreSQL container with the required configuration options for pg_cron.
version: '3.9'
services:
postgres:
image: postgres:16
container_name: postgres-pg-cron
restart: unless-stopped
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres
POSTGRES_DB: companydb
ports:
- "5432:5432"
command:
- postgres
- -c
- shared_preload_libraries=pg_cron
- -c
- cron.database_name=companydb
This configuration starts a PostgreSQL 16 container, preloads the pg_cron background worker during server startup, and configures the scheduler to execute jobs within the companydb database.
2.9.2 Start the Container
After creating the Docker Compose file, start the PostgreSQL container using Docker Compose.
docker compose up -d
This command downloads the required PostgreSQL image (if necessary) and starts the database container in detached mode.
2.9.3 Install the pg_cron Extension
Once the PostgreSQL server is running, connect to the target database and install the pg_cron extension.
CREATE EXTENSION pg_cron;
This command creates the extension in the current database and makes all pg_cron scheduling functions available.
2.9.4 Verify the Installation
You can verify that the extension has been installed successfully by querying PostgreSQL’s extension catalog.
SELECT extname FROM pg_extension;
The query lists all installed extensions for the current database.
extname --------- plpgsql pg_cron
If pg_cron appears in the output, the extension has been installed successfully and is ready to schedule SQL jobs.
2.9.5 Schedule a Test Job
Finally, create a simple scheduled job to verify that the scheduler is functioning correctly.
SELECT cron.schedule(
'test-job',
'* * * * *',
$$SELECT NOW();$$
);
This statement schedules a job named test-job that executes every minute and runs a simple SQL statement. If the job appears in the cron.job table and its execution history is recorded in cron.job_run_details, your pg_cron installation is working correctly inside the Docker container.
3. Code Example
In this example, we’ll build a simple automation workflow that removes expired user sessions every day at 2:00 AM using pg_cron. The example demonstrates how to create the required table, schedule the cleanup task, and verify that the scheduled job has been registered successfully.
3.1 Create Table
First, create a table to store user session information. Each session contains a unique identifier, the associated username, and an expiration timestamp that determines when the session becomes invalid.
CREATE TABLE user_sessions
(
session_id SERIAL PRIMARY KEY,
username VARCHAR(100),
expires_at TIMESTAMP
);
This statement creates the user_sessions table with an auto-generated primary key, a username column, and an expires_at column. The expiration timestamp will later be used by the scheduled job to identify and remove expired sessions.
3.2 Insert Sample Data
Next, insert some sample records into the table. Some sessions are intentionally expired while others remain active so that we can observe the cleanup process.
INSERT INTO user_sessions(username, expires_at)
VALUES
('alice', NOW() - INTERVAL '3 days'),
('bob', NOW() + INTERVAL '2 days'),
('charlie', NOW() - INTERVAL '5 days'),
('david', NOW() + INTERVAL '1 day');
This statement inserts four sample sessions. Alice and Charlie have expiration dates in the past, making them eligible for deletion, while Bob and David have future expiration dates and should remain in the table after the cleanup job executes.
3.3 Current Data
Before scheduling the cleanup task, query the table to verify the initial data that will be processed by the scheduled job.
SELECT * FROM user_sessions;
This query retrieves all records from the user_sessions table, allowing you to verify which sessions are currently expired and which are still active.
session_id | username | expires_at -----------+----------+------------------------ 1 | alice | 2025-07-10 2 | bob | 2025-07-15 3 | charlie | 2025-07-08 4 | david | 2025-07-14
The output shows that Alice and Charlie have already expired, whereas Bob and David are still active. These expired records will be removed once the cleanup procedure is executed.
3.4 Create Cleanup Procedure
Instead of embedding complex SQL directly into the scheduler, it is considered a best practice to encapsulate the cleanup logic inside a stored procedure that can be executed manually or by pg_cron.
CREATE OR REPLACE PROCEDURE cleanup_expired_sessions()
LANGUAGE plpgsql
AS
$$
BEGIN
DELETE
FROM user_sessions
WHERE expires_at < NOW();
END;
$$;
This stored procedure deletes every session whose expiration timestamp is earlier than the current time. Encapsulating the logic inside a procedure makes the scheduled job easier to maintain, test, and extend in the future.
3.5 Schedule the Job
With the cleanup procedure in place, schedule it to run automatically every day at 2:00 AM using the cron.schedule() function.
SELECT cron.schedule(
'cleanup-expired-sessions',
'0 2 * * *',
'CALL cleanup_expired_sessions();'
);
The cron.schedule() function creates a recurring job named cleanup-expired-sessions. The cron expression 0 2 * * * schedules the procedure to execute daily at 2:00 AM, ensuring expired sessions are removed automatically without manual intervention.
3.6 View Scheduled Jobs
After scheduling the task, verify that it has been successfully registered by querying the pg_cron metadata table.
SELECT * FROM cron.job;
This query returns all jobs currently managed by pg_cron, including their unique job identifiers, execution schedules, and the SQL commands that will be executed.
jobid | schedule | command ------+----------+------------------------------- 1 | 0 2 * * * | CALL cleanup_expired_sessions();
The output confirms that the scheduled job has been created successfully. PostgreSQL will now invoke the cleanup_expired_sessions() procedure automatically every day at 2:00 AM according to the configured cron schedule.
3.7 Verify the Results After Job Execution
Once the scheduled job runs at its configured time, the cleanup procedure is executed automatically by pg_cron. The procedure removes all expired sessions, leaving only the active sessions in the table. You can verify the results by querying the table again.
SELECT * FROM user_sessions;
This query retrieves the remaining records after the scheduled cleanup job has completed, allowing you to confirm that only non-expired sessions are retained.
session_id | username | expires_at -----------+----------+------------------------ 2 | bob | 2025-07-15 4 | david | 2025-07-14
The output shows that the expired sessions belonging to Alice and Charlie have been deleted automatically by the scheduled job, while the active sessions for Bob and David remain unchanged. This demonstrates how pg_cron can automate routine database maintenance tasks without requiring manual intervention or external scheduling tools.
3.8 Unschedule the Job
If the scheduled task is no longer required, you can remove it from the pg_cron scheduler using the cron.unschedule() function. Once removed, PostgreSQL will stop executing the job according to its cron schedule.
SELECT cron.unschedule('cleanup-expired-sessions');
This statement removes the job named cleanup-expired-sessions from the scheduler. The function returns a boolean value indicating whether the job was successfully removed.
unschedule ----------- true
The output value true confirms that the scheduled job has been successfully removed and will no longer execute automatically.
3.9 Monitor Scheduled Job Execution
pg_cron maintains an execution history for every scheduled job, making it easy to monitor successful executions, investigate failures, and troubleshoot scheduling issues. You can query the job execution details using the cron.job_run_details system table.
SELECT * FROM cron.job_run_details ORDER BY start_time DESC;
This query retrieves the execution history of scheduled jobs, including their execution status, start time, and completion time, ordered from the most recent execution to the oldest.
jobid | status | start_time | end_time ------+-----------+--------------------+-------------------- 1 | succeeded | 2025-07-12 02:00 | 2025-07-12 02:00 1 | succeeded | 2025-07-13 02:00 | 2025-07-13 02:00 1 | failed | 2025-07-14 02:00 | 2025-07-14 02:00
The output provides valuable operational insights by showing whether each scheduled execution succeeded or failed, along with its execution timestamps. This information is useful for auditing scheduled jobs, monitoring reliability, identifying recurring failures, and diagnosing issues that may require further investigation.
4. Conclusion
pg_cron provides an elegant and lightweight solution for scheduling recurring SQL operations directly inside PostgreSQL. By embedding scheduling within the database, it eliminates the need for external cron scripts for many common maintenance and automation tasks such as data cleanup, report generation, refreshing materialized views, and routine administrative jobs. The extension is easy to install, supports familiar Unix cron expressions, and offers built-in job management and execution history, making it an excellent choice for database-centric automation. However, it is important to recognize its scope. pg_cron excels at executing SQL-based tasks but is not intended to replace full-featured workflow orchestration platforms that coordinate multiple services or complex business processes. For applications whose scheduled work remains within PostgreSQL, pg_cron is often the simplest, most maintainable, and most reliable scheduling solution available. By following best practices such as keeping jobs lightweight, encapsulating logic in stored procedures, monitoring execution history, and avoiding overlapping tasks, teams can build robust and efficient database automation with minimal operational overhead.
