how can one monitor daily growth of individual tables

dgrumanndgrumann Employee, Registered User

I have seen some queries that use v_monitor.projection_storage and v_catalog.license_audits to come up with compressed and raw sizes for whole schemas, and I know one can use the audit function and the resulting v_catalog.user_audits to see a snapshot of the raw size of individual tables, but I do not know of a way to query the growth of DB tables day by day. Does someone have such a query? Monitoring daily growth of tables could help tuning, sizing and help configure retention settings.

Comments

  • dgrumanndgrumann Employee, Registered User
    edited June 3

    ``# example MB growth by day for whole DB, but not by table:
    select date_trunc('minute',audit_start_timestamp), cast(database_size_bytes / (1024^3) as decimal(6,0)) as "compressed size GB", cast(database_size_bytes / (1024^2) as decimal(10,0)) as "compressed size MB", cast((database_size_bytes - lead(database_size_bytes,1) over (order by audit_start_timestamp desc)) / (1024^2) as decimal(8,0)) as 'MB growth' from v_catalog.license_audits where database_size_bytes > 0 and audited_data = 'Total' order by audit_start_timestamp desc limit 7;

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file