Setting the purge policy in Vertica

In Vertica the Tuple Mover process is responsible for purging old data. The data that is eligible for purging will listen to the value that the HistoryRetentionTime parameter is set. The default value of this parameter is set to -1, this values tells Vertica that no deleted data will be saved and once the Tuple Mover executes the deleted data will be purged.

Let's alter the value of this parameter and see how Vertica database reacts.

  • we will set it to 10 minutes(600 seconds), the value will be set in seconds.
(dbadmin@:5433) [dbadmin] * SELECT SET_CONFIG_PARAMETER('HistoryRetentionTime', '600' );
    SET_CONFIG_PARAMETER
----------------------------
 Parameter set successfully
(1 row)

(dbadmin@:5433) [dbadmin] * select get_config_parameter('HistoryRetentionTime');
 get_config_parameter
----------------------
 600
(1 row)

 Now let's create a table in insert some data and delete some data as well.

dbadmin@:5433) [dbadmin]  create table one(id int);
CREATE TABLE
(dbadmin@:5433) [dbadmin]  select current_epoch,ahm_epoch,last_good_epoch from system;
 current_epoch | ahm_epoch | last_good_epoch
---------------+-----------+-----------------
           909 |       908 |             908
(1 row)


-- check the epoch values from the system table
(dbadmin@:5433) [dbadmin] * select current_epoch,ahm_epoch,last_good_epoch from system;
-[ RECORD 1 ]---+----
current_epoch   | 909
ahm_epoch       | 908
last_good_epoch | 908

Insert some data into your table

(dbadmin@:5433) [dbadmin] * insert into one values(1);
 OUTPUT
--------
      1
(1 row)

(dbadmin@:5433) [dbadmin] * commit;
COMMIT
(dbadmin@:5433) [dbadmin]  insert into one values(1);
 OUTPUT
--------
      1
(1 row)

(dbadmin@:5433) [dbadmin] * commit;
COMMIT

(dbadmin@:5433) [dbadmin]  select * from one;
 id
----
  1
  1
(2 rows)

Check the epoch values once more

(dbadmin@:5433) [dbadmin] * select current_epoch,ahm_epoch,last_good_epoch from system;
-[ RECORD 1 ]---+----
current_epoch   | 911
ahm_epoch       | 908
last_good_epoch | 908

 Delete the data from the table ans check the epochs

(dbadmin@:5433) [dbadmin] * delete from one ;
-[ RECORD 1 ]
OUTPUT | 2

(dbadmin@:5433) [dbadmin] * commit;
COMMIT
(dbadmin@:5433) [dbadmin]  select current_epoch,ahm_epoch,last_good_epoch from system;
-[ RECORD 1 ]---+----
current_epoch   | 912
ahm_epoch       | 908
last_good_epoch | 908

 Insert some data and check the content of the table

(dbadmin@:5433) [dbadmin] * insert into one values(2);
-[ RECORD 1 ]
OUTPUT | 1

(dbadmin@:5433) [dbadmin] * commit;
COMMIT


(dbadmin@:5433) [dbadmin] * select * from one;
 id
----
  2
(1 row)
  • important to know is that all the selects without using historical queries statements will use the current epoch as a guide.

 Now we will do a select on the deleted data using the historical query statement.

(dbadmin@:5433) [dbadmin] *  at epoch 910 select * from one;
 id
----
  1
  1

To disable the purge policy you need to set the value of the parameter back to -1 or use the clear statement.

Using the set_config_parameter: 
(dbadmin@:5433) [dbadmin] * SELECT SET_CONFIG_PARAMETER('HistoryRetentionTime', '-1');
    SET_CONFIG_PARAMETER
----------------------------
 Parameter set successfully
(1 row)
Using the clear statement: 
(dbadmin@:5433) [dbadmin] *  ALTER DATABASE dcg023 clear HistoryRetentionTime;
ALTER DATABASE

Copyright 2020. All Rights Reserved