postgres calculate changes in db sizing - 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;

Rozmiar bieżącej bazy danych

SELECT pg_size_pretty(pg_database_size(current_database())) AS rozmiar; 

Rozmiar wszystkich baz na serwerze

SELECT
  d.datname                                    AS baza,
  pg_size_pretty(pg_database_size(d.datname)) AS rozmiar
FROM pg_database d
ORDER BY pg_database_size(d.datname) DESC;

Rozmiar poszczególnych tabel

SELECT
  schemaname || '.' || relname            AS tabela,
  pg_size_pretty(pg_total_relation_size(relid)) AS rozmiar
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

Przybliżony rozmiar obiektów indeksowych

SELECT
  schemaname || '.' || relname                AS tabela,
  pg_size_pretty(pg_indexes_size(schemaname || '.' || relname)) AS rozmiar_indeksow
FROM pg_stat_user_tables
ORDER BY pg_indexes_size(schemaname || '.' || relname) DESC
LIMIT 20;

Rozmiar w systemie calejistancji

du -sh /var/lib/postgresql/14/main
du -sh $PGDATA

Rozmiar schematu

SELECT
  pg_size_pretty(
    SUM(
      pg_total_relation_size(
        format('%I.%I', schemaname, tablename)::regclass
      )
    )
  ) AS rozmiar_schematu
FROM pg_tables
WHERE schemaname = 'nazwa_twojego_schematu';

Rozmiar wszystkich schematow bez systemowych

SELECT
  n.nspname                           AS schemat,
  pg_size_pretty(sum(pg_total_relation_size(c.oid))) AS rozmiar
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'               -- tylko tabele
  AND n.nspname NOT IN ('pg_catalog','information_schema')
GROUP BY n.nspname
ORDER BY sum(pg_total_relation_size(c.oid)) DESC;

-- alternatywnie
SELECT
  schemaname                          AS schemat,
  pg_size_pretty(
    SUM(
      pg_total_relation_size(
        format('%I.%I', schemaname, relname)::regclass
      )
    )
  ) AS rozmiar
FROM pg_stat_user_tables
GROUP BY schemaname
ORDER BY SUM(
           pg_total_relation_size(
             format('%I.%I', schemaname, relname)::regclass
           )
         ) DESC;

Ilosc danych bez dead tuples i pustych przestrzeni

CREATE EXTENSION IF NOT EXISTS pgstattuple;

Pojedyncza tabel

SELECT * FROM pgstattuple('schemat.nazwa_tabeli');

Wszystkie w schemacie

WITH stats AS (
  SELECT
    n.nspname AS schemat,
    c.relname AS tabela,
    s.*
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
  -- bierzemy tylko zwykłe tabele
  WHERE c.relkind = 'r'
    AND n.nspname NOT IN ('pg_catalog','information_schema')
  -- dla każdej tabeli wywołujemy pgstattuple
  CROSS JOIN LATERAL pgstattuple(format('%I.%I', n.nspname, c.relname)) AS s
)
SELECT
  schemat,
  sum(tuple_len)       AS żywe_dane_bytes,
  sum(dead_tuple_len)  AS dead_tuples_bytes,
  sum(free_space)      AS wolna_przestrzeń_bytes,
  pg_size_pretty(sum(tuple_len))      AS żywe_dane,
  pg_size_pretty(sum(dead_tuple_len)) AS dead_tuples,
  pg_size_pretty(sum(free_space))     AS wolna_przestrzeń
FROM stats
GROUP BY schemat
ORDER BY sum(tuple_len) DESC;