PostgreSQL database table size statistics

database-stats.sql

WITH table_sizes1 AS (
  SELECT
      table_name,
      table_size AS table_size,
      indexes_size AS indexes_size,
      total_size AS total_size
  FROM (
      SELECT
          table_name,
          pg_table_size(table_name) AS table_size,
          pg_indexes_size(table_name) AS indexes_size,
          pg_total_relation_size(table_name) AS total_size
      FROM (
          SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
          FROM information_schema.tables
      ) AS all_tables
      ORDER BY total_size DESC
  )    AS pretty_sizes
),
table_sizes2 AS (
  SELECT
     relname AS table_name_short,
     CONCAT('"public"."', relname, '"') AS table_name,
     relkind AS objecttype,
     reltuples AS "number_of_rows"
     FROM pg_class
     ORDER BY relpages DESC
)
SELECT 
  table_sizes2.table_name_short,

  pg_size_pretty(table_size) as table_size_pretty,
  pg_size_pretty(indexes_size) as index_size_pretty,
  pg_size_pretty(total_size) as total_size_pretty,
  table_sizes2.number_of_rows,
  pg_size_pretty(CASE 
    WHEN number_of_rows > 0 THEN (total_size / number_of_rows)::bigint
    ELSE total_size
  END) AS row_size_pretty,
  table_size as table_size_bytes,
  indexes_size as index_size_bytes,
  total_size as total_size_bytes,
  CASE 
    WHEN number_of_rows > 0 THEN (total_size / number_of_rows)::bigint
    ELSE total_size
  END AS row_size_bytes
  from table_sizes1
  inner join table_sizes2 ON table_sizes2.table_name=table_sizes1.table_name
  ORDER BY table_name_short ASC;