Delete Vectors

Hi , 


Could someone explain the concept of Delete Vectors. How it works and all . I could not find it documented nicely anywhere .


Thanks in advance ,



  • Options



    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)



    Rahul Choudhary

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file