CODEWITHSUNDEEP
postgresqltimescaledb
Geuis
Geuis

Reputation: 42277

Timescaledb: retention policy isn't removing data from hypertable

(note: I've also posted this as a github issue https://github.com/timescale/timescaledb/issues/3653)

I have a hypertable request_logs configured with a 24 hour retention policy. The retention policy is being marked as running successfully, however no old data from the table is being removed. The table continues to grow day by day.

I checked and don't see any errors in the postgresql log files.

Could use additional guidance on where to look for information to troubleshoot this issue.

request_logs table structure

  \d+ request_logs;
  Table "public.request_logs"
Column   |           Type           | Collation | Nullable | Default | Storage | Stats target | Description 
-----------+--------------------------+-----------+----------+---------+---------+--------------+-------------
time      | timestamp with time zone |           | not null |         | plain   |              | 
referer   | bigint                   |           |          |         | plain   |              | 
useragent | bigint                   |           |          |         | plain   |              | 
Indexes:
"request_logs_time_idx" btree ("time" DESC)
Triggers:
ts_insert_blocker BEFORE INSERT ON request_logs FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Child tables: _timescaledb_internal._hyper_1_37_chunk,
_timescaledb_internal._hyper_1_38_chunk,
_timescaledb_internal._hyper_1_40_chunk
Access method: heap

This is the hypertable description retrieved by running select * from _timescaledb_catalog.hypertable;

 id | schema_name |  table_name  | associated_schema_name | associated_table_prefix | num_dimensions | chunk_sizing_func_schema |  chunk_sizing_func_name  | chunk_target_size | compression_state | compressed_hypertable_id | replication_factor 
----+-------------+--------------+------------------------+-------------------------+----------------+--------------------------+--------------------------+-------------------+-------------------+--------------------------+--------------------
  1 | public      | request_logs | _timescaledb_internal  | _hyper_1                |              1 | _timescaledb_internal    | calculate_chunk_interval |                 0 |                 0 |                          |                   
(1 row)

This is the retention_policy retrieved by running SELECT * FROM timescaledb_information.job_stats;.

 hypertable_schema | hypertable_name | job_id |      last_run_started_at      |    last_successful_finish     | last_run_status | job_status | last_run_duration |          next_start           | total_runs | total_successes | total_failures 
-------------------+-----------------+--------+-------------------------------+-------------------------------+-----------------+------------+-------------------+-------------------------------+------------+-----------------+----------------
 public            | request_logs    |   1002 | 2021-10-05 23:59:01.601404+00 | 2021-10-05 23:59:01.638441+00 | Success         | Scheduled  | 00:00:00.037037   | 2021-10-06 23:59:01.638441+00 |          8 |               8 |              0
                   |                 |      1 | 2021-10-05 08:38:20.473945+00 | 2021-10-05 08:38:21.153468+00 | Success         | Scheduled  | 00:00:00.679523   | 2021-10-06 08:38:21.153468+00 |         45 |              45 |              0
(2 rows)

Relevant system information:

Upvotes: 2

Views: 1427

Answers (1)

greenweeds
greenweeds

Reputation: 554

It looks as though this might be related to a bug that has been fixed in version 2.4.2 of TimescaleDB. The GitHub report has been updated, if you find that the issue remains after upgrade, please update the issue on GitHub with your example. Thanks for reporting! Transparency: I work for Timescale

Upvotes: 2

Related Questions