Tracking Table Row Counts Over Time: Quick Tip

Posted February 15, 2019 by Jim Knicely, Vertica Principal Solution Architect

I would like to track the row counts from all of my database tables over time. Doing so will help me predict future growth.

How do I do that? Like this…

Example: dbadmin=> CREATE TABLE table_row_count(audit_date DATETIME, table_schema VARCHAR(128), table_name VARCHAR(128), row_count INT) dbadmin-> ORDER BY table_schema, table_name, audit_date UNSEGMENTED ALL NODES; CREATE TABLE dbadmin=> \! vsql -Atc "SELECT 'SELECT sysdate || \$\$|' || table_schema || '|' || table_name || '|\$\$ || (SELECT COUNT(*) FROM \"' || table_schema || '\".\"' || table_name || '\")' || NVL2(LEAD(table_schema) OVER (ORDER BY table_schema), ' UNION', ';') FROM tables WHERE table_schema || '.' || table_name <> 'public.table_row_count';" | vsql -At | vsql -c "COPY table_row_count FROM STDIN DIRECT;" dbadmin=> SELECT * FROM table_row_count ORDER BY row_count DESC; audit_date | table_schema | table_name | row_count ----------------------------+--------------+-----------------+----------- 2019-02-14 08:19:22.406804 | Happy | Valentine's Day | 117440512 2019-02-14 08:19:22.406804 | public | table_b | 12288 2019-02-14 08:19:22.406804 | public | parts | 8 (3 rows) Are the row counts accurate?

Yup!

Now I can add my table row count job to CRONTAB so that it runs once a day at 11:59 PM. [dbadmin@s18384357 ~]$ cat table_row_count.sh . ~/.bashrc /opt/vertica/bin/vsql -Atc "SELECT 'SELECT sysdate || \$\$|' || table_schema || '|' || table_name || '|\$\$ || (SELECT COUNT(*) FROM \"' || table_schema || '\".\"' || table_name || '\")' || NVL2(LEAD(table_schema) OVER (ORDER BY table_schema), ' UNION', ';') FROM tables WHERE table_schema || '.' || table_name <> 'public.table_row_count';" | /opt/vertica/bin/vsql -At | /opt/vertica/bin/vsql -c "COPY table_row_count FROM STDIN DIRECT;" [dbadmin@s18384357 ~]$ crontab -l # Minute Hour Day Month Day of Week Command 5 3 * * * /opt/vertica/oss/python/bin/python -m vertica.do_logrotate &> /dev/null 59 23 * * * /home/dbadmin/table_row_count.sh In a few months I’ll have a pretty good history of how my tables are growing and/or shrinking!

Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/COUNTAggregate.htm/br>
https://www.vertica.com/docs/latest/HTML/Content/Authoring/ConnectingToVertica/vsql/CommandLineOptions.htm/br>
https://www.vertica.com/docs/latest/HTML/Content/Authoring/ConnectingToVertica/vsql/CopyingDataUsingVsql.htm Have fun!