I observe that data is not getting deleted from Timescale DB tables either automatically or manually. Here are my DDLs:
CREATE TABLE traffic
(
src_ip_addr INET,
ts TIMESTAMPTZ NOT NULL DEFAULT NOW(),
);
CREATE INDEX ON traffic (ts);
SELECT public.create_hypertable('traffic', 'ts');
SELECT public.add_retention_policy('traffic', drop_after => INTERVAL '2 minute', schedule_interval => INTERVAL '1 minute');
In my table table I have rows with ts values ranging from 2024-07-31 13:41:20.611561 +00:00 to 2024-07-31 13:41:33.140070 +00:00.
According to Timescale, the chunk deletion should have happened:
| hypertable_name | job_id | config | schedule_interval | job_status | last_run_status | last_run_started_at | next_start | total_runs | total_successes | total_failures |
|---|---|---|---|---|---|---|---|---|---|---|
| traffic | 1015 | {“drop_after”: “00:02:00”, “hypertable_id”: 6} | 0 years 0 mons 0 days 0 hours 1 mins 0.0 secs | Scheduled | Success | 2024-07-31 13:53:49.294042 +00:00 | 2024-07-31 13:54:49.315476 +00:00 | 14 | 14 | 0 |
The data is not deleted even if I manually delete the chunks:
SELECT public.drop_chunks('traffic', INTERVAL '1 minute');
I think this is happening because range_end is tomorrow, but I’m not sure why that’s the case if drop_after is 2 minutes.
| hypertable_schema | hypertable_name | chunk_schema | chunk_name | primary_dimension | primary_dimension_type | range_start | range_end | range_start_integer | range_end_integer | is_compressed | chunk_tablespace | chunk_creation_time |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| traffic | traffic | _timescaledb_internal | _hyper_6_16_chunk | ts | timestamp with time zone | 2024-07-25 00:00:00.000000 +00:00 | 2024-08-01 00:00:00.000000 +00:00 | null | null | false | null | 2024-07-31 13:41:20.614031 +00:00 |