Drop chunks of specific a space partition

[mbande]

is it possible to drop chunks if a specific space partition, e.g. to remove chunks belonging to "partition id 1 older that 1 days"

[LeeHampton]

Our drop_chunks()command currently only supports dropping chunks within a specified time interval. You'll likely need to take a look at your chunk metadata to determine which chunks need to be deleted, then just drop them.

[RobAtticus]

Just a word of warning, be careful when doing this. We use hash partitioning for space dimensions, so it is usually not the case that each partition corresponds 1-to-1 with a value in the dimension. For example, if you have 1000 device ids and only 10 partitions, on average you'll have 100 different device ids per partition, so by dropping a partition you may be deleting more information than you intend.

[andriyfomenko]

Given that similar functionality is getting discussed under issue #563 since 2018 with no movements, it looks like "manual chunk drop" may be a stop-gap measure, but it raises much bigger question:

Is it SAFE to tinker with chunks?

In our use-case, we are totally in control of our space partitioning, so we can clearly see which chunks are to be dropped, but would it create some kind of metadata out-of-sync condition for TimeScale ?

[svenklemm]

You can run DROP TABLE <chunk>; on an individual chunk. You can identify the chunk like so:

SELECT tableoid::regclass FROM <hypertable> WHERE <constraints> GROUP BY tableoid;

[muntdan]

You can run DROP TABLE <chunk>; on an individual chunk. You can identify the chunk like so:

SELECT tableoid::regclass FROM <hypertable> GROUP BY tableoid WHERE <constraints>;

Where is actually before Group By.

Or you can write directly:
SELECT tableoid::regclass as chunk, datetime_col__cast_as_interval, dimension_location_column FROM <hypertable> GROUP BY tableoid, datetime_col__cast_as_interval,dimension_location_column;
DROP TABLE <chunk>;

[Arcturuss]

shouldn't this issue be reopened? function drop_chunks still don't accept space partition as parameter.

[yazeed]

shouldn't this issue be reopened? function drop_chunks still don't accept space partition as parameter.

Agree, this feature alongside other space partition features like #563 should be kept open if the space partitioning implementation is planned to be expanded upon in Timescale in the future.