How to know which DB objects caused a high DB storage growth rate?

(1)

Create the table rep_tmp_check_postgres_db_objects_size_growth_rate

create table if not exists rep_tmp_check_postgres_db_objects_size_growth_rate
(dt timestamp,
dt_yyyy char(4),
dt_mm char(2),
dt_dd char(2),
dt_hh char(2),
dt_mi char(2),
dt_ss char(2),
db_name varchar(100),
obj_name varchar(100),
obj_type varchar(100),
n_of_entries numeric,
obj_size numeric,
obj_size_kb numeric,
obj_size_mb numeric,
obj_size_gb numeric);

(2) Run the following query once an hour, every insert will "take a picture" of the DB objects and their sizes:

insert into rep_tmp_check_postgres_db_objects_size_growth_rate
select
now(),
to_char(now(), 'YYYY'),
to_char(now(), 'MM'),
to_char(now(), 'DD'),
to_char(now(), 'HH24'),
to_char(now(), 'MI'),
to_char(now(), 'SS'),
'<database_name>',
relname,
case
when relkind = 'r' then 'Table'
when relkind = 'i' then 'Index'
when relkind = 's' then 'Sequence'
when relkind = 'v' then 'View'
when relkind = 'm' then 'Materialized View'
when relkind = 'c' then 'Composite Type'
when relkind = 't' then 'Toast Table'
when relkind = 'f' then 'Foreign Table'
else relkind
end reltype,
reltuples,
relpages::bigint*8*1024,
round(relpages::bigint*8*1024/1024),
round(relpages::bigint*8*1024/1024/1024),
round(relpages::bigint*8*1024/1024/1024/1024)
from pg_class
where relkind in ('r', 'i', 'm');

(3) This will list all populated snapshots:

select distinct dt_yyyy||dt_mm||dt_dd||'-'||dt_hh||dt_mi||dt_ss from rep_tmp_check_postgres_db_objects_size_growth_rate order by 1;

(4) Compare any snapshots and see which objects have the most growth rate:

select
all_the_objs.db_name,
all_the_objs.obj_name,
all_the_objs.obj_type,
a.obj_size obj_size_start_snapshot,
b.obj_size obj_size_end_snapshot,
b.obj_size - a.obj_size as delta_size_B,
round((b.obj_size - a.obj_size)/1024/1024, 2) as delta_size_MB,
round(100 * (b.obj_size - a.obj_size) / a.obj_size, 2) percent_change
from
(select distinct db_name, obj_name, obj_type from rep_tmp_check_postgres_db_objects_size_growth_rate) all_the_objs
left outer join (select * from rep_tmp_check_postgres_db_objects_size_growth_rate where dt_yyyy||dt_mm||dt_dd||'-'||dt_hh||dt_mi||dt_ss = '$start_snapshot') a on a.db_name = all_the_objs.db_name and a.obj_name = all_the_objs.obj_name and a.obj_type = all_the_objs.obj_type
left outer join (select * from rep_tmp_check_postgres_db_objects_size_growth_rate where dt_yyyy||dt_mm||dt_dd||'-'||dt_hh||dt_mi||dt_ss = '$end_snapshot') b on b.db_name = all_the_objs.db_name and b.obj_name = all_the_objs.obj_name and b.obj_type = all_the_objs.obj_type
where (b.obj_size - a.obj_size) > 0
and all_the_objs.obj_name not in ('rep_tmp_check_postgres_db_objects_size_growth_rate')
order by 6 desc;

Example of run:

(1) Create the repository to collect the data:

postgres=> create table if not exists rep_tmp_check_postgres_db_objects_size_growth_rate
postgres-> (dt timestamp,
postgres(> dt_yyyy char(4),
postgres(> dt_mm char(2),
postgres(> dt_dd char(2),
postgres(> dt_hh char(2),
postgres(> dt_mi char(2),
postgres(> dt_ss char(2),
postgres(> db_name varchar(100),
postgres(> obj_name varchar(100),
postgres(> obj_type varchar(100),
postgres(> n_of_entries numeric,
postgres(> obj_size numeric,
postgres(> obj_size_kb numeric,
postgres(> obj_size_mb numeric,
postgres(> obj_size_gb numeric);
CREATE TABLE
postgres=>

(2) On a regular basis populate the table created with data. It can run once an hour, once a minute, once a day, etc.

postgres=> insert into rep_tmp_check_postgres_db_objects_size_growth_rate
select
now(),
to_char(now(), 'YYYY'),
to_char(now(), 'MM'),
to_char(now(), 'DD'),
to_char(now(), 'HH24'),
to_char(now(), 'MI'),
to_char(now(), 'SS'),
'My_Database',
relname,
case
when relkind = 'r' then 'Table'
when relkind = 'i' then 'Index'
when relkind = 's' then 'Sequence'
when relkind = 'v' then 'View'
when relkind = 'm' then 'Materialized View'
when relkind = 'c' then 'Composite Type'
when relkind = 't' then 'Toast Table'
when relkind = 'f' then 'Foreign Table'
else relkind
end reltype,
reltuples,
relpages::bigint*8*1024,
round(relpages::bigint*8*1024/1024),
round(relpages::bigint*8*1024/1024/1024),
round(relpages::bigint*8*1024/1024/1024/1024)
from pg_class
where relkind in ('r', 'i', 'm');
INSERT 0 185
postgres=>

(3) List of snapshots populated:

postgres=> select distinct dt_yyyy||dt_mm||dt_dd||'-'||dt_hh||dt_mi||dt_ss from rep_tmp_check_postgres_db_objects_size_growth_rate order by 1;
?column?
-----------------
20210222-212032
20210222-212037
20210222-212041
20210222-212047
(4 rows)

postgres=>

(4) Now, let's compare growth rate between 20210222-212032 and 20210222-212047:

select
all_the_objs.db_name,
all_the_objs.obj_name,
all_the_objs.obj_type,
a.obj_size obj_size_start_snapshot,
b.obj_size obj_size_end_snapshot,
b.obj_size - a.obj_size as delta_size_B,
round((b.obj_size - a.obj_size)/1024/1024, 2) as delta_size_MB,
round(100 * (b.obj_size - a.obj_size) / a.obj_size, 2) percent_change
from
(select distinct db_name, obj_name, obj_type from rep_tmp_check_postgres_db_objects_size_growth_rate) all_the_objs
left outer join (select * from rep_tmp_check_postgres_db_objects_size_growth_rate where dt_yyyy||dt_mm||dt_dd||'-'||dt_hh||dt_mi||dt_ss = '20210222-212032') a on a.db_name = all_the_objs.db_name and a.obj_name = all_the_objs.obj_name and a.obj_type = all_the_objs.obj_type
left outer join (select * from rep_tmp_check_postgres_db_objects_size_growth_rate where dt_yyyy||dt_mm||dt_dd||'-'||dt_hh||dt_mi||dt_ss = '20210222-212047') b on b.db_name = all_the_objs.db_name and b.obj_name = all_the_objs.obj_name and b.obj_type = all_the_objs.obj_type
where (b.obj_size - a.obj_size) > 0
and all_the_objs.obj_name not in ('rep_tmp_check_postgres_db_objects_size_growth_rate')
order by 6 desc;


db_name | obj_name | obj_type | obj_size_start_snapshot | obj_size_end_snapshot | delta_size_b | delta_size_mb | percent_change
-------------+----------+----------+-------------------------+-----------------------+--------------+---------------+----------------
...
...
...

postgres=>