Automating Maintenance with pg_cron

pg_cron is a simple cron scheduler inside PostgreSQL. It runs SQL commands automatically — perfect for maintenance tasks like creating and dropping partitions.

Setup Example

Install pg_cron:

CREATE EXTENSION IF NOT EXISTS pg_cron;

Schedule pg_partman’s maintenance:

SELECT cron.schedule(
    'pg_partman_maintenance',
    '30 0 * * *',  -- Every day at 00:30
    'SELECT partman.run_maintenance();'
);

Verify:

SELECT * FROM cron.job;

Now PostgreSQL will automatically:

• Create upcoming partitions (premake)
• Drop old ones based on retention (e.g., 30 days)
• Keep your table clean and performant

How pg_partman and pg_cron Work Together

flowchart LR
    A[Parent Table] --> B[pg_partman - Partition Manager]
    B --> C[pg_cron - Scheduler]
    C --> D[Auto Lifecycle]
    D --> E[Create New Partitions and Drop Old Ones]

Flow Summary

1. pg_partman manages the structure: defines how partitions are created and retained.
2. pg_cron manages the timing: when maintenance should run.
3. Combined, they give PostgreSQL an automated partition lifecycle.

Analogy “The Smart Warehouse”

Think of PostgreSQL as a warehouse:

• pg_partman builds new rooms every day (new partitions).
• pg_cron sends a cleaning team each night (drops expired rooms).
  The warehouse always stays clean, efficient, and ready for tomorrow’s data.

Benefits of pg_partman + pg_cron Automation

Conclusion

pg_partman takes care of partition creation and structure. pg_cron makes sure it happens regularly.
Together, they turn PostgreSQL into a self-maintaining time-series engine, perfect for IoT data, logs, analytics, or any system that never stops writing.