The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Checking the Validity of a Table Audit

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.


First let’s audit the table BIG_TABLE.

[[email protected] ~]$ /opt/vertica/bin/vsql -c "SELECT audit('public.big_table', 0, 100);"
(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.

[[email protected] ~]$ /opt/vertica/bin/vsql -o /home/dbadmin/big_table.out -F '' -R '' -At -c "SELECT * FROM public.big_table;" -q

Here is the exported file size:

[[email protected] ~]$ stat -c %s /home/dbadmin/big_table.out

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 training new line character in the file. Let’s get rid of it!

[[email protected] ~]$ printf %s "$(< /home/dbadmin/big_table.out)" > /home/dbadmin/big_table_no_trailing_nl.out

[[email protected] ~]$ stat -c %s /home/dbadmin/big_table_no_trailing_nl.out

Now we are 100% accurate!

Helpful Links:

Have fun!


  • Jim_KnicelyJim_Knicely Administrator

    Fyi... Instead of having to create the output file as I did in in the example above, you can stream the output from VSQL to WC to get the output size in bytes:


    [[email protected] ~]$ /opt/vertica/bin/vsql -c "SELECT audit('public.big_table2', 0, 100);"
    (1 row)
    [[email protected] ~]$ /opt/vertica/bin/vsql -F '' -R '' -At -c "SELECT * FROM public.big_table2;" -q | wc -c


Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.