Skip to main content
Question

GET_COMPLIANCE_STATUS ==> Raw Data Size is displaying more than disk usage

  • May 18, 2022
  • 5 replies
  • 12 views

PavanVejju
Forum|alt.badge.img+1

Command: SELECT GET_COMPLIANCE_STATUS();
Current raw data size is 2TB
Disk usage is 1.5 TB

Why it is showing more than disk usage

5 replies

Bryan_H
Forum|alt.badge.img+2
  • Participating Frequently
  • May 18, 2022

License usage represents actual data size stored by Vertica.
Disk usage shows compressed data size recorded on disk. Disk usage is less than license due to compression and encoding.


PavanVejju
Forum|alt.badge.img+1
  • Author
  • Participating Frequently
  • May 19, 2022

SELECT * FROM DELETE_VECTORS;
For above command, it shows few records.

so if we execute purge(), will it minimise actual data size?
or
any alternative to minimise actual data size


PavanVejju
Forum|alt.badge.img+1
  • Author
  • Participating Frequently
  • May 19, 2022

Will projections storage is also consider as license usage


Bryan_H
Forum|alt.badge.img+2
  • Participating Frequently
  • May 19, 2022

Removing deleted data with PURGE() will reduce data size and license usage.
Projection storage does not count as license usage, as it is duplicate data.


mosheg
Forum|alt.badge.img+2
  • Participating Frequently
  • May 19, 2022

To minimize actual db size on disk, check you current status first.

SELECT AUDIT('','table',5,95);       -- error‑tolerance=5, confidence‑level=95%
SELECT anchor_table_schema || '.' || anchor_table_name AS S_TABLE,
            SUM(used_bytes) AS compressed_bytes,
            (SUM(used_bytes) / 1024^3)::NUMERIC(20,1)  AS compressed_GB,
            (SUM(used_bytes) / 1024^4)::NUMERIC(20,1)  AS compressed_TB,
            sum(row_count)  AS projections_row_count,
            count(distinct projection_id) as projections_number
     FROM v_monitor.projection_storage
     GROUP BY rollup(S_TABLE)
     ORDER BY 2 DESC;

Then drop unnecessary projections.
Try to choose better encoding and sort order on Correlated Columns for projections with weak compression.
Better encoding can improve your compression ratio and might effect queries performance, so it is advised first to check its size and performance on data sample.