How to check disk-usage in specific schema and sum for all schema? pg9+

Question

The documentation about disk-usage have no "fast and simple" clue or information... So is faster to check here.

When I try SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'big' it is working fine... but "big" is not a "public.big", it is a "othername.big", if there are public also, will be ambiguous.
When I try SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'othername.big' not works.

I need to compare disk-usage of tables and to check (or sum) all schema disk-usage.

score 1 · Accepted Answer · edited Nov 18 '17 at 01:01

To get Schema size :

SELECT schemaname, pg_size_pretty(t.taille::bigint) AS taille_table, pg_size_pretty(t.taille_totale::bigint) AS taille_totale_table
  FROM (SELECT schemaname,          
               sum(pg_relation_size(schemaname || '.' || tablename)) AS taille, 
               sum(pg_total_relation_size(schemaname || '.' || tablename)) AS taille_totale
          FROM pg_tables
          WHERE relname_exists(tablename,schemaname)   -- see note
GROUP BY schemaname) as t ORDER BY taille_totale DESC;

And for Tables by Schema, you can do this :

SELECT schemaname, tablename, tablespace, pg_size_pretty(taille) AS taille_table, pg_size_pretty(taille_totale) AS taille_totale_table
  FROM (SELECT *, 
               pg_relation_size(schemaname || '.' || tablename) AS taille, 
               pg_total_relation_size(schemaname || '.' || tablename) AS taille_totale
          FROM pg_tables) AS tables
          WHERE relname_exists(tablename,schemaname)   -- see note
 ORDER BY taille_totale DESC;

NOTE: to avoid "ERROR: relation 'x.y' does not exist", needs to guard the string before use it, so

use to_regclass(rel_name) in Postgres 9.4+... See https://stackoverflow.com/a/24089729

or use a generic (see "SwissKnife libraries") function for any pg version, as below

CREATE or replace FUNCTION relname_exists(text,text default NULL) RETURNS boolean AS $$
  SELECT EXISTS (
     SELECT 1
     FROM   pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace,
            regexp_split_to_array($1,'\.') t(x) -- not work with quoted names
     WHERE  CASE
            WHEN COALESCE(x[2],'')>'' THEN   n.nspname = x[1]      AND c.relname = x[2]
            WHEN $2 IS NULL THEN         n.nspname = 'public'  AND c.relname = $1
            ELSE                         n.nspname = $2        AND c.relname = $1
     END
  )
$$ language SQL IMMUTABLE;

Thanks Hervé, I think it is a good solution, but something in a "cache" that is not a good string for pg_Etc functions, error at my database. " ERROR: relation 'public.t1c-transcimg' does not exist" — Peter Krauss, Nov 17 '17 at 19:53
OK, I had this problem also, it's because you used camel case to name your table. In this case two choices : rename the table using double quote to name this table, or simply modify my request to double quote field used for tablename. — Hervé Piedvache, Nov 19 '17 at 13:49

score 0 · Answer 2 · answered Nov 19 '17 at 17:27

Using pg_class and simplifying layout

Same results as Hervé's queries (that used pg_tables)... Was adapted from https://wiki.postgresql.org/wiki/Disk_Usage

-- -- -- -- --
-- DISK-USAGE

CREATE VIEW pgvw_class_usage AS
  SELECT *, pg_size_pretty(table_bytes) AS table_size
  FROM (
    SELECT nspname , relname, total_bytes
           , total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes
    FROM (
        SELECT nspname , relname
          , pg_total_relation_size(c.oid) AS total_bytes
          , pg_indexes_size(c.oid) AS index_bytes
          , pg_total_relation_size(reltoastrelid) AS toast_bytes
        FROM pg_class c
        LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE relkind = 'r'
    ) a
  ) t
  ORDER BY 1,2
; -- eg. SELECT * FROM pgvw_class_usage WHERE relname='foo' AND nspname='bar';

CREATE VIEW pgvw_nsclass_usage AS
  SELECT *, pg_size_pretty(table_bytes) as table_size
  FROM (
    SELECT nspname, count(*) as n_tables,
           sum(total_bytes) as total_bytes, sum(table_bytes) as table_bytes
    FROM pgvw_class_usage
    GROUP BY nspname
  ) t
; -- eg. SELECT * FROM pgvw_nsclass_usage WHERE nspname='bar';

EXAMPLE:

      nspname       | n_tables | total_bytes | table_bytes | table_size 
--------------------+----------+-------------+-------------+------------
 bench1             |        8 |     4718592 |     3825664 | 3736 kB
 dataset            |        4 |     8552448 |     6225920 | 6080 kB
 information_schema |        7 |      352256 |      294912 | 288 kB
 pg_catalog         |       54 |     9003008 |     4734976 | 4624 kB
(4 rows)

How to check disk-usage in specific schema and sum for all schema? pg9+

2 Answers2

Using pg_class and simplifying layout