Tracking Raw Schema Size Over Time

Jim_Knicely · September 2019

There is no way to go back in time to see how large a schema was a day/month/year ago.

But you can use the AUDIT function to get a “raw” size of a schema and track growth over time by storing the results from a daily audit.

Example:

dbadmin=> CREATE EXTERNAL TABLE table_raw_size (audit_date DATE, table_schema VARCHAR(128), raw_size VARCHAR(100)) AS COPY FROM ‘/home/dbadmin/table_raw_size.txt’;
CREATE TABLE

dbadmin=> \! vsql -Atc “SELECT ‘SELECT sysdate::DATE, ”’ || schema_name || ”’, audit(”’ || schema_name || ”’);’ FROM schemata WHERE NOT is_system_schema;” |vsql -At >> /home/dbadmin/table_raw_size.txt

dbadmin=> SELECT * FROM table_raw_size ORDER BY audit_date, table_schema;
audit_date | table_schema | raw_size
————+———————+————-
2019-09-25 | P02R | 16384
2019-09-25 | b | 0
2019-09-25 | bnym | 14367347
2019-09-25 | chewy | 0
2019-09-25 | ctg_analytics | 0
2019-09-25 | dds | 1992179
2019-09-25 | entity | 0
2019-09-25 | etl_temp | 0
2019-09-25 | facts | 0
2019-09-25 | global_table_schema | 0
2019-09-25 | groupm_ee_gcp | 0
2019-09-25 | idea_dwh | 12288
2019-09-25 | integrate | 0
2019-09-25 | ix_platonic_tables | 54724325801
2019-09-25 | jack | 0
2019-09-25 | jim | 0
2019-09-25 | julie | 0
2019-09-25 | local_table_schema | 0
2019-09-25 | my_system_tables | 195015
2019-09-25 | my_system_views | 0
2019-09-25 | nba | 64
2019-09-25 | public | 16814285982
2019-09-25 | s1 | 0
2019-09-25 | stlouis | 398319385
2019-09-25 | v_func | 0
2019-09-25 | v_txtindex | 0
2019-09-25 | views | 4
2019-09-25 | walkme | 387
2019-09-25 | z | 0
(29 rows)

Using cron, you can run this daily.

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/LicenseManagement/AUDIT.htm

Have fun!