pg_cron is an easy-to-use, cron-based job scheduler designed specifically for PostgreSQL (version 10 and higher). This powerful extension allows you to execute PostgreSQL commands directly from your database using familiar cron syntax, enabling scheduled jobs with precision and flexibility. For instance, you can define jobs to run at specific times or intervals, making it ideal for automating routine database maintenance tasks such as deleting old data or performing vacuum operations.

Key Features of pg_cron:

• Cron Syntax Compatibility: Schedule jobs using the standard cron syntax while taking advantage of PostgreSQL command execution.
• Advanced Scheduling Options: Utilize unique options such as scheduling jobs for specific seconds or the last day of the month by using the '$' symbol.
• Parallel Job Execution: Run multiple jobs simultaneously while ensuring individual job instances do not conflict with one another.

Example Usage

Here are a few examples of how to schedule jobs using pg_cron:

-- Delete old events every Saturday at 3:30 AM (GMT)
SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);

-- Run VACUUM every day at 10:00 AM (GMT)
SELECT cron.schedule('nightly-vacuum', '0 10 * * *', 'VACUUM');

-- Adjust vacuum time to 3:00 AM (GMT)
SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM');

-- Un-schedule a job
SELECT cron.unschedule('nightly-vacuum');

-- Schedule a job in a different database
SELECT cron.schedule_in_database('weekly-vacuum', '0 4 * * 0', 'VACUUM', 'some_other_database');

Additional Features

• Job Scheduling: Flexibly control job execution with cron.schedule and cron.unschedule functions, along with oversight capabilities through the cron.job and cron.job_run_details tables.
• User Security and Isolation: Each user is restricted to viewing and managing their jobs, ensuring secure operations.
• Timezone Configuration: Customize job scheduling to run in different time zones according to your needs.

Learn More

For more information on possible use cases and detailed documentation, visit:

• Articles & Use Cases
• Explore pg_cron on major managed services, including Aiven, Amazon RDS, and Google Cloud.

In summary, pg_cron enhances your PostgreSQL database management by automating routine tasks efficiently, thus improving performance and ensuring timely data operations.