how I can see database size correctly (raw and compressed)

aleksss55

Hello, I can't understand how I can see database size correctly (raw and compressed)
I see compressed database size:
select sum(used_bytes) from projection_storage;
I get output 1139051427 bytes or 1139 Мb

I see raw database size:
select audit ('');
I get output 229751617 bytes or 229 Мb

Why raw database size in 5 times less than compressed?
It has to be vice versa or do I see database size not correctly?

Jim_Knicely

Are you running Eon or Enterprise?

Do you have a lot of projections per anchor table? Or a lot of replicated projections?

SELECT anchor_table_name, COUNT(*) FROM projections GROUP BY anchor_table_name ORDER BY 2 DESC LIMIT 5;
SELECT COUNT(*) FROM projections WHERE NOT is_segmented;

Note: Data in TEMP tables is included in PROJECTION_STORAGE but not an AUDIT().

aleksss55

I have an enterprise mode (3 nodes in cluster)
This is the output of the commands above

SELECT anchor_table_name, COUNT(*) FROM projections GROUP BY anchor_table_name ORDER BY 2 DESC LIMIT 5;
     anchor_table_name     | COUNT
---------------------------+-------
 stream_sources            |     7
 stream_microbatches       |     7
 stream_microbatch_history |     6
 stream_targets            |     5
 stream_clusters           |     4
(5 rows)

 SELECT COUNT(*) FROM projections WHERE NOT is_segmented;
 COUNT
-------
    21
(1 row)

LenoyJ

@aleksss55 said:
Hello, I can't understand how I can see database size correctly (raw and compressed)
I see compressed database size:
select sum(used_bytes) from projection_storage;
I get output 1139051427 bytes or 1139 Мb

Just querying projection_storage won't be accurate when comparing to the results from the audit. Projection_storage includes buddy projections and other type of projections which are all not included in the license audit. Most types of 'duplicated' data aren't considered for license audit (IMO, one of the cool perks of using Vertica ). That's why you see license audit size drastically smaller than projection_storage.
 
According to https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/AdministratorsGuide/Licensing/CalculatingTheDatabaseSize.htm:

Unaudited Data
Table data that appears in multiple projections is counted only once. An audit also excludes the following data:

• Temporary table data.
• Data in SET USING columns.
• Non-columnar data accessible through external table definitions. Data in columnar formats such as ORC and Parquet count against your totals.
• Data that was deleted but not yet purged.
• Data stored in system and work tables such as monitoring tables, Data Collector tables, and Database Designer tables.
• Delimiter characters.