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 Administrator

    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