Data Retention
A data retention policy aims at removing data older than a certain duration from the database.
Example
Below an example of a retention policy which applies on all hypertables.
It is possible to customize how it works by changing the format
function according to your
data or add filter using WHERE
clause in the PERFORM
query.
CREATE OR REPLACE PROCEDURE generic_retention (config jsonb)
LANGUAGE PLPGSQL
AS $$
DECLARE
drop_after interval;
schema varchar;
name varchar;
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;
-- You can modify the following query to add a more precise retention policy.
FOR schema, name IN SELECT hypertable_schema, hypertable_name FROM timescaledb_information.hypertables
LOOP
RAISE NOTICE '%', format('%I.%I', schema, name);
PERFORM drop_chunks(format('%I.%I', schema, name), older_than => drop_after);
COMMIT;
END LOOP;
END
$$;
Execute Procedure
You can schedule the execution of the previous procedure by using the
Scalingo scheduler.
To do so, add a cron.json
file at the root of your application’s source code,
containing the code below:
{
"jobs": [
{
"command": "0 0 * * * dbclient-fetcher psql && psql $SCALINGO_POSTGRESQL_URL -f data_retention_procedure.sql",
"size": "S"
}
]
}
The scheduled task will be executed every day at midnight. And execute a psql
command that invokes a SQL script called data_retention_procedure.sql
.
Create this SQL script file at the root of your application’s source code containing the code below:
call generic_retention(config => '{"drop_after":"12 month"}');
This script invokes the created procedure generic_retention
to remove the data
older than twelve months.