How delete process works in Vertica database

The DELETE command in Vertica does not delete data imediateley, but it marks records as deleted data with delete vectors. Delete statement marks tuples as no longer valid in the current epoch, marking the records for deletion in the WOS(Write Optimized Store), rather than deleting data from disk storage.

Pros and cons when using delete: Pros

• marked rows as deleted so that they remain available to historical queries.
• delete process can be long and costfull so saving time and resources at the time we chose the deleted data is important.

Cons

• uses disk space for the deleted rows and delete markers(as Vertica will write a new file that will point out to the deleted data).
• performance penalty when reading and skipping over deleted data.

How to permanently remove data from Vertica ? The purge operation permanently removes deleted data from physical storage so that the disk space can be reused. How to use purge ?

• Setup a purge policy or purge data manually.

To purge data manually Vertica offers the following functions:

• PURGE_PROJECTION() purges a specified projection.
• PURGE_TABLE() purges all projections on the specified table.
• PURGE() purges all projections in the physical schema.