Skip to main content

Freeing up diskspace after deleting large number of rows

  • August 15, 2023
  • 3 replies
  • 6 views
jtee

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.

    3 replies

    SruthiA
    Forum|alt.badge.img+1
    • SruthiA
    • Participating Frequently
    • August 15, 2023

    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

    jtee
    • jtee
    • Author
    • New Participant
    • August 16, 2023

    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?

    Sergey_Cherepan_1
    Forum|alt.badge.img+2

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

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement