> ## Documentation Index > Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # Create and manage jobs > Create and manage custom Postgres functions and procedures that run on a schedule in TimescaleDB export const HYPERCORE = 'hypercore'; export const CAGG = 'continuous aggregate'; export const JOB_CAP = 'Job'; export const JOB = 'job'; export const HYPERTABLE = 'hypertable'; export const CHUNK = 'chunk'; export const CLOUD_LONG = 'Tiger Cloud'; export const TIMESCALE_DB = 'TimescaleDB'; export const SERVICE_LONG = 'Tiger Cloud service'; export const SELF_LONG = 'self-hosted TimescaleDB'; {JOB_CAP}s in {TIMESCALE_DB} are custom Postgres functions or procedures that run on a schedule you define. They help you automate routine database maintenance tasks, data processing operations, and custom workflows that go beyond the built-in automation policies. While {TIMESCALE_DB} includes native scheduling policies for common operations like [refreshing {CAGG}s][refresh-policy], [compressing data with {HYPERCORE}][compression-policy], [dropping old data][retention-policy], and [reordering data within {CHUNK}s][reordering-policy], you may need custom {JOB}s to: * Perform complex data transformations that aren't covered by built-in policies * Trigger external processes or notifications based on database events * Coordinate multiple operations in a specific sequence * Run custom business logic on a schedule * Implement organization-specific maintenance routines ## How jobs work {JOB_CAP}s in {TIMESCALE_DB} follow a simple lifecycle: you create a function or procedure, register it with the job scheduler, and the system handles execution on your defined schedule. ```mermaid theme={"dark"} %%{init: { 'theme':'base', 'themeVariables': { 'primaryColor':'#fff', 'primaryTextColor':'#1a1a1a', 'primaryBorderColor':'#333', 'lineColor':'#666', 'secondaryColor':'#fff', 'secondaryTextColor':'#1a1a1a', 'secondaryBorderColor':'#333', 'tertiaryColor':'#fff', 'tertiaryTextColor':'#1a1a1a', 'tertiaryBorderColor':'#333', 'noteBkgColor':'#fff', 'noteTextColor':'#1a1a1a', 'noteBorderColor':'#333', 'background':'#fff', 'mainBkg':'#fff', 'fontFamily': "'Geist Mono', monospace", 'edgeLabelBackground':'#fff', 'labelColor':'#333', 'labelTextColor':'#333' }, 'flowchart': { 'padding': 30, 'htmlLabels': true, 'curve': 'stepAfter' }, /* Hover tint (best-effort; renderer may ignore themeCSS) */ 'themeCSS': ` .node rect:hover, .node polygon:hover, .node path:hover { fill: rgba(244, 255, 97, 0.18) !important; transition: fill 120ms ease-in-out; } ` }}%% graph TB A[Create function/procedure] --> B[Register job  ] B -->|add_job| C[Job scheduled in catalog] C --> D{Schedule interval} D -->|Time reached| E[Background worker executes job] E --> F[Job completes] F --> G[Record in job stats] G --> C I[Alter job  ] -.->|alter_job| C J[Delete job  ] -.->|delete_job| C H[Manual execution  ] -.->|run_job| E %% Border-only hierarchy %% - Primary nodes: thicker border (B, C, E) %% - Standard nodes: normal border (A, D, F, G) %% - Optional nodes: dashed + lighter border (H, I, J) style A fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a,rx:4,ry:4 style B fill:#fff,stroke:#333,stroke-width:3px,color:#1a1a1a,rx:4,ry:4 style C fill:#fff,stroke:#333,stroke-width:3px,color:#1a1a1a,rx:4,ry:4 style E fill:#fff,stroke:#333,stroke-width:3px,color:#1a1a1a,rx:4,ry:4 style D fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a style F fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a,rx:4,ry:4 style G fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a,rx:4,ry:4 style H fill:#fff,stroke:#666,stroke-width:1.5px,stroke-dasharray: 5 5,color:#1a1a1a,rx:4,ry:4 style I fill:#fff,stroke:#666,stroke-width:1.5px,stroke-dasharray: 5 5,color:#1a1a1a,rx:4,ry:4 style J fill:#fff,stroke:#666,stroke-width:1.5px,stroke-dasharray: 5 5,color:#1a1a1a,rx:4,ry:4 %% connectors linkStyle default stroke:#777,stroke-width:1px click B "/api-reference/timescaledb/jobs-automation/add_job" "add_job API reference" click C "/api-reference/timescaledb/informational-views/index" "Learn about the catalog" click H "/api-reference/timescaledb/jobs-automation/run_job" "run_job API reference" click I "/api-reference/timescaledb/jobs-automation/alter_job" "alter_job API reference" click J "/api-reference/timescaledb/jobs-automation/delete_job" "delete_job API reference" ``` The job scheduler runs as a background process and automatically executes your {JOB}s according to the schedule you define. You can monitor execution history through the [`timescaledb_information.jobs`][informational-views] and [`timescaledb_information.job_stats`][job-stats] views, and manually trigger jobs for testing or immediate execution. ## Prerequisites To follow the steps on this page: * Create a target [{SERVICE_LONG}][create-service] with Real-time analytics enabled.

You need [your connection details][connection-info]. This procedure also works for [{SELF_LONG}][enable-timescaledb]. [create-service]: /deploy-and-operate/tiger-cloud/get-started/create-services [enable-timescaledb]: /deploy-and-operate/self-hosted/install-and-update/install-self-hosted [connection-info]: /integrations/find-connection-details ## Create a job To create a {JOB}, create a [function][postgres-createfunction] or [procedure][postgres-createprocedure] that you want your database to execute, then set it up to run on a schedule. 1. **Define a function or procedure in the language of your choice** Wrap it in a `CREATE` statement: ```sql theme={"dark"} CREATE FUNCTION (job_id INT DEFAULT NULL, config JSONB DEFAULT NULL) RETURNS VOID DECLARE ; BEGIN ; END; $$ LANGUAGE ; ``` For example, to create a function that reindexes a table within your database: ```sql theme={"dark"} 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** For example: ```sql theme={"dark"} select reindex_mytable(); ``` The result looks like this: ```sql theme={"dark"} reindex_mytable ----------------- (1 row) ``` 3. **Register your job with [`add_job`][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: ```sql theme={"dark"} SELECT add_job('reindex_mytable', '1h', config => NULL); ``` The call returns a `job_id` and stores it along with `config` in the {TIMESCALE_DB} catalog. The {JOB} runs on the schedule you set. You can also run it manually with [`run_job`][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 {JOB}s with [`timescaledb_information.jobs`][informational-views]: ```sql theme={"dark"} SELECT * FROM timescaledb_information.jobs; ``` The result looks like this: ```sql theme={"dark"} 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`][run-job] in the foreground. Because `run_job` is a stored procedure and not a function, run it with [`CALL`][postgres-call] instead of `SELECT`. 1. **Set the minimum log level to `DEBUG1`** ```sql theme={"dark"} SET client_min_messages TO DEBUG1; ``` 2. **Run the job** Replace `1000` with your `job_id`: ```sql theme={"dark"} CALL run_job(1000); ``` ## Alter and delete a job Alter an existing {JOB} with [`alter_job`][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. For example, replace the JSON config for a {JOB} with ID `1000`: ```sql theme={"dark"} 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`: ```sql theme={"dark"} SELECT alter_job(1000, scheduled => false); ``` 3. **Re-enable automatic scheduling of a job** 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`: ```sql theme={"dark"} SELECT alter_job(1000, scheduled => true); ``` 4. **Delete a job with [`delete_job`][delete_job]** For example, to delete a {JOB} with ID `1000`: ```sql theme={"dark"} SELECT delete_job(1000); ``` ## Samples ### Downsample and compress chunks {TIMESCALE_DB} lets you downsample and compress {CHUNK}s by combining a [{CAGG} refresh policy][create-cagg] with [{HYPERCORE}][hypercore]. If you want to implement features not supported by those policies, you can write a {JOB} to downsample and convert {CHUNK}s to columnstore instead. The following example downsamples raw data to an average over hourly data. This is an illustrative example, which can be done more simply with a {CAGG} policy. But you can make the query arbitrarily complex. 1. **Create a procedure to downsample chunks and convert them to columnstore** This procedure that first queries the {CHUNK}s of a {HYPERTABLE} to determine if they are older than the `lag` parameter. The {HYPERTABLE} in this example is named `metrics`. If the {CHUNK} is not already compressed, downsample it by taking the average of the raw data. Then compress by converting to the columnstore. This procedure uses a temporary table to store the data while calculating the average. ```sql theme={"dark"} CREATE OR REPLACE PROCEDURE downsample_compress (job_id int, config jsonb) LANGUAGE PLPGSQL AS $$ DECLARE lag interval; chunk REGCLASS; tmp_name name; BEGIN SELECT jsonb_object_field_text (config, 'lag')::interval INTO STRICT lag; IF lag IS NULL THEN RAISE EXCEPTION 'Config must have lag'; END IF; FOR chunk IN SELECT show.oid FROM show_chunks('metrics', older_than => lag) SHOW (oid) INNER JOIN pg_class pgc ON pgc.oid = show.oid INNER JOIN pg_namespace pgns ON pgc.relnamespace = pgns.oid INNER JOIN timescaledb_information.chunks chunk ON chunk.chunk_name = pgc.relname AND chunk.chunk_schema = pgns.nspname WHERE chunk.is_compressed::bool = FALSE LOOP RAISE NOTICE 'Processing chunk: %', chunk::text; -- build name for temp table SELECT '_tmp' || relname FROM pg_class WHERE oid = chunk INTO STRICT tmp_name; -- copy downsampled chunk data into temp table EXECUTE format($sql$ CREATE UNLOGGED TABLE %I AS SELECT time_bucket('1h', time), device_id, avg(value) FROM %s GROUP BY 1, 2; $sql$, tmp_name, chunk); -- clear original chunk EXECUTE format('TRUNCATE %s;', chunk); -- copy downsampled data back into chunk EXECUTE format('INSERT INTO %s(time, device_id, value) SELECT * FROM %I;', chunk, tmp_name); -- drop temp table EXECUTE format('DROP TABLE %I;', tmp_name); PERFORM convert_to_columnstore (chunk); COMMIT; END LOOP; END $$; ``` 2. **Register the job to run daily** In the `config`, set `lag` to 12 months to drop {CHUNK}s containing data older than 12 months. ```sql theme={"dark"} SELECT add_job('downsample_compress','1d', config => '{"lag":"12 month"}'); ``` ### Generic retention policy {TIMESCALE_DB} natively supports adding a [data retention policy][retention-policy] to a {HYPERTABLE}. If you want to add a generic data retention policy to all {HYPERTABLE}s, you can create a custom {JOB}. 1. **Create a procedure that drops chunks from any hypertable** This procedure drops {CHUNK}s from any {HYPERTABLE} if they are older than the `drop_after` parameter. To get all {HYPERTABLE}s, the `timescaledb_information.hypertables` table is queried. ```sql theme={"dark"} CREATE OR REPLACE PROCEDURE generic_retention (job_id int, config jsonb) LANGUAGE PLPGSQL AS $$ DECLARE drop_after interval; BEGIN SELECT jsonb_object_field_text (config, 'drop_after')::interval INTO STRICT drop_after; IF drop_after IS NULL THEN RAISE EXCEPTION 'Config must have drop_after'; END IF; PERFORM drop_chunks( format('%I.%I', hypertable_schema, hypertable_name), older_than => drop_after ) FROM timescaledb_information.hypertables; END $$; ``` 2. **Register the job to run daily** In the `config`, set `drop_after` to 12 months to drop {CHUNK}s containing data older than 12 months. ```sql theme={"dark"} SELECT add_job('generic_retention','1d', config => '{"drop_after":"12 month"}'); ``` You can further refine this policy by adding filters to your procedure. For example, add a `WHERE` clause to the `PERFORM` query to only drop {CHUNK}s from particular {HYPERTABLE}s. ### Automatic tablespace management Moving older data to a different tablespace can help you save on storage costs. {TIMESCALE_DB} supports automatic tablespace management by providing the `move_chunk` function to move chunks between tablespaces. To schedule the moves automatically, you can write a custom {JOB}. On {CLOUD_LONG}, use [tiered storage][tiered-storage] which handles this by providing a [tiering policy API][tiering-policy-api] to move data to low-cost object storage backed by Amazon S3. To implement automatic {CHUNK} moving with a {JOB}: 1. **Create a procedure that moves chunks to a different tablespace** This procedure moves {CHUNK}s to a different tablespace if they contain data older than the `lag` parameter. ```sql theme={"dark"} CREATE OR REPLACE PROCEDURE move_chunks (job_id int, config jsonb) LANGUAGE PLPGSQL AS $$ DECLARE ht REGCLASS; lag interval; destination_tablespace name; index_destination_tablespace name; reorder_index REGCLASS; chunk REGCLASS; tmp_name name; BEGIN SELECT jsonb_object_field_text (config, 'hypertable')::regclass INTO STRICT ht; SELECT jsonb_object_field_text (config, 'lag')::interval INTO STRICT lag; SELECT jsonb_object_field_text (config, 'destination_tablespace') INTO STRICT destination_tablespace; SELECT jsonb_object_field_text (config, 'index_destination_tablespace') INTO STRICT index_destination_tablespace; SELECT jsonb_object_field_text (config, 'reorder_index') INTO STRICT reorder_index; IF ht IS NULL OR lag IS NULL OR destination_tablespace IS NULL THEN RAISE EXCEPTION 'Config must have hypertable, lag and destination_tablespace'; END IF; IF index_destination_tablespace IS NULL THEN index_destination_tablespace := destination_tablespace; END IF; FOR chunk IN SELECT c.oid FROM pg_class AS c LEFT JOIN pg_tablespace AS t ON (c.reltablespace = t.oid) JOIN pg_namespace AS n ON (c.relnamespace = n.oid) JOIN (SELECT * FROM show_chunks(ht, older_than => lag) SHOW (oid)) AS chunks ON (chunks.oid::text = n.nspname || '.' || c.relname) WHERE t.spcname != destination_tablespace OR t.spcname IS NULL LOOP RAISE NOTICE 'Moving chunk: %', chunk::text; PERFORM move_chunk( chunk => chunk, destination_tablespace => destination_tablespace, index_destination_tablespace => index_destination_tablespace, reorder_index => reorder_index ); END LOOP; END $$; ``` 2. **Register the job to run daily** In the config, set `hypertable` to `metrics` to implement automatic {CHUNK} moves on the `metrics` {HYPERTABLE}. Set `lag` to 12 months to move {CHUNK}s containing data older than 12 months. Set `tablespace` to the destination tablespace. ```sql theme={"dark"} SELECT add_job( 'move_chunks', '1d', config => '{"hypertable":"metrics","lag":"12 month","destination_tablespace":"old_chunks"}' ); ``` [tiered-storage]: https://www.tigerdata.com/docs/use-timescale/latest/data-tiering/ [tiering-policy-api]: https://www.tigerdata.com/docs/use-timescale/latest/data-tiering/enabling-data-tiering/#add-a-tiering-policy [alter_job]: /api-reference/timescaledb/jobs-automation/alter_job [compression-policy]: /api-reference/timescaledb/compression/add_compression_policy [create-cagg]: /manage-data/continuous-aggregates/create-a-continuous-aggregate [delete_job]: /api-reference/timescaledb/jobs-automation/delete_job [hypercore]: /manage-data/capabilities/hypercore/understand-hypercore [informational-views]: /api-reference/timescaledb/informational-views/jobs [job]: /api-reference/timescaledb/jobs-automation/add_job [job-stats]: /api-reference/timescaledb/informational-views/job_stats [postgres-call]: https://www.postgresql.org/docs/current/sql-call.html [postgres-createfunction]: https://www.postgresql.org/docs/current/xfunc.html [postgres-createprocedure]: https://www.postgresql.org/docs/current/xproc.html [refresh-policy]: /api-reference/timescaledb/continuous-aggregates/add_continuous_aggregate_policy [reordering-policy]: /api-reference/timescaledb/hypertables/add_reorder_policy [retention-policy]: /api-reference/timescaledb/data-retention/add_retention_policy [run-job]: /api-reference/timescaledb/jobs-automation/run_job