Use a user-defined action to create a generic retention policy

TimescaleDB natively supports adding a data retention policy to a hypertable. If you want to add a generic data retention policy to all hypertables, you can write a user-defined action.

Using a user-defined action to create a generic retention policy

  1. 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.

``` 1. CREATE OR REPLACE PROCEDURE generic_retention (job_id int, config jsonb)

  1. LANGUAGE PLPGSQL

  2. AS $$

  3. DECLARE

  4. drop_after interval;

  5. BEGIN

  6. SELECT jsonb_object_field_text (config, 'drop_after')::interval

  7. INTO STRICT drop_after;

  8. IF drop_after IS NULL THEN

  9. RAISE EXCEPTION 'Config must have drop_after';

  10. END IF;

  11. PERFORM drop_chunks(

  12. format('%I.%I', hypertable_schema, hypertable_name),

  13. older_than => drop_after

  14. ) FROM timescaledb_information.hypertables;

  15. END

  16. $$; ```

  17. Register the job to run daily. In the config, set drop_after to 12 months to drop chunks containing data older than 12 months.

1. 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.