View to get postgres table sizes (table, toast, index and total size for each one)

README.md

postgres table sizes

The result will be like this:

 schema |                table                | row_estimate  | total_size | index_size | toast_size | table_size | table_size_ratio | avg_row_size | total_bytes | index_bytes | toast_bytes | table_bytes 
--------+-------------------------------------+---------------+------------+------------+------------+------------+------------------+--------------+-------------+-------------+-------------+-------------
 public | estabelecimento                     | 5.4933384e+07 | 17 GB      | 4901 MB    | 8192 bytes | 12 GB      |             0.72 |       332.22 | 18249842688 |  5139447808 |        8192 | 13110386688
 public | empresa                             |  5.211064e+07 | 9883 MB    | 3985 MB    | 8192 bytes | 5898 MB    |             0.60 |       198.87 | 10363297792 |  4178649088 |        8192 |  6184640512
 public | socio                               | 2.2281472e+07 | 4594 MB    | 1748 MB    | 8192 bytes | 2846 MB    |             0.62 |       216.19 |  4816994304 |  1833074688 |        8192 |  2983911424
 public | simples                             |  3.384048e+07 | 3297 MB    | 1018 MB    | 8192 bytes | 2279 MB    |             0.69 |       102.16 |  3457261568 |  1067253760 |        8192 |  2389999616
 public | receita_candidato                   |  3.236903e+06 | 1991 MB    | 0 bytes    | 8192 bytes | 1991 MB    |             1.00 |       645.04 |  2087944192 |           0 |        8192 |  2087936000
 public | regime_tributario                   |     9.028e+06 | 1086 MB    | 270 MB     | 8192 bytes | 816 MB     |             0.75 |       126.13 |  1138704384 |   283131904 |        8192 |   855564288
 public | doacao_candidatura                  |  2.330707e+06 | 748 MB     | 0 bytes    | 8192 bytes | 748 MB     |             1.00 |       336.31 |   783835136 |           0 |        8192 |   783826944
 public | candidatura                         |        617048 | 375 MB     | 0 bytes    | 8192 bytes | 375 MB     |             1.00 |       636.83 |   392953856 |           0 |        8192 |   392945664
 public | receita_candidato_doador_originario |        446080 | 123 MB     | 0 bytes    | 8192 bytes | 123 MB     |             1.00 |       288.69 |   128778240 |           0 |        8192 |   128770048
 public | municipio_uf                        |          5571 | 736 kB     | 352 kB     | 8192 bytes | 376 kB     |             0.51 |       135.28 |      753664 |      360448 |        8192 |      385024
 public | cnae                                |          1359 | 168 kB     | 0 bytes    | 8192 bytes | 160 kB     |             0.95 |       126.59 |      172032 |           0 |        8192 |      163840
 public | pais                                |           255 | 56 kB      | 0 bytes    | 8192 bytes | 48 kB      |             0.86 |       224.88 |       57344 |           0 |        8192 |       49152
 public | natureza_juridica                   |            90 | 48 kB      | 0 bytes    | 8192 bytes | 40 kB      |             0.83 |       546.13 |       49152 |           0 |        8192 |       40960
 public | qualificacao_socio                  |            68 | 48 kB      | 0 bytes    | 8192 bytes | 40 kB      |             0.83 |       722.82 |       49152 |           0 |        8192 |       40960
 public | motivo_situacao_cadastral           |            61 | 48 kB      | 0 bytes    | 8192 bytes | 40 kB      |             0.83 |       805.77 |       49152 |           0 |        8192 |       40960

postgres_table_size_report.sql

-- This code is based on <https://wiki.postgresql.org/wiki/Disk_Usage>
-- Changed:
-- - Added average row size
-- - Added values in bytes
-- - Filter out tables from pg_catalog and information_schema
-- - Show materialized views
-- - Better indentation
DROP VIEW IF EXISTS size_report;
CREATE VIEW size_report AS
  WITH RECURSIVE pg_inherit(inhrelid, inhparent) AS (
    SELECT
      inhrelid,
      inhparent
      FROM pg_inherits
    UNION
    SELECT
      child.inhrelid,
      parent.inhparent
    FROM pg_inherit child, pg_inherits parent
    WHERE child.inhparent = parent.inhrelid
  ),
  pg_inherit_short AS (
    SELECT *
    FROM pg_inherit
    WHERE inhparent NOT IN (SELECT inhrelid FROM pg_inherit)
  )
  SELECT
    table_schema AS schema
    , table_name AS table
    , row_estimate
    , pg_size_pretty(total_bytes) AS total_size
    , pg_size_pretty(index_bytes) AS index_size
    , pg_size_pretty(toast_bytes) AS toast_size
    , pg_size_pretty(table_bytes) AS table_size
    , CASE
        WHEN total_bytes = 0 THEN NULL
        ELSE ROUND(table_bytes::numeric / total_bytes::numeric, 2)
      END AS table_size_ratio
    , CASE
        WHEN row_estimate = 0 THEN NULL
        ELSE ROUND(total_bytes::numeric / row_estimate::numeric, 2) 
      END AS avg_row_size
    , total_bytes
    , index_bytes
    , toast_bytes
    , table_bytes
  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
        , SUM(c.reltuples) OVER (partition BY parent) AS row_estimate
        , SUM(pg_total_relation_size(c.oid)) OVER (partition BY parent) AS total_bytes
        , SUM(pg_indexes_size(c.oid)) OVER (partition BY parent) AS index_bytes
        , SUM(pg_total_relation_size(reltoastrelid)) OVER (partition BY parent) AS toast_bytes
        , parent
       FROM (
         SELECT
           pg_class.oid
           , reltuples
           , relname
           , relnamespace
           , pg_class.reltoastrelid
           , COALESCE(inhparent, pg_class.oid) parent
         FROM pg_class
         LEFT JOIN pg_inherit_short
           ON inhrelid = oid
         WHERE relkind IN ('r', 'p', 'm')
       ) c
       LEFT JOIN pg_namespace n
         ON n.oid = c.relnamespace
       WHERE nspname NOT IN ('information_schema', 'pg_catalog')
    ) a
    WHERE oid = parent
  ) a
  ORDER BY total_bytes DESC;

-- Make sure to have all the statistics updated
-- WARNING: can be expensive and won't need to run everytime
VACUUM ANALYZE;

-- Check out the data:
SELECT * FROM size_report;