Are you looking for the official how-to guide from timescaledb about retention policies? Here is a link
Firstly I want to know which tables I have are actually hypertables. You can list hypertables (check that link as they talk about it having changed) by doing:
select * from _timescaledb_catalog.hypertable;
metrics=> select table_name from _timescaledb_catalog.hypertable;
But I have a lot of other tables that are created by telegraf (specifically, phemmer’s fork which adds postgres output support). Why aren’t they hypertables?
This is important because you can’t do retention policies on regular tables with timescaledb. You also lose out on other important timescaledb features which in my case of concern about disk space may be important, i.e. compression.
Turns out that the telegraf plugin is not automatically creating a hypertable, so that’s a todo on my telegraf fork.
Regardless, we want to solve for disk space to avoid a 3 AM pagerduty alert. We want a retention policy of 12 months for the rails requests and 2 weeks for everything else.
Let’s apply these now:
Here’re the docs on creating a retention policy:
SELECT add_retention_policy('rails_requests', INTERVAL '12 months');
Let’s apply these two policies now:
metrics=> SELECT add_retention_policy('rails_requests', INTERVAL '12 months');
We can confirm it with
SELECT * FROM timescaledb_information.job_stats;
metrics=> SELECT * FROM timescaledb_information.job_stats;
Looks good. But we need to free disk space NOW and we have many tables that are not hypertables…
What we can do is create hypertables through migration which will lockup tables for an unknown amount of time, or we can drop the table and rebuild it.
I am going to opt for scripting for the latter option because it’s not a big deal to get a fresh start on these other tables. List them with
Eliminating the two tables that we already dealt with, the plan is to take these tables, and for each one, truncate the table, create the hypertable, and then set a 2 week retention policy… Let’s do it manually with the biggest (and least interesting) table we have, the cpu table:
TRUNCATE TABLE cpu;
Wow indeed we saved 20GB… and thanks to the retention policy we will no longer run out of space.