CODEWITHSUNDEEP
sqlpostgresql
wizengeeky
wizengeeky

Reputation: 67

Track table counts from a schema

I use postgres and I need some help from you all PG experts... I am looking to track counts from a large set of source tables whose counts keep changing everyday. I want to use the tablename, row count and tablesize in a tracker table, and a column called created_dttm field to show when this row count is recorded from source table. This is for trending how the table counts are changing with time and look for peaks.

insert into tracker_table( tablename, rowcount, tablesize, timestamp)
from 
(
(select schema.tablename ... - not sure how to drive this to pick up a list of tables??
, select count(*) from schema.tablename
, SELECT pg_size_pretty(pg_total_relation_size('"schema"."tablename"'))
, select created_dttm from schema.tablename
)
);

Additionally, I want to get a particular column from source table for a fourth column. This would be a created_dttm timestamp field in the source table, and I want to run a simple sql to get this date to the tracker table. Any suggestions how to attack this problem?

Upvotes: 0

Views: 276

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51456

before reading the code please consider this:

  1. instead of selecting several subqueries, this if you can join them into one qry, eg select (select 1 from t), (select 2 from t) can be refactored to select 1,2 from t
  2. pg_total_relation_size is sum of data pages, so it is size of table, but not size of data in it.
  3. you need aggregation on your created_dttm column (I used oid instead), otherwise your subquery returns more then one row, so you won't be able to insert the result.
  4. instead of select count(*) maybe use pg_stat_all_tables stats?.. counting can be very expensive and acuracy of the count() is neglected by the fact that next minute same select count() will be different and you probably wont run this count every two seconds...

code:

t=# create table so30 (n text, c int, s text, o int);
CREATE TABLE

t=# do
$$
declare
  _r record;
  _s text;
begin
  for _r in (values('pg_database'),('pg_roles')) loop
    _s := format('select %1$L,(select count(*) from %1$I), (SELECT pg_size_pretty(pg_total_relation_size(%1$L))), (select max(oid) from %1$I)',_r.column1);
    execute format('insert into so30 %s',_s);
  end loop;
end;
$$
;
DO
t=# select * from so30;
      n      | c |    s    |   o
-------------+---+---------+-------
 pg_database | 4 | 72 kB   | 16384
 pg_roles    | 2 | 0 bytes |  4200
(2 rows)

Upvotes: 1

Related Questions