Retention policy is not deleting old data

[vishnuc]

What type of bug is this?

Incorrect result

What subsystems and features are affected?

Other

What happened?

CREATE TABLE IF NOT EXISTS tick (
    symbol TEXT NOT NULL,
    volume REAL NOT NULL,
    price REAL NOT NULL,
    timestamp TIMESTAMPTZ NOT NULL,
    UNIQUE (symbol, timestamp)
);
SELECT create_hypertable('tick', 'timestamp', chunk_time_interval => INTERVAL '1 day');
CREATE INDEX IF NOT EXISTS tick_symbol_timestamp_idx ON tick (symbol, timestamp DESC);
SELECT add_retention_policy('tick', INTERVAL '3 days');

I created this table and 100's of ticks are ingested every second from jan 13th , Still my old data are not being deleted. I expect it to delete data older than 3 days .

TimescaleDB version affected

2.17.2

PostgreSQL version used

17.2

What operating system did you use?

ubuntu 24

What installation method did you use?

Deb/Apt

What platform did you run on?

Amazon Web Services (AWS)

Relevant log output and stack trace

How can we reproduce the bug?

adding SELECT add_retention_policy('tick', INTERVAL '3 days');
to tick which contains more than 5 million rows.

[mkindahl]

@vishnuc Thank you for the bug report. Could you show the configuration of the job? For example, when I use your code, I get the following configuration.

mats=# select * from timescaledb_information.jobs where job_id = 1000;
-[ RECORD 1 ]-----+----------------------------------------------
job_id            | 1000
application_name  | Retention Policy [1000]
schedule_interval | 1 day
max_runtime       | 00:05:00
max_retries       | -1
retry_period      | 00:05:00
proc_schema       | _timescaledb_functions
proc_name         | policy_retention
owner             | mats
scheduled         | t
fixed_schedule    | f
config            | {"drop_after": "3 days", "hypertable_id": 46}
next_start        | 2025-01-21 11:09:54.84348+01
initial_start     | [NULL]
hypertable_schema | public
hypertable_name   | tick
check_schema      | _timescaledb_functions
check_name        | policy_retention_check

As you can see (from the schedule_interval), the policy runs once a day, and will then remove the last three days of data when it runs.