---
title: "pg_cron extension"
url: "https://docs.yugabyte.com/stable/additional-features/pg-extensions/extension-pgcron/"
---

# pg_cron extension

Using the pg_cron extension in YugabyteDB

The [pg\_cron](https://github.com/citusdata/pg_cron "pg_cron") extension provides a cron-based job scheduler that runs inside the database. It uses the same syntax as regular cron, and allows you to schedule YSQL commands directly from the database. You can also use '\[1-59] seconds' to schedule a job based on an interval.

YugabyteDB supports all features of the pg\_cron extension. Although YugabyteDB is a distributed database that operates on multiple nodes, pg\_cron only runs on one of these nodes, called the pg\_cron leader. Only the pg\_cron leader schedules and runs the cron jobs. The queries executed by jobs do take advantage of all available resources in the cluster.

If the pg\_cron leader node fails, another node is automatically elected as the new leader to ensure it is highly available. This process is transparent, and you can connect to any node in a cluster to schedule jobs.

## Set up pg\_cron

Before you can use the feature, you must set the `enable_pg_cron` flag to true on all YB-Masters and YB-TServers. This starts the pg\_cron PostgreSQL background worker for the extension.

The pg\_cron extension is installed on only one database, which stores the extension data. The default cron database is `yugabyte`. You can change it by setting the `ysql_cron_database_name` flag on all YB-TServers. You can create the database after setting the flag. To change the database after the extension is created, you must first *drop the extension* and then change the flag value.

For example, to create a single-node [yugabyted](/stable/reference/configuration/yugabyted/ "yugabyted") cluster with pg\_cron on database 'db1', use the following command:

```sh
./bin/yugabyted start --master_flags "enable_pg_cron=true" --tserver_flags "enable_pg_cron=true,ysql_cron_database_name=db1" --ui false
```

## Enable pg\_cron

Create the extension as superuser on the cron database.

```sql
CREATE EXTENSION pg_cron;
```

You can grant access to other users to use the extension. For example:

```sql
GRANT USAGE ON SCHEMA cron TO elephant;
```

## Use pg\_cron

YugabyteDB supports all features and syntax of the pg\_cron extension.

For example, the following command calls a stored procedure every five seconds:

```sql
SELECT cron.schedule('process-updates', '5 seconds', 'CALL process_updates()');
```

If you need to run jobs in multiple databases, use `cron.schedule_in_database()`.

When running jobs, keep in mind the following:

- It may take up to 60 seconds for job changes to get picked up by the pg\_cron leader.
- When a new pg\_cron leader node is elected, no jobs are run for the first minute. Any job that were in flight on the failed node will not be retried, as their outcome is not known.

For more information on how to schedule jobs, refer to the [pg\_cron documentation](https://github.com/yugabyte/yugabyte-db/blob/master/src/postgres/third-party-extensions/pg_cron/README.md "pg_cron documentation").

## Best practices

The `cron.job_run_details` table is part of the pg\_cron extension in PostgreSQL. This table logs information about each cron job run, including its start and end time, status, and any exit messages or errors that occurred during the execution. The records in `cron.job_run_details` are not cleaned automatically, so in scenarios where you have jobs that run frequently, set up a periodic cleanup task for the table using pg\_cron to ensure old data doesn't accumulate and affect database performance.

If you are using pg\_cron to automatically manage partitions, see [Maintain partitions using pg\_cron](/stable/additional-features/pg-extensions/extension-pgpartman/#maintain-partitions-using-pg-cron "Maintain partitions using pg_cron") for more information.

### View job details

You can view the status of running and recently completed jobs in the `cron.job_run_details` table using the following command:

```sql
select * from cron.job_run_details order by start_time desc limit 5;
```

### Set up a periodic cleanup task

Create a periodic cleaning task for the `cron.job_run_details` table using pg\_cron similar to the following example:

```sql
-- Delete old cron.job_run_details records of the current user every day at noon
SELECT  cron.schedule('delete-job-run-details', '0 12 * * *', $$DELETE FROM cron.job_run_details WHERE end_time < now() - interval '7 days'$$);
```

## Limitations

If you are using pg\_cron with xCluster:

- In v2025.1.0 or earlier, or if you are using [semi-automatic or manual mode](/stable/deploy/multi-dc/async-replication/async-transactional-setup-semi-automatic/ "semi-automatic or manual mode"), you must install pg\_cron on a separate database that is not part of the xCluster configuration.
- In v2025.1.1 or later in [automatic mode](/stable/deploy/multi-dc/async-replication/async-transactional-setup-automatic/ "automatic mode"), you can install pg\_cron on the same database.

For more information on xCluster limitations, refer to [Limitations](/stable/architecture/docdb-replication/async-replication/#limitations "Limitations").

## Examples

The following examples describe various ways pg\_cron can be used to automate and improve database management tasks. The tool can help maintain database performance, consistency, and reliability through scheduled jobs.

### Monitor and identify slow queries

Use [pg\_stat\_statements](/stable/additional-features/pg-extensions/extension-pgstatstatements/ "pg_stat_statements") to capture statistics about queries, and schedule regular reports with pg\_cron to summarize slow queries.

```sql
INSERT INTO slow_queries SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;
```

### Validate performance

pg\_cron can indirectly assist with query tuning in a few ways. For example, after identifying and implementing query optimizations or indexing improvements, you can monitor the impact over time using pg\_cron to execute scripts that validate the effectiveness of your changes.

```sql
SELECT cron.schedule('weekly_check_performance', '0 4 * * 0',
$$
DO $$
BEGIN
-- Example of weekly check
IF EXISTS (SELECT 1 FROM your_table WHERE performance_degraded) THEN
RAISE NOTICE 'Performance issue detected';
END IF;
END;
$$;
$$);
```

### Automate data refresh for materialized views

You can keep materialized views up to date ensuring that they reflect recent data changes.

The following example schedules a job to refresh the `your_materialized_view` every hour.

```sql
SELECT cron.schedule('refresh_materialized_view', '0 * * * *', 'REFRESH MATERIALIZED VIEW your_materialized_view');
```

### Cleanup old logs or temporary data

You can periodically clean up old logs or temporary data to free up space.

The following example schedules a job to delete logs older than 30 days every day at 2AM.

```sql
SELECT cron.schedule('cleanup_old_logs', '0 2 * * *', $$
DO $$
BEGIN
DELETE FROM logs WHERE log_date < NOW() - INTERVAL '30 days';
END;
$$);
```

## Learn more

[Maintain partitions using pg\_cron](/stable/additional-features/pg-extensions/extension-pgpartman/#maintain-partitions-using-pg-cron "Maintain partitions using pg_cron")