How do i manage specific table's Delete Vector?

HyeontaeJu

these days specific table's delete vector is too larger than any other tablees.. i think this the cuase is that upates occured too many to this table.
in this case how do i reduce delete vector??

Bryan_H

Try this: SELECT PURGE_TABLE('schema.table');
https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/PURGE_TABLE.htm

Nimmi_gupta

@HyeontaeJu is the data is historical and is the table is partitioned?
As @Bryan_H mentioned you can use purge command to clean the deleted records.
Other option to manage DV (delete vectors) records in vertica are truncate table and drop partition.
1. Use the TRUNCATE TABLE statement. Truncating a table removes all the storage associated with that table and the table’s projections. With the TRUNCATE TABLE, you can preserve the projection definition. Truncating a table removes dependencies in the catalog and removes storage containers in the background.
2. To delete historical data, partition the data by the date field and use the DROP PARTITION function. DROP PARTITION removes dependencies in the catalog files and removes storage containers in the background without affecting Vertica transactions.

Jim_Knicely

Severval of my clients have also changed the configuration parameter PurgeMergeoutPercent so that automatic purges are more aggressive.

PurgeMergeoutPercent specifies as a percentage the threshold of deleted records in a ROS container that invokes an automatic mergeout operation, to purge those records.

See: https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/AdministratorsGuide/ConfiguringTheDB/TupleMoverParameters.htm?zoom_highlight=PurgeMergeoutPercent