Create a custom retention job

Per-hypertable built-in retention policies cover most cases. When you need one rule that walks every hypertable in the database, implement it with a custom job.

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.

1. Create a procedure to drop old chunks

   Create a procedure that drops chunks from any hypertable if they are older than the drop_after parameter. To get all hypertables, the timescaledb_information.hypertables table is queried.

   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 chunks containing data older than 12 months.

   SELECT add_job('generic_retention','1d', config => '{"drop_after":"12 month"}');

   Note

   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 chunks from particular hypertables.