How to Schedule Queries in ParadeDB in PostgreSQL

Galaxy Glossary

How do I schedule recurring SQL queries in ParadeDB?

Use pg_cron in ParadeDB to run SQL automatically at set intervals.

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

Example H2

Example H3

Why schedule queries in ParadeDB?

Automated scheduling keeps dashboards fresh, recalculates aggregates, and cleans data without manual effort. ParadeDB inherits PostgreSQL’s extensibility, so the pg_cron extension is the simplest way to run timed jobs.

Does ParadeDB support native scheduling?

ParadeDB itself does not bundle a scheduler. Instead, enable the pg_cron extension on the underlying PostgreSQL instance and create jobs with SELECT cron.schedule(...).

How to enable pg_cron?

Superusers run: CREATE EXTENSION IF NOT EXISTS pg_cron;. In managed ParadeDB clouds, ask the provider to enable it or spin up a self-hosted instance with shared_preload_libraries='pg_cron'.

What is the basic scheduling syntax?

Use SELECT cron.schedule(job_name text, schedule text, command text). The schedule uses standard cron format. Any valid SQL fits in command.

How do I refresh a materialized view hourly?

Example: SELECT cron.schedule('refresh_order_sales', '0 * * * *', $$REFRESH MATERIALIZED VIEW CONCURRENTLY order_sales_mv$$);

Can I parameterize ecommerce jobs?

Yes. Store parameters in a settings table or embed them directly. For daily out-of-stock alerts: SELECT cron.schedule('low_stock_email', '30 6 * * *', $$CALL send_low_stock_email()$$);

How to list, run, and remove jobs?

List: SELECT * FROM cron.job;. Run immediately: SELECT cron.run_job(jobid). Remove: SELECT cron.unschedule(jobid).

Best practices

Keep jobs idempotent, log results to a table, and use CONCURRENTLY or NOWAIT to reduce locks.

Common use cases

Nightly customer segmentation updates
Rolling 7-day sales summary
Vector index re-embedding

What permissions are required?

Only superusers can create jobs by default. Grant cron_admin to trusted roles: GRANT cron_admin TO analyst;

How to monitor scheduled jobs?

Query cron.job_run_details for start_time, end_time, and status. Alert on consecutive failures with external tooling.

When should I avoid pg_cron?

If you need second-level granularity or cross-cluster orchestration, use an external workflow engine like Airflow instead.

Why How to Schedule Queries in ParadeDB in PostgreSQL is important

How to Schedule Queries in ParadeDB in PostgreSQL Example Usage


-- Auto-clean abandoned carts every hour
SELECT cron.schedule(
    'cleanup_abandoned_carts',
    '0 * * * *',
    $$DELETE FROM Orders WHERE status = 'pending' AND order_date < NOW() - INTERVAL '24 hours';$$
);

-- Verify
SELECT jobid, jobname, schedule FROM cron.job WHERE jobname = 'cleanup_abandoned_carts';

How to Schedule Queries in ParadeDB in PostgreSQL Syntax


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

-- General pattern
SELECT cron.schedule(
    job_name   TEXT,          -- descriptive unique name
    schedule   TEXT,          -- cron expression: min hour dom mon dow
    command    TEXT           -- SQL to execute
);

-- E-commerce example: nightly revenue snapshot
SELECT cron.schedule(
    'nightly_revenue',        -- job name
    '0 2 * * *',             -- 02:00 every day
    $$
    INSERT INTO daily_revenue (snapshot_date, total)
    SELECT CURRENT_DATE, SUM(total_amount)
    FROM   Orders
    WHERE  order_date >= CURRENT_DATE - INTERVAL '1 day';
    $$
);

-- List jobs
SELECT * FROM cron.job;

-- Run or remove
SELECT cron.run_job( jobid INT );
SELECT cron.unschedule( jobid INT );

Common Mistakes

Using long-running transactions inside jobs. This locks tables and delays subsequent runs. Fix by adding CONCURRENTLY or batching records.
Hard-coding timestamps like NOW() - INTERVAL '1 day' in daily snapshots. This drifts when the job time changes. Use CURRENT_DATE at execution or pass variables.