How to schedule jobs in ParadeDB PostgreSQL

Galaxy Glossary

How do I schedule recurring SQL jobs with ParadeDB?

schedule_job in ParadeDB lets you run named SQL tasks on a cron-like schedule inside PostgreSQL.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is `schedule_job` in ParadeDB?

schedule_job is a ParadeDB helper that wraps pg_cron under a simple API so you can automate SQL tasks—refreshing materialized views, sending reports, or cleaning data—without leaving PostgreSQL.

Why schedule jobs in an ecommerce database?

Automating nightly inventory syncs, daily revenue rollups, or customer-email exports removes manual steps, guarantees consistency, and lowers operational overhead.

How do I create a new job?

Call parade.schedule_job with a unique name, SQL to run, a cron string, and optional JSON settings. ParadeDB stores the job in parade.jobs and registers it with pg_cron.

Example: nightly sales summary

The query below refreshes the daily_sales_mv materialized view at 01:00 every day.

SELECT parade.schedule_job( 'refresh_daily_sales', $$ REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_mv; $$, '0 1 * * *', '{"owner":"analytics"}'::jsonb );

How can I list existing jobs?

Query parade.jobs or call parade.show_jobs() to see name, schedule, last_run, and next_run.

How do I alter or disable a job?

Use parade.alter_job(job_name, new_cron, new_sql, new_config) to edit, or parade.disable_job(job_name) to stop it without deletion.

Best practices for ParadeDB jobs

Keep job SQL idempotent, monitor runtime in parade.job_run_history, and log errors with RAISE to capture failures quickly.

Common mistakes and fixes

Using NOW() instead of current_date in daily jobs causes multiple dates in one run window. Use current_date for date-based inserts.

Overlapping schedules create lock contention. Stagger heavy jobs or use CONCURRENTLY options.

Further reading

Check pg_cron documentation for advanced cron patterns and ParadeDB GitHub for job management helpers.

FAQ

How do I reschedule a job without dropping it?

Call parade.alter_job('job_name', '30 2 * * *'); the change is immediate.

Can I run a job on demand?

Yes. Execute parade.run_job('job_name') to trigger it instantly.

Where are job logs stored?

Use SELECT * FROM parade.job_run_history WHERE job_name='job_name' for execution history and errors.

Why How to schedule jobs in ParadeDB PostgreSQL is important

How to schedule jobs in ParadeDB PostgreSQL Example Usage


-- Refresh daily sales materialized view every night at 1 AM
SELECT parade.schedule_job(
    'refresh_daily_sales',
    $$
        REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_mv;
    $$,
    '0 1 * * *',
    '{"owner":"analytics"}'
);

How to schedule jobs in ParadeDB PostgreSQL Syntax


SELECT parade.schedule_job(
    job_name      TEXT,              -- unique identifier
    job_sql       TEXT,              -- SQL to execute
    schedule      TEXT,              -- cron pattern '* * * * *'
    config        JSONB DEFAULT '{}' -- free-form settings
);

-- Helpful helpers
SELECT parade.alter_job(job_name, new_schedule, new_sql, new_config);
SELECT parade.disable_job(job_name);
SELECT parade.enable_job(job_name);
SELECT parade.drop_job(job_name);
SELECT parade.show_jobs();
SELECT parade.run_job(job_name);

-- Example with ecommerce tables
SELECT parade.schedule_job(
    'nightly_inventory_sync',
    $$
        UPDATE products p
        SET stock = p.stock + oi.quantity
        FROM orderitems oi
        JOIN orders o ON o.id = oi.order_id
        WHERE o.order_date = current_date - 1;
    $$,
    '0 2 * * *',
    '{"owner":"ops"}'
);

Common Mistakes

Using volatile timestamps like NOW() in daily jobs causes date drift. Fix by using current_date or parameterize the date.
Creating overlapping heavy jobs leads to table locks and slow queries. Fix by staggering schedules or using CONCURRENTLY where supported.

Frequently Asked Questions (FAQs)

How do I reschedule a job without dropping it?

Run parade.alter_job('job_name', '30 2 * * *'); changes apply immediately.

Can I trigger a job manually?

Yes. Call parade.run_job('job_name') to execute it on demand.

Where can I see job execution history?

Query parade.job_run_history to inspect start time, end time, and errors for each run.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.