Auditing database size

You can use your Vertica software until columnar data reaches the maximum raw data size that your license agreement allows. Vertica periodically runs an audit of the columnar data size to verify that your database complies with this agreement. You can also run your own audits of database size with two functions:

• AUDIT: Estimates the raw data size of a database, schema, or table.

• AUDIT_FLEX: Estimates the size of one or more flexible tables in a database, schema, or projection.

The following two examples audit the database and one schema:

=> SELECT AUDIT('', 'database');
  AUDIT
----------
 76376696
(1 row)

=> SELECT AUDIT('online_sales', 'schema');
  AUDIT
----------
 35716504
(1 row)

Raw data size

AUDIT and AUDIT_FLEX use statistical sampling to estimate the raw data size of data stored in tables—that is, the uncompressed data that the database stores. For most data types, Vertica evaluates the raw data size as if the data were exported from the database in text format, rather than as compressed data. For details, see Evaluating Data Type Footprint.

By using statistical sampling, the audit minimizes its impact on database performance. The tradeoff between accuracy and performance impact is a small margin of error. Reports on your database size include the margin of error, so you can assess the accuracy of the estimate.

Data in ORC and Parquet-based external tables are also audited whether they are stored locally in the Vertica cluster's file system or remotely in S3 or on a Hadoop cluster. AUDIT always uses the file size of the underlying data files as the amount of data in the table. For example, suppose you have an external table based on 1GB of ORC files stored in HDFS. Then an audit of the table reports it as being 1GB in size.

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.

Evaluating data type footprint

Vertica evaluates the footprint of different data types as follows:

• Strings and binary types—CHAR, VARCHAR, BINARY, VARBINARY—are counted as their actual size in bytes using UTF-8 encoding.

• Numeric data types are evaluated as if they were printed. Each digit counts as a byte, as does any decimal point, sign, or scientific notation. For example, -123.456 counts as eight bytes—six digits plus the decimal point and minus sign.

• Date/time data types are evaluated as if they were converted to text, including hyphens, spaces, and colons. For example, vsql prints a timestamp value of 2011-07-04 12:00:00 as 19 characters, or 19 bytes.

• Complex types are evaluated as the sum of the sizes of their component parts. An array is counted as the total size of all elements, and a ROW is counted as the total size of all fields.

Controlling audit accuracy

AUDIT can specify the level of an audit's error tolerance and confidence, by default set to 5 and 99 percent, respectively. For example, you can obtain a high level of audit accuracy by setting error tolerance and confidence level to 0 and 100 percent, respectively. Unlike estimating raw data size with statistical sampling, Vertica dumps all audited data to a raw format to calculate its size.

The following example audits the database with 25% error tolerance:

=> SELECT AUDIT('', 25);
  AUDIT
----------
 75797126
(1 row)

The following example audits the database with 25% level of tolerance and 90% confidence level:

=> SELECT AUDIT('',25,90);
  AUDIT
----------
 76402672
(1 row)