Efficient data retention policy other than time in timescaledb

buddy2891 0
Viewed 39

I have a hypertable which looks like this:

   Column    |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
-------------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 state       | text    |           |          |         | extended |             |              |
 device      | text    |           |          |         | extended |             |              |
 time        | bigint  |           | not null |         | plain    |             |              |
Indexes:
    "device_state_time" btree ("time")
Triggers:
    ts_insert_blocker BEFORE INSERT ON "device_state" FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Child tables: _timescaledb_internal._hyper_4_2_chunk
Access method: heap

I have 100k devices each sending their state at different time intervals. For ex, device1 sends state every second, device2 every day, device3 every 5 days etc. And I MUST keep at least 10 latests states for a device. So, I can't really use the default data retention policy provided by timescale.

Is there any way to achieve this efficiently other than manually selecting the latest 10 entries for each device and deleting the rest?

Thanks!

timescaledb time-series database postgresql
1 Answers

That sounds like a corner case because the chunks are time-based. Can you categorize these devices in advance?

Maybe you can insert data into different hypertables based on the insert timeframe if you still want to use the retention policies.

For example, on promscale, the solution uses one table for each metric, allowing users to redefine the retention policy for every metric.

It will depend on how you read the data later; maybe fragmenting it into several hypertables will make it harder.

Also, consider hacking the hypertable creation optional arguments maybe you can get something from the partitioning_func and time_partitioning_func.

jonatasdp0
Related