---
title: Create and manage custom jobs | Tiger Data Docs
description: Create, register, test, alter, and delete custom jobs in TimescaleDB
---

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

To follow the procedure on this page you need to:

- Create a [target Tiger Cloud service](/docs/get-started/quickstart/create-service/index.md).

  This procedure also works for [self-hosted TimescaleDB](/docs/get-started/choose-your-path/install-timescaledb/index.md).

## Create a job

To create a job, create a [PostgreSQL function](https://www.postgresql.org/docs/current/xfunc.html) or [procedure](https://www.postgresql.org/docs/current/xproc.html) 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
   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`](/docs/reference/timescaledb/jobs-automation/run_job/index.md) 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`](/docs/reference/timescaledb/informational-views/jobs/index.md):

   ```
   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 |         config         |          next_start           | hypertable_schema | hypertable_name
   --------+----------------------------+-------------------+-------------+-------------+--------------+-----------------------+------------------+-----------+-----------+------------------------+-------------------------------+-------------------+-----------------
   1 | Telemetry Reporter [1]     | 24:00:00          | 00:01:40    |          -1 | 01:00:00     | _timescaledb_internal | policy_telemetry | postgres  | t         |                        | 2022-08-18 06:26:39.524065+00 |                   |
   1000 | User-Defined Action [1000] | 01:00:00          | 00:00:00    |          -1 | 00:05:00     | public                | reindex_mytable  | tsdbadmin | t         |                        | 2022-08-17 07:17:24.831698+00 |                   |
   (2 rows)
   ```

## Test and debug a job

To debug a job, increase the log level and run the job manually with [`run_job`](/docs/reference/timescaledb/jobs-automation/run_job/index.md) in the foreground. Because `run_job` is a stored procedure and not a function, run it with [`CALL`](https://www.postgresql.org/docs/current/sql-call.html) instead of `SELECT`.

1. **Set the minimum log level to `DEBUG1`**

   ```
   SET client_min_messages TO DEBUG1;
   ```

2. **Run the job**

   Replace `1000` with your `job_id`:

   ```
   CALL run_job(1000);
   ```

## Alter and delete a job

Alter an existing job with [`alter_job`](/docs/reference/timescaledb/jobs-automation/alter_job/index.md). 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. For example, replace the JSON config for a job with ID `1000`:

   ```
   SELECT alter_job(1000, 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, turn off the scheduling for a job with ID `1000`:

   ```
   SELECT alter_job(1000, 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, re-enable scheduling for a job with ID `1000`:

   ```
   SELECT alter_job(1000, scheduled => true);
   ```

4. **Delete a job with `delete_job`**

   For example, to delete a job with ID `1000`:

   ```
   SELECT delete_job(1000);
   ```