Tracking Table Row Counts Over Time

Jim_Knicely

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

How do I 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?

dbadmin=> SELECT COUNT(*) FROM "Happy"."Valentine's Day";
   COUNT
-----------
117440512
(1 row)

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/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/COUNTAggregate.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ConnectingToVertica/vsql/CommandLineOptions.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ConnectingToVertica/vsql/CopyingDataUsingVsql.htm

Have fun!