We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


DROP_Partitions with Hierarchical partitioning takes forever — Vertica Forum

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.