How to get uncompressed size of tables in vertica?

[BNegetive]

Hello, i can't find an option to check the uncompressed size of tables in vertica, i need to migrate a cluster to new one with exports of data, and can't calculate the sizes on disk for the exports, without knowing the uncompressed size, vertica license checks the uncompressed size, so there should be an option to do it, can anyone help me with that?

p.s. i found user_audits, but it is not good because i need run audit on each table.


ok, i'v got answer in private message, thanks Daniel.

sorry i write here, but wanted to ask does it include the k-safety size or not?

edit:

ok, now i undesrtood it, thanks again Daniel, you are very helpful and solved my issue.

btw, why i can't answer pm's?

[adrian.oprea]

--run the audit_license_size()

Code: Select all

SELECT audit_license_size();

--this will populate the license_audits table
--it runs in the backgound but you can anticipate it's action

--Get your compression Ratio see the order by (will get you the last audit record)

Code: Select all

SELECT cast(database_size_bytes/(SELECT SUM(used_bytes) FROM v_monitor.projection_storage) as numeric(10,3)) as compression_ratio
          FROM v_catalog.license_audits
         ORDER BY audit_start_timestamp DESC LIMIT 1;

--calculate the stored data size of your table

Code: Select all

select cast(SUM(used_bytes) / (1024 ^ 3) AS numeric(3,3))AS compressed_space
  from projection_storage
 where anchor_table_name = 'StockTransaction_Fact';

--calculate the raw data size of your table

Code: Select all

select cast((compressed_space * compression_ratio)as numeric(10,3)) as raw_size;

-- You can also join all of this code and make a single script maybe to examine all of your tables.

Post the Solution that Daniel provided you ! Always great to have more resources on the forum.

[scutter]

Using the queries above to determine the overall compression ratio is a starting point, but personally I don’t rely on general formulas like that since each table’s compression ratio will be different. I do in fact do an audit() on every large table in order to understand how each table is contributing to the total license consumption. By doing that I have observed tables that use a surprisingly large license percent - much more than you’d expect — because of unfortunate data model decisions.

—Sharon

[NorbertKrupa]

--Get your compression Ratio see the order by (will get you the last audit record)

Code: Select all

SELECT cast(database_size_bytes/(SELECT SUM(used_bytes) FROM v_monitor.projection_storage) as numeric(10,3)) as compression_ratio
          FROM v_catalog.license_audits
         ORDER BY audit_start_timestamp DESC LIMIT 1;

I don't think buddy projections should count towards compression.

[adrian.oprea]

--Get your compression Ratio see the order by (will get you the last audit record)

Code: Select all

SELECT cast(database_size_bytes/(SELECT SUM(used_bytes) FROM v_monitor.projection_storage) as numeric(10,3)) as compression_ratio
          FROM v_catalog.license_audits
         ORDER BY audit_start_timestamp DESC LIMIT 1;

I don't think buddy projections should count towards compression.

Very good point , i have my testing lab on a one node DB (just need to add the is_super_projection = 't' to it)

Code: Select all

SELECT cast(database_size_bytes/(SELECT SUM(used_bytes) FROM v_monitor.projection_storage where anchor_table_name in (select anchor_table_name from projections where is_super_projection = 't')) as numeric(10,3)) as compression_ratio
          FROM v_catalog.license_audits order by audit_end_timestamp limit 1 ;