How to free up vertica raw space after deleting records

Question

We are using Vertica community edition which has raw data limit of 1TB.

recently reached 1 TB raw data limit so we decided to delete some records from all tables. After deletion of old records Vertica still shows Utilization : 104%

dbadmin=> SELECT GET_COMPLIANCE_STATUS();
GET_COMPLIANCE_STATUS
----------------------------------------------------------------------------------------
Raw Data Size: 1.04TB +/- 0.10TB
License Size : 1.00TB
Utilization  : 104%
Audit Time   : 2014-09-04 13:05:24.020979-04
Compliance Status : The database is in compliance with respect to raw data size.

No expiration date for a Perpetual license

NOTICE: Recent audits suggests a change in compliance status. We are awaiting additional data points to confirm.
(1 row)

Any idea how to free up that space ?

Kermit · Accepted Answer · 2014-09-04T23:44:24.673

11

Rows that were deleted using DELETE are marked for deletion and not immediately removed from physical storage. You need to wait for a mergeout to occur, advance the epoch, or run a PURGE. More information about purging deleted data is available in the documentation.

edited Sep 04 '14 at 23:44

answered Sep 04 '14 at 23:01

Kermit

33,827
13
85
121

score 3 · Answer 2 · answered Sep 05 '14 at 14:10

3

Besides Kermit's answer...

Since deletes are expensive I used to put data on partitions, based around date, and after archiving the data somewhere else I would drop the partition.

answered Sep 05 '14 at 14:10

geoffrobinson

1,580
3
15
23

+1 I would hope OP reads the grey note in the documentation about doing just that. – Kermit Sep 05 '14 at 14:29

score 3 · Answer 3 · edited Oct 01 '14 at 11:12

3

The records marked for deletion and pointed by delete vectors You can find the count of delete vectors by the following query

select count(1) from delete_vectors;

To delete the delete vectors You can write a script which contains

select make_ahm_now()
select purge()

And then schedule to run this script

edited Oct 01 '14 at 11:12

Pang

9,564
146
81
122

answered Oct 01 '14 at 11:02

kc10

31
1

score 1 · Answer 4 · answered Oct 23 '15 at 08:50

Deleted records are excluded when computing database size. So there is no urgency in immediately purging the delete vectors.

To immediately see the effect of your deletion on your license compliance, you can trigger an immediate audit with

select audit_license_size();

As others have said

select make_ahm_now(); select purge();

will remove the deleted records permanently but these operations can be very time consuming and will block operations like truncate(). You can reduce the disruption by using

  select purge_table('<your table name');

instead of

  select purge()

How to free up vertica raw space after deleting records

4 Answers4