Create and manage custom jobs

Jobs are custom functions or procedures that TimescaleDB runs on your schedule. The sections below show how to register one, test it, and change or remove it safely.

Prerequisites for this procedure

To follow these steps, you'll need:

• A Tiger Cloud service, or a running instance of self-hosted TimescaleDB.

Try this with your AI agent

In Tiger Cloud, set up Tiger MCP and ask your AI agent to do this for you. See example prompts to get started, and best practices to keep it safe.

Create a job

To create a job, create a PostgreSQL function or procedure that you want your database to execute, then set it up to run on a schedule.

1. Define a function or procedure

   Wrap it in a CREATE statement:

   CREATE FUNCTION <function_name> (job_id INT DEFAULT NULL, config JSONB DEFAULT NULL)
   RETURNS VOID
   AS $<variable_name>$
   DECLARE
       <declaration>;
   BEGIN
       <function_body>;
   END;
   $<variable_name>$ LANGUAGE <language>;

   For example, to create a function that reindexes a table within your database:

   CREATE FUNCTION reindex_mytable(job_id INT DEFAULT NULL, config JSONB DEFAULT NULL)
   RETURNS VOID
   AS $$
   BEGIN
      REINDEX TABLE mytable;
   END;
   $$ LANGUAGE plpgsql;

   job_id and config are required arguments in the function signature. This returns CREATE FUNCTION to indicate that the function has successfully been created.

2. Call the function to validate

   select reindex_mytable();

   The result looks like this:

    reindex_mytable
   -----------------
   
   (1 row)

3. Register your job with add_job

   Pass the name of your job, the schedule you want it to run on, and the content of your config. For the config value, if you don't need any special configuration parameters, set to NULL. For example, to run the reindex_mytable function every hour:

   SELECT add_job('reindex_mytable', '1h', config => NULL);

   The call returns a job_id and stores it along with config in the TimescaleDB catalog.

   The job runs on the schedule you set. You can also run it manually with run_job passing job_id. When the job runs, job_id and config are passed as arguments.

4. Validate the job

   List all currently registered jobs with timescaledb_information.jobs:

   SELECT * FROM timescaledb_information.jobs;

   The result looks like this:

    job_id |           application_name           | schedule_interval | max_runtime | max_retries | retry_period |      proc_schema       |             proc_name             |   owner   | scheduled | fixed_schedule |                                         config                                         |          next_start           |         initial_start         | hypertable_schema | hypertable_name |      check_schema      |               check_name
   --------+--------------------------------------+-------------------+-------------+-------------+--------------+------------------------+-----------------------------------+-----------+-----------+----------------+----------------------------------------------------------------------------------------+-------------------------------+-------------------------------+-------------------+-----------------+------------------------+-----------------------------------------
      1074 | User-Defined Action [1074]           | 01:00:00          | 00:00:00    |          -1 | 00:05:00     | public                 | reindex_mytable                   | tsdbadmin | t         | t              |                                                                                        | 2026-10-12 09:28:40.657211+00 | 2026-10-12 09:28:40.657211+00 |                   |                 |                        |
         3 | Job History Log Retention Policy [3] | 06:00:00          | 01:00:00    |          -1 | 01:00:00     | _timescaledb_functions | policy_job_stat_history_retention | tsdbadmin | t         | t              | {"drop_after": "1 month", "max_failures_per_job": 1000, "max_successes_per_job": 1000} | 2026-10-12 12:00:00+00        | 2000-01-01 00:00:00+00        |                   |                 | _timescaledb_functions | policy_job_stat_history_retention_check
         1 | Telemetry Reporter [1]               | 24:00:00          | 00:01:40    |          -1 | 01:00:00     | _timescaledb_functions | policy_telemetry                  | postgres  | t         | f              |                                                                                        | 2026-10-13 07:02:01.612712+00 |                               |                   |                 |                        |
   (3 rows)

Test and debug a job

To debug a job, increase the log level and run the job manually with run_job in the foreground. Because run_job is a stored procedure and not a function, run it with CALL instead of SELECT.

1. Set the minimum log level to DEBUG1

   SET client_min_messages TO DEBUG1;

2. Run the job

   Replace <JOB_ID> with the job_id that add_job returned:

   CALL run_job(<JOB_ID>);

Alter and delete a job

Alter an existing job with alter_job. You can change both the config and the schedule on which the job runs.

1. Change a job's config

   To replace the entire JSON config for a job, call alter_job with a new config object. Replace <JOB_ID> with the job_id that add_job returned:

   SELECT alter_job(<JOB_ID>, config => '{"hypertable":"metrics"}');

2. Turn off job scheduling

   To turn off automatic scheduling of a job, call alter_job and set scheduled to false. You can still run the job manually with run_job. For example:

   SELECT alter_job(<JOB_ID>, scheduled => false);

3. Re-enable automatic scheduling

   To re-enable automatic scheduling of a job, call alter_job and set scheduled to true. For example:

   SELECT alter_job(<JOB_ID>, scheduled => true);

4. Delete a job with delete_job

   Replace <JOB_ID> with the job_id of the job to delete:

   SELECT delete_job(<JOB_ID>);