Jobs and automation overview

Jobs allow you to run functions and procedures implemented in a language of your choice on a schedule within Timescale. This allows automatic periodic tasks that are not covered by existing policies and even enhancing existing policies with additional functionality.

The following APIs and views allow you to manage the jobs that you create and get details around automatic jobs used by other TimescaleDB functions like continuous aggregate refresh policies and data retention policies. To view the policies that you set or the policies that already exist, see informational views.

Samples

Create a job that runs every hour

Create a procedure and schedule it to run automatically:

CREATE OR REPLACE PROCEDURE cleanup_old_data(job_id int, config jsonb)
LANGUAGE PLPGSQL AS
$$
BEGIN
  DELETE FROM metrics WHERE time < NOW() - INTERVAL '90 days';
  RAISE NOTICE 'Cleanup completed for job %', job_id;
END
$$;

SELECT add_job('cleanup_old_data', '1 hour');

Create a job with configuration parameters

Pass configuration to your job using JSONB:

CREATE OR REPLACE PROCEDURE aggregate_metrics(job_id int, config jsonb)
LANGUAGE PLPGSQL AS
$$
DECLARE
  threshold int := config->>'threshold';
BEGIN
  INSERT INTO daily_summary
  SELECT time_bucket('1 day', time), location, AVG(value)
  FROM metrics
  WHERE value > threshold
  GROUP BY 1, 2;
END
$$;

SELECT add_job(
  'aggregate_metrics',
  '1 day',
  config => '{"threshold": 100}'
);

Alter a job schedule

Change when a job runs:

SELECT alter_job(1000, schedule_interval => INTERVAL '2 hours');

Manually run a job

Trigger a job immediately outside of its schedule:

CALL run_job(1000);

Delete a job

Remove a job from the scheduler:

SELECT delete_job(1000);

Available functions

• add_job(): add a job to run a function or procedure automatically
• alter_job(): alter a job that is scheduled to run automatically
• delete_job(): delete a job from the automatic scheduler
• run_job(): manually run a job