The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

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 ,

Priyabrat 

Comments

  • 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

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.