Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Long running partition reorganization interferes with other operations

Hi, what is the best way to perform a multi-day partioning so that it doesn't interfere with other tasks like deletes?

I have a long running partition reorganization that is taking multiple days. Some other steps in my workflow involve deletes, which fail as a result, since deletes need an exclusive lock. This is creating multiple headaches, especially since there doesn't seem to be a way to cancel, pause, or resume the reorganization.

Ideas are greatly appreciated!
Tagged:

Best Answer

  • Jim_KnicelyJim_Knicely Administrator
    Accepted Answer

    Maybe use the COPY_TABLE command to make a copy of the table. Then run the partition reorg on that table. Once that's done, you'd have to sync up any data changes (i.e. DML) from the original table to the new table, then drop the original table and rename the copied table to back to the original name.

    Also, consider Partition Grouping and Hierarchical Partitioning to limit the number ROS containers.

Answers

  • If you delete lots of data try to partition the table in a way that you can drop partition instead.
    If it is related to delete/ re-insert like a day of data use a staging table with the same partition and projection layout.
    Then you can use swap_partitions between both tables and truncate the staging table instead.

  • @baron42bba said:
    If you delete lots of data try to partition the table in a way that you can drop partition instead.
    If it is related to delete/ re-insert like a day of data use a staging table with the same partition and projection layout.
    Then you can use swap_partitions between both tables and truncate the staging table instead.

    Unfortunately it was neither of those cases. There was simply a process which needed to delete an uncompleted intraday load of a day's data and re-insert fresh to ensure integrity. Maybe not the best workflow, but what we have to live with right now.

    But thank you for mentioning swap_partitions!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.