How to Automate Reports in ParadeDB in PostgreSQL

Galaxy Glossary

How do I automate recurring reports in ParadeDB?

Registers a SQL query as a scheduled job that reruns at a fixed interval and stores or exports the results automatically.

Sign up for the latest in SQL knowledge from the Galaxy Team!
Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)
Oops! Something went wrong while submitting the form.

Description

Table of Contents

Why automate ParadeDB reports?

Automating reports guarantees up-to-date analytics, removes manual query runs, and reduces human error in recurring dashboards or CSV exports.

What permissions do I need?

Only superusers or roles granted EXECUTE on the parade.report_schedule() function and USAGE on pg_cron can create, modify, or drop scheduled reports.

How do I schedule a new report?

Call parade.report_schedule() with a unique report_name, the SQL query to run, a cron-style schedule, and an output destination.

SELECT parade.report_schedule(
report_name => 'daily_sales',
sql_query => $$
SELECT order_date::date AS day,
sum(total_amount) AS total_sales,
count(*) AS orders
FROM Orders
WHERE order_date >= current_date - interval '1 day'
GROUP BY 1;
$$,
cron_schedule => '0 2 * * *', -- 02:00 UTC daily
destination => jsonb_build_object(
'type', 's3',
'bucket', 'company-reports',
'path', 'daily_sales/{{DATE}}.csv')
);

How can I list existing automated reports?

Query parade.report_jobs to see name, schedule, next_run, owner, and whether the job is enabled.

SELECT job_id, report_name, cron_schedule, next_run
FROM parade.report_jobs
ORDER BY next_run;

How do I disable or drop a report?

Temporarily pause with parade.report_enable(job_id,false) or remove permanently with parade.report_drop(job_id).

-- pause
SELECT parade.report_enable(42,false);
-- drop
SELECT parade.report_drop(42);

How do I change the SQL or schedule?

Use parade.report_update() to modify sql_query, cron_schedule, or destination without recreating the job.

SELECT parade.report_update(
job_id => 42,
cron_schedule => '0 */6 * * *' -- every 6 hours
);

Best practices for reliable automation

Create covering indexes for large tables used in report queries. Always store timestamps in UTC to avoid daylight-saving surprises. Log to a dedicated schema to keep metadata tidy.

How to debug a failing report?

Check parade.report_logs(job_id) for stderr, runtime, and exit_status. Rerun the underlying SQL manually to isolate data issues.

Common mistake #1

Scheduling at local midnight instead of UTC causes double or missed runs during DST changes. Always use UTC cron strings.

Common mistake #2

Exporting millions of rows without compression inflates S3 costs. Add destination option "compression":"gzip" or aggregate first.

Need to refresh a materialized view instead?

Wrap REFRESH MATERIALIZED VIEW CONCURRENTLY in parade.report_schedule() to combine scheduling with index-friendly refreshes.

Next steps

Set up alerts with pg_notify after each run, and version control your job definitions in a migrations folder to keep infra reproducible.

Why How to Automate Reports in ParadeDB in PostgreSQL is important

How to Automate Reports in ParadeDB in PostgreSQL Example Usage


-- Schedule a weekly revenue report emailed to finance
SELECT parade.report_schedule(
    report_name   => 'weekly_revenue',
    sql_query     => $$
        SELECT date_trunc('week', o.order_date)          AS week_start,
               sum(o.total_amount)                      AS revenue,
               count(*)                                  AS orders,
               count(DISTINCT o.customer_id)             AS customers
        FROM   Orders o
        GROUP  BY 1
        ORDER  BY 1 DESC;
    $$,
    cron_schedule => '0 6 * * 1',   -- 06:00 UTC every Monday
    destination   => jsonb_build_object(
                        'type','email',
                        'to',['finance@acme.io'],
                        'subject','Weekly Revenue Report',
                        'format','csv')
);

How to Automate Reports in ParadeDB in PostgreSQL Syntax


SELECT parade.report_schedule(
    report_name     => text,          -- unique identifier
    sql_query       => text,          -- body of the report
    cron_schedule   => text,          -- "* * * * *" format (UTC)
    destination     => jsonb,         -- {type:'s3'|'table'|'email', ...}
    owner_role      => name  DEFAULT current_user,
    enabled         => boolean DEFAULT true
);

-- inspect jobs
described in view parade.report_jobs

-- pause/resume
SELECT parade.report_enable(job_id boolean);

-- update
SELECT parade.report_update(job_id integer, sql_query text DEFAULT NULL,
                             cron_schedule text DEFAULT NULL,
                             destination jsonb DEFAULT NULL);

-- drop
SELECT parade.report_drop(job_id integer);

Common Mistakes

Frequently Asked Questions (FAQs)

Can I write reports in multiple schemas?

Yes. Qualify objects inside sql_query (e.g., analytics.daily_sales). The job runs with the search_path of owner_role.

Does parade.report_schedule() require pg_cron?

ParadeDB bundles pg_cron internally, but you still need to LOAD 'pg_cron' in postgresql.conf or via shared_preload_libraries.

How do I send JSON instead of CSV?

Set destination {"type":"s3","format":"json"}. ParadeDB serializes each row as JSON and uploads a .json file.

Want to learn about other SQL terms?

Trusted by top engineers on high-velocity teams
Aryeo Logo
Assort Health
Curri
Rubie Logo
Bauhealth Logo
Truvideo Logo
Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)
Oops! Something went wrong while submitting the form.