Manually Purging Data
You manually purge deleted data as follows:
- Determine the point in time to which you want to purge deleted data.
-
Set the ancient history mark (AHM) to this point in time using one of the following functions:
SET_AHM_TIME
sets the AHM to the epoch that includes the specified TIMESTAMP value on the initiator node.SET_AHM_EPOCH
sets the AHM to the specified epoch.GET_AHM_TIME
returns a TIMESTAMP value representing the AHM.GET_AHM_EPOCH
returns the number of the epoch in which the AHM is located.MAKE_AHM_NOW
sets the AHM to the greatest allowable value. This lets you purge all deleted data.
When you use
SET_AHM_TIME
orGET_AHM_TIME
, keep in mind that the timestamp you specify is mapped to an epoch, which by default has a three-minute granularity. Thus, if you specify an AHM time of2008-01-01 00:00:00.00
, the resulting purge might permanently remove as much as the first three minutes of 2008, or fail to remove the last three minutes of 2007.The system prevents you from setting the AHM beyond the point where it prevents recovery in the event of node failure.
-
Purge deleted data from the desired projections with one of the following functions:
PURGE
purges all projections in the physical schema.PURGE_TABLE
purges all projections anchored to the specified table.PURGE_PROJECTION
purges the specified projection.PURGE_PARTITION
purges a specified partition.
The tuple mover performs a mergeout operation to purge the data. Vertica periodically invokes the tuple mover to perform mergeout operations, as configured by tuple mover parameters. You can manually invoke the tuple mover by calling the function
DO_TM_TASK
.
Manual purge operations can take a long time.