postgres calculate changes in db - ghdrako/doc_snipets GitHub Wiki

Analiza przyrostow nie uwzglednia operacji update i delety kompesnsuja sie z insertami

#Each row in PostgreSQL is one event. To calculate the event size in PostgreSQL, we run the following queries 
#The query to find the size in kb of entire table is : 
Select pg_size_pretty( pg_total_relation_size('Table_Name')); 
#The number of rows in the table is found with the following query: 
select count(*) from Table_Name; 
#To find the total number of records in all the tables in the database, you can analyze the database and then look at the stats table. 
#This can also be re-run the next day and a difference report can be generated that will show the daily number of inserts. 
Run “ANALYZE VERBOSE;” – This will analyze all tables in the database that it is executed in. 
#Query to pull the schema, tables, rows, and date. 
SELECT schemaname "SCHEMA", 
       relname "TABLE", 
       n_live_tup "RECORDS", 
       last_analyze "DATE" 
  FROM pg_catalog.pg_stat_user_tables 
 ORDER BY schemaname, 
          relname ; 
#Event size = size of table/number of rows in table 

Select pg_size_pretty(pg_total_relation_size('"Retailcdc"')); 

Select count(*) from "Retailcdc"; 

#Postgres database – database size 
SELECT schema_name, 
       sum(table_size), 
       sum(database_size), 
       (sum(table_size) / database_size) * 1000 
  FROM ( SELECT pg_catalog.pg_namespace.nspname as schema_name, 
                pg_relation_size(pg_catalog.pg_class.oid) as table_size, 
                sum(pg_relation_size(pg_catalog.pg_class.oid)) over () as database_size 
           FROM pg_catalog.pg_class 
           JOIN pg_catalog.pg_namespace 
             ON relnamespace = pg_catalog.pg_namespace.oid 
       ) t 
 GROUP BY schema_name, 
          database_size;