1

If you google for "postgresql table size" you get this nice query. https://wiki.postgresql.org/wiki/Disk_Usage

SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , 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
) a;

I have a schema app:

enter image description here

But doesnt show in the result:

enter image description here

Why app schema doesnt show in the result?

I try a second query, and also returm empty result.

select table_name, pg_relation_size(table_name)
from information_schema.tables
where table_schema = 'app'
order by 2
Community
  • 1
  • 1
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118

1 Answers1

0

Unfortunately even psql command line tool does not show total size of all objects in schema. All commands like "\l+" for databases or "\dt+" for tables show in "+" form also total size. Only "\dn+" for schemas does not show size.

Select from pg_class is probably the best but do not filter only for "r" - schemas contain also indexes and other objects.

JosMac
  • 2,164
  • 1
  • 17
  • 23
  • the problems isnt object in schema, is the schema is totally missing – Juan Carlos Oropeza Dec 05 '16 at 17:58
  • Because you select from pg_class and it contains only "r = ordinary table, i = index, S = sequence, v = view, m = materialized view, c = composite type, t = TOAST table, f = foreign table" - see here https://www.postgresql.org/docs/current/static/catalog-pg-class.html. Procedures are listed in pg_proc and types in pg_type – JosMac Dec 08 '16 at 18:38
  • mmm now I read your answer again , looks like you say the same, but I didnt understand it that day. – Juan Carlos Oropeza Dec 08 '16 at 18:40