DROP_Partitions with Hierarchical partitioning takes forever

prad33p2301prad33p2301 ✭
in General Discussion

I have a table which holds 750M per day for 90 days at once.

this is my Hierarchical partitioning logic

`PARTITION BY (mytable.TIMESTAMP_DAY)
GROUP BY
(CASE WHEN ("datediff"('year', mytable.TIMESTAMP_DAY, ((now())::timestamptz(6))::date) >= 1)
THEN (date_trunc('year', mytable.TIMESTAMP_DAY))::date

WHEN ("datediff"('month', mytable.TIMESTAMP_DAY, ((now())::timestamptz(6))::date) >= 2)

THEN (date_trunc('month', mytable.TIMESTAMP_DAY))::date ELSE mytable.TIMESTAMP_DAY END);`

when I try to call DROP_Partitions function for dates beyond 90 days like for ex if I try to drop 2024-09-13 it takes forever , infact its running for 4hrs now and not finished yet. But if I try to drop something in this month its very quick, How do i tackle this?

Answers

  • SruthiASruthiA Administrator

    This requires logs from env. Please create a support case.

  • marcothesanemarcothesane - Select Field - Administrator

    It's a hierarchical partitioning scheme. If you drop a recent partition, you can have a from_key equal to a to_key, and just that partition will be dropped.

    If you drop older partitions, where you have a whole year in one partition, the from_key needs to be the first day, the to_key needs to be the last day of the year. Otherwise, a new partition is created, with everything except the stuff between from_key and to_key will be written into, before the partition is dropped. And that part - creating and filling a partition , takes forever. this is also why the last parameter of DROP_PARTITIONS() is an optional Boolean where you can state if you really want to do that ....

This discussion has been closed.