Understanding Database size and sizes of respective tables

Products

• VMware Smart Assurance

Issue/Introduction

Finding out the size of the tables in Smarts NCM (Voyence) database.

Environment

10.1.x - NCM

Resolution

• Launch a command prompt in Database (DB) server and log into the postgres with the following command sequence:
  
  su - pgdba -c 'psql voyencedb voyence'

         Provide password for user 'voyence' when prompted.
 

• Once logged in, run the below SQL select command:
  • select * from cm_total_db_size;

                   to get entire DB size.

 

• • select * from cm_calculate_table_sizes;
    to get size by each table, i.e the above query should produce output similar to the following for all of the tables in the database:
    
    voyencedb=# SELECT * from cm_calculate_table_sizes;

                   relation_name                    | table_size_pretty | table_size

----------------------------------------------------+-------------------+------------

 voyence.cm_config_audit_test                       | 1232 kB           |    1261568

 voyence.cm_config_file                             | 1096 kB           |    1122304

 voyence.cm_task_action                             | 784 kB            |     802816

 voyence.cm_cel_audit_record                        | 712 kB            |     729088

 voyence.cm_filter                                  | 552 kB            |     565248