If you want to remove delete vectors manually or troubleshoot why they are not being removed automatically, follow this checklist.
| Step | Task | Results |
|---|---|---|
| 1 | Check if you have too many delete vectors (more than 100) in a projection.
=>SELECT node_name, schema_name, projection_name, COUNT(*) num_dv, SUM(deleted_row_count) del_cnt, SUM(used_bytes) ubytes, MIN(start_epoch) min_epoch, MAX(start_epoch) max_epoch FROM delete_vectors GROUP BY 1,2,3 ORDER BY 4 DESC; |
If there are too many delete vectors as shown by the num_dv (usually more than 100), go to Step 2. |
| 2 | Check if the AHM and LGE are advancing.
=>SELECT current_epoch,ahm_epoch,last_good_epoch FROM system; |
If the AHM and LGE are advancing, go to Step 3. If they are not advancing, review Epoch Management checklist. |
| 3 | Check if the epochs in the output of Step 1 are older than the AHM in the output of Step 2. | If the result of the Step 1 query shows that the deleted rows have a newer epoch than the AHM, go to Step 4. |
| 4 | Advance the AHM with:
=>SELECT make_ahm_now();
Do one of the following:
|
If you’re waiting for the mergeout to remove the delete vectors as part of its standard process, the checklist is now complete. If you want to manually initiate a removal of the mergeout, go to Step 5. |
| 5 | Check if the percentage of delete vectors marked meets the PurgeMergeoutPercent. This configuration parameter dictates when the vectors are automatically purged by the mergeout operation.
|
If the delete vector percentage is more than the PurgeMergeoutPercent and the ROS size is not too big, you have two options. Either:
|
a.
Force a purge by doing one of the following:
|
The checklist is complete! | |
b.
Merge the delete vectors to reduce the number of files on disk without rewriting the data.
=>SELECT DO_TM_TASK('dvmergeout'); |
The checklist is complete! | |
c.
Create a new table with the data that is not deleted.
To do this you must first create a new table (B) like the original table (A), including projections using the CREATE TABLE command. Then execute the following commands:
=>INSERT into B SELECT * from A;
DROP TABLE A;
ALTER TABLE B RENAME to A; |
The checklist is complete! |
Learn More
Learn more about Best Practices for Deleting Data and check out Deletes in Vertica: The FAQS in Vertica Knowledge Base.About the Author
Soniya Shah
Information Developer
Currently, a first year law student with a background in science and technology. Experienced technical writer, with specializations in software documentation, big data, blog development, and website development. I build user-centered content to communicate complex and technical information more easily.
I used to work for Vertica full time for about 3 years. I still work at Vertica part time while going to law school.
Update: Soniya is now doing her law internship, and no longer working at Vertica. Good luck, Soniya!
