postgresqltimescaledb

Data not getting deleted automatically or manually in TimescaleDB

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 because it last ran on 2024-07-31 13:53:49.294042 +00:00, but there's still data older than that time:

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
Solution

  • I think you should explicit the chunk time interval. Also, the latest syntax you say by_range:

    SELECT create_hypertable('traffic', by_range('ts', INTERVAL '1 minute'));

    In this case, your chunk will have only 1 minute of data. You can remove the remaining data only when all the full minute is at least 1 minute old.

    Another thing, is double check if background workers are not stuck: https://docs.timescale.com/use-timescale/latest/user-defined-actions/troubleshooting/