[SOLVED] How to find the difference between two table sizes in postgres using shell script

Issue

I have one table named 'Table_size_details' in my database which stores the size of the tables in my Postgres database, every Friday.

Date         Table_Name   Table_size  Growth_Difference  Growth_Percentage
----         -----------  ----------  -----------------  -----------------
20-08-2021   Demo         1.2 GB
13-08-2021   Demo         578 MB

I have got a task to add two more columns named 'Growth_Difference' and 'Growth_Percentage'. In 'Growth_Difference' column I need to find the difference between current table size(1.3 GB) and previous week table size(578 MB) and display it in MB format. Also I need to find the growth percentage of both- the current table size and previous week's table size. I have asked to develop using SHELL SCRIPT.

Table_size_old=`psql -d abc -At -c "SELECT Table_size_details from abc order by Date desc limit 1;"`

Table_size_new=`psql -At -c "SELECT pg_size_pretty(pg_total_relation_size('Table_size_details'));`

growth_table=`expr $Table_size_new - $Table_size_old;`

Above logic I have used to find the difference between new and old table size but I'm getting expr: syntax error on growth_table variable line. I believe its because I trying to find the difference between 1.2 GB and 578 MB. I'm new to shell scripting, could anyone help me to find a solution?

Appreciate your help in advance.

Solution

There is no need to even attempt this is a shell script, further since both growth columns are computed values there is no need to store them. This can be done in a single query, or perhaps even better a single query that populates a view. With that getting what you are looking for is a simple Select from that view.
First off however do not store your size as a string with number and unit size code. Store instead a single numeric value in a constant unit size, then convert all values to that constant unit size. For example select GB as the constant unit, then 587MB would be stored as .587, this way there is no unit conversion needed. With that done (or added) create a view as follows:

create or replace view table_size_growth as 
    select table_name
         , run_date
         , size_in_gb
         , Round( (size_in_gb - gb_last_week)::numeric,6) growth_in_gb 
         , case when gb_last_week < 0.0000001          -- set to desired precision
                then null::double precision 
                else round((100 * (size_in_gb - gb_last_week)/abs(gb_last_week))::numeric,6)  
           end growth_in_pct
      from (select ts.*, lag(ts.size_in_gb) over( partition by ts.table_name
                                                       order by ts.run_date) gb_last_week 
              from table_size_details ts
           ) s
    order by table_name, run_date; 

Your script (or anywhere else) now needs the single query: select * from table_size_growth Note: this provides every week for every table you are capturing. Use where clause as needed. See example here.

Answered By - Belayer