The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Freeing up diskspace after deleting large number of rows

I've recently deleted some data from a table after inserting them in a different table (using select-insert).

I do notice the diskspace usage of the current partition had suddenly shot up (by 500-600GB) after performing the select-insert command. Deleting the data from the original table does not free up the diskspace.

I've read about Purging and I would also like to ask:
1. does purging happen automatically or must it be triggered manually?
2. In one of the rows, the used_bytes value is 571,187,565 for one node. Each node has about 600GB diskspace left. Is this enough to perform purging for this projection?
3. Is there a way to perform partial purging? in case 2 is not possible.

I am on Vertica 10.1.

Comments

  • SruthiASruthiA Vertica Employee Employee

    yes. purging happens automatically. 600GB should be enough. if it is a partitioned table, then you can perform purge_partition

    https://www.vertica.com/docs/10.1.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/PartitionManagement/PURGE_PARTITION.htm

  • I have tried the following:

    1. Do a pre-test by checking the /data partition of all the Vertica nodes.
    2. Execute select * from delete_vectors where deleted_row_count > 0 ORDER BY used_bytes DESC; to find the DVs with the largest size.
    3. Execute purge_projection(...) on the largest DV.
    4. The purge_projection function completes with a "Projection purged" message.
    5. Do a post-test by checking the /data partition of all the Vertica nodes.

    The /data partition houses the VERTICADB database.

    The finding is that the diskpace usage after purging projections is more than before.

    This is unexpected, as I initially thought purging projections will free up diskspace.

    With the purpose of freeing up diskpace usage, is purging projections (after mass deletion of rows from the table) the correct direction?

  • You have all signs of purge failing because it runs out of disk space for temp.
    Check tuple_mover_operations for operation status = Abort.

Leave a Comment

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