Question 89349 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL Monthly/Daily Database growth

My goal is to generate a report showing the number of records saved each day/month in a PostgreSQL database under a specific schema.

So far i have something simiilar byt with size (MB) not count and with overal sum

SELECT
     table_schema || '.' || table_name AS table_full_name,
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
where table_schema = 'public'
ORDER BY
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;

enter image description here

Is there any solution to generate such report:

TABLE NAME count data
foo 312 30-03-2023
foo 111 29-03-2023
bar 312 30-03-2023
bar 344 29-03-2023
Tags:

2

Answers


  1. 0

    If you can afford to get approximate counts (maintained by stuff like vacuum analyze statements), then you could use such a query:

    create view table_stat as 
select format('%I.%I', n.nspname, c.relname) "table name",
  reltuples "count",
  pg_size_pretty(pg_total_relation_size(c.oid)) "size",
  now()::date "date"
from pg_class c
join pg_namespace n on c.relnamespace = n.oid 
where n.nspname = 'public' 
and c.relkind  in ('r', 'm', 'p')
order by 3 desc;

    then create a table once and insert first batch:

    create table stat_history as select * from table_stat;

    Then in a cron or something, to append new data:

    insert into stat_history select * from table_stat;

    Then, the final query to display results:

    select * from stat_history -- where "whatever you want";

    Note the usage of format('%I') or quote_ident() to always get the correct escaping of double quotes.

    Otherwise, you will need to use dynamic queries, using plpgsql stuff like:

    Login or Signup to reply.
  2. 0

    Take a look at pg_stat_all_tables system view:

    create table test as select 1 AS a;

select n_tup_ins,n_tup_upd,n_tup_del,n_tup_hot_upd,n_live_tup,n_dead_tup 
from pg_stat_all_tables where schemaname='public' and relname='test';
-- n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup
-------------+-----------+-----------+---------------+------------+------------
--         1 |         0 |         0 |             0 |          1 |          0

insert into test select 2; 
insert into test select 3; 
delete from test where a=1; 
update test set a=5 where a=2;

select n_tup_ins,n_tup_upd,n_tup_del,n_tup_hot_upd,n_live_tup,n_dead_tup 
from pg_stat_all_tables where schemaname='public' and relname='test';
-- n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup
-------------+-----------+-----------+---------------+------------+------------
--         3 |         1 |         1 |             1 |          2 |          2

    You can take snapshots of this view daily using cron or pg_cron to monitor not only the estimated number of rows but also traffic leading to that number.

    To see all that traffic live, you can tap into the logical streaming replication protocol using pg_recvlogical.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search