The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Tracking Raw Schema Size Over Time

Jim_KnicelyJim_Knicely - Select Field - Administrator

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!

Sign In or Register to comment.