
The Vertica AUDIT function can be used to estimate the raw data size of a database, schema, or table.
Ever wonder if it’s accurate? A simple test shows that it is 100 percent accurate if you specify an error tolerance of 0 and a confidence level of 100.
Example: First let’s audit the table BIG_TABLE.
[dbadmin@s18384357 ~]$ /opt/vertica/bin/vsql -c "SELECT audit('public.big_table', 0, 100);"
audit
----------
10696205
(1 row)
Next extract the data from the table to a file, eliminating all field separators and record terminators as Vertica does not include those bytes in the audit.
[dbadmin@s18384357 ~]$ stat -c %s /home/dbadmin/big_table.out
10696206
Wait a second! Why is the output file size 1 byte bigger than the result of the Vertica AUDIT function? It’s because there is a trailing new line character in the file. Let’s get rid of it!
[dbadmin@s18384357 ~]$ printf %s "$(< /home/dbadmin/big_table.out)" > /home/dbadmin/big_table_no_trailing_nl.out
[dbadmin@s18384357 ~]$ stat -c %s /home/dbadmin/big_table_no_trailing_nl.out
10696205
Now we are 100% accurate!Helpful Links:
https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/VerticaFunctions/LicenseManagement/AUDIT.htm
https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/AdministratorsGuide/Licensing/CalculatingTheDatabaseSize.htm
Have fun!