Why did the mergeout process not reduce my disk space?
I installed Vertica 7.1, and made no changes to any config setting.
I created a large table, and noted the amount of disk space on my server.
I ran a large update on that table, noted the disk space increased, which was expected, since an update is really an insert + marking old rows for later deletion.
I waited over an hour, waiting for mergeout to delete the old rows, but the disk space did not decrease at all. I expected the mergeout background process would remove the old rows marked for deletion.
I rebuilt the table, and disk space decreased.
Why did the mergeout process not reduce my disk space?
0
Comments
This is documented behavior. When an UPDATE (which is an INSERT + DELETE) is run, it marks rows for deletion and does not remove rows from physical storage. These rows can be found in v_monitor.delete_vectors. You can perform a manual removal using PURGE. The time for which deleted data is saved can be altered using the HistoryRetentionTime configuration parameter.
It sounds like you were waiting for the automated background mergeout process to run. The Tuple Mover does check for work to do on an interval, but the automated mergeout only runs when there is "enough" work to do. This is a function of the number of ROS containers available to merge, whether the table is partitioned, and whether the partitions are active or inactive. Also the Ancient History Mark (AHM) needs to have moved past the epoch of your UPDATE so that the rows are eligible for being purged during the mergeout.
If you want explicit control, use PURGE as Norbert pointed out, or PURGE_PARTITION. You might also want to use the partition-related functions to do your updates and deletes off to the side, and avoid introducing delete vectors into your live fact table. It depends on what you're use case.
--Sharon