How to Automate Reports in PostgreSQL

How do I automate recurring reports in PostgreSQL with pg_cron?

Use pg_cron to schedule SQL jobs that refresh materialized views or run ad-hoc queries, producing hands-free, recurring reports.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What problem does pg_cron solve?

pg_cron lets you run SQL statements on a schedule directly inside PostgreSQL, removing the need for external cron jobs or orchestration tools.

How do I install pg_cron?

Connect as superuser and run:

CREATE EXTENSION IF NOT EXISTS pg_cron;On managed services, enable the extension in the control panel first.

How do I create the report I want to automate?

Encapsulate expensive aggregations in a materialized view so that each refresh replaces the whole report quickly.

CREATE MATERIALIZED VIEW monthly_sales_summary AS SELECT DATE_TRUNC('month', o.order_date) AS month, SUM(o.total_amount) AS revenue, COUNT(o.id) AS order_count FROM Orders o GROUP BY 1;

How do I schedule the refresh?

Use cron.schedule() to run the refresh after business hours:

SELECT cron.schedule( job_name => 'refresh_monthly_sales', schedule => '0 1 1 * *', -- 1 AM on the 1st of every month command => $$REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary;$$ );

How do I run a query and email the result?

Wrap the query in a server-side function that uses pgenv or an SMTP extension, then schedule the function with pg_cron.This keeps credentials inside the database.

How can I list or change jobs?

Query cron.job to inspect jobs and use cron.unschedule(jobid) to remove them. Update schedules with cron.schedule() on the same job_name.

What are best practices?

• Always mark long-running refreshes as CONCURRENTLY to avoid locks.
• Log job output by setting log_min_messages = info.
• Store results in materialized views or tables, not temporary files, for easier reuse.

Why How to Automate Reports in PostgreSQL is important

How to Automate Reports in PostgreSQL Example Usage


-- Automate a weekly "Top-Selling Products" report

-- 1. Create the materialized view
CREATE MATERIALIZED VIEW weekly_top_products AS
SELECT p.id, p.name, SUM(oi.quantity) AS units_sold
FROM   OrderItems oi
JOIN   Products    p ON p.id = oi.product_id
JOIN   Orders      o ON o.id = oi.order_id
WHERE  o.order_date >= CURRENT_DATE - INTERVAL '7 days'
GROUP  BY p.id, p.name
ORDER  BY units_sold DESC
LIMIT  10;

-- 2. Schedule refresh every Monday at 02:00
SELECT cron.schedule(
   job_name => 'refresh_weekly_top_products',
   schedule => '0 2 * * 1',
   command  => $$REFRESH MATERIALIZED VIEW CONCURRENTLY weekly_top_products;$$
);

-- 3. Query the view whenever you need the report
SELECT * FROM weekly_top_products;

How to Automate Reports in PostgreSQL Syntax


-- Enable extension (superuser)
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Create a materialized view for the report
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT DATE_TRUNC('month', o.order_date)  AS month,
       SUM(o.total_amount)               AS revenue,
       COUNT(o.id)                       AS order_count
FROM   Orders o
GROUP  BY 1;

-- Schedule the job (name, cron expression, SQL command)
SELECT cron.schedule(
    job_name => 'refresh_monthly_sales',
    schedule => '0 1 1 * *',
    command  => $$REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary;$$
);

-- View jobs
SELECT * FROM cron.job;

-- Remove a job
SELECT cron.unschedule(jobid => 42);

Common Mistakes

Scheduling <code>REFRESH MATERIALIZED VIEW</code> without the <code>CONCURRENTLY</code> keyword. This can lock the underlying tables and block writes. Fix by always including <code>CONCURRENTLY</code> for large views.
Using an overly frequent schedule such as every minute for heavy aggregations. This wastes resources and can slow production traffic. Fix by matching the refresh interval to business needs (e.g., hourly or daily).