Setting a Purge Policy
The preferred method for purging data is to establish a policy that determines which deleted data is eligible to be purged. Eligible data is automatically purged when the Tuple Mover performs mergeout operations.
Vertica provides two methods for determining when deleted data is eligible to be purged:
- Specifying the time for which delete data is saved
- Specifying the number of epochs that are saved
Specifying the Time for Which Delete Data Is Saved
Specifying the time for which delete data is saved is the preferred method for determining which deleted data can be purged. By default, Vertica saves historical data only when nodes are down.
To change the specified time for saving deleted data, use the HistoryRetentionTime
configuration parameter:
=> ALTER DATABASE DEFAULT SET HistoryRetentionTime = {seconds | -1};
In the above syntax:
- seconds is the amount of time (in seconds) for which to save deleted data.
- -1 indicates that you do not want to use the
HistoryRetentionTime
configuration parameter to determine which deleted data is eligible to be purged. Use this setting if you prefer to use the other method (HistoryRetentionEpochs
) for determining which deleted data can be purged.
The following example sets the history epoch retention level to 240 seconds:
=> ALTER DATABASE DEFAULT SET HistoryRetentionTime = 240;
Specifying the Number of Epochs That Are Saved
Unless you have a reason to limit the number of epochs, Vertica recommends that you specify the time over which delete data is saved.
To specify the number of historical epoch to save through the HistoryRetentionEpochs
configuration parameter:
-
Turn off the
HistoryRetentionTime
configuration parameter:=> ALTER DATABASE DEFAULT SET HistoryRetentionTime = -1;
-
Set the history epoch retention level through the
HistoryRetentionEpochs
configuration parameter:=> ALTER DATABASE DEFAULT SET HistoryRetentionEpochs = {num_epochs | -1};
- num_epochs is the number of historical epochs to save.
- -1 indicates that you do not want to use the
HistoryRetentionEpochs
configuration parameter to trim historical epochs from the epoch map. By default,HistoryRetentionEpochs
is set to -1.
The following example sets the number of historical epochs to save to 40:
=> ALTER DATABASE DEFAULT SET HistoryRetentionEpochs = 40;
Modifications are immediately implemented across all nodes within the database cluster. You do not need to restart the database.
If both HistoryRetentionTime
and HistoryRetentionEpochs
are specified, HistoryRetentionTime
takes precedence.
See Epoch Management Parameters for additional details. See Epochs for information about how Vertica uses epochs.
Disabling Purge
If you want to preserve all historical data, set the value of both historical epoch retention parameters to -1, as follows:
=> ALTER DABABASE mydb SET HistoryRetentionTime = -1;
=> ALTER DATABASE DEFAULT SET HistoryRetentionEpochs = -1;