Delete Vectors

Hi

A delete vector is a byproduct of the Vertica transaction model and is used to track historical values of a tuple when deleted or updated. Delete vectors should be avoided because they slow down the process of reading from the table after data has been deleted from that table.

Delete vectors also take additional space. Delete vectors may be avoided by limiting DELETE & UPDATE statements. The effect of delete vectors may be minimized by regularly purging projections; see the documentation for the following Vertica meta-functions: PURGE(), PURGE_TABLE(), PURGE_PROJECTION().

You can query delete_vectors system table like below to get the accumulated count of delete_vectors per projection wise as well:

dbadmin=> select schema_name,projection_name,storage_type,sum(deleted_row_count) as rows from delete_vectors group by schema_name,projection_name,storage_type order by rows desc;
schema_name | projection_name | storage_type | rows
-------------+---------------------------------+--------------+------
c44737 | vs_partitions_raw_keys_node0003 | DVROS | 10
c44737 | vs_partitions_raw_keys_node0001 | DVROS | 10
c44737 | vs_partitions_raw_keys_node0004 | DVROS | 10
s44737 | vs_partitions_raw_keys_node0001 | DVROS | 10
s44737 | vs_partitions_raw_keys_node0003 | DVROS | 10
s44737 | vs_partitions_raw_keys_node0004 | DVROS | 10
(6 rows)

dbadmin=> select sum(deleted_row_count),projection_name from delete_vectors group by 2 order by 1 desc;
sum | projection_name
-----+---------------------------------
20 | vs_partitions_raw_keys_node0003
20 | vs_partitions_raw_keys_node0004
20 | vs_partitions_raw_keys_node0001
(3 rows)

Regards

Rahul Choudhary