select table_name,n_live_tup, pg_size_pretty(pg_relation_size(table_name))
from information_schema.tables
inner join pg_stat_user_tables  on table_name=relname
where table_schema = 'public'
order by 2 desc

另一种替代方法

这里的大多数答案使用pg_size_pretty，这是非常有用的，但如果你想输出一个数值，你可以自己计算

SELECT tab_size /1024 AS size_kb
      ,tab_size /1024 /1024 AS size_mb
      ,tab_size /1024 /1024 / 1024 AS size_gb
      ,tab_size /1024 /1024 / 1024 / 1024 AS size_tb
  FROM 
      (
       SELECT pg_total_relation_size(relid) AS tab_size
         FROM pg_catalog.pg_statio_user_tables
        WHERE schemaname = 'your_schema' 
          AND relname = 'your_table'
      ) AS tabs;

我喜欢以下说法:

SELECT 
  table_name, 
  pg_size_pretty( pg_total_relation_size(quote_ident(table_name))), 
  pg_total_relation_size(quote_ident(table_name))
FROM 
  information_schema.tables
WHERE 
  table_schema = 'public'
ORDER BY 
  pg_total_relation_size(quote_ident(table_name)) DESC

你可以看到总大小在一个漂亮的格式，但它的顺序也是正确的。

您可以获得总关系大小和关系大小，这可能取决于您的表关系。 下面是如何获取数据库中的前100个表:

SELECT schemaname                                    AS table_schema,
       relname                                       AS table_name,
       PG_SIZE_PRETTY(PG_TOTAL_RELATION_SIZE(relid)) AS total_size,
       PG_SIZE_PRETTY(PG_RELATION_SIZE(relid))       AS data_size,
       PG_SIZE_PRETTY(PG_TOTAL_RELATION_SIZE(relid) - PG_RELATION_SIZE(relid))
                                                     AS external_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY PG_TOTAL_RELATION_SIZE(relid) DESC,
         PG_RELATION_SIZE(relid) DESC
LIMIT 100;

select table_name,n_live_tup, pg_size_pretty(pg_relation_size(table_name))
from information_schema.tables
inner join pg_stat_user_tables  on table_name=relname
where table_schema = 'public'
order by 2 desc

另一种替代方法

select table_name, pg_size_pretty(pg_total_relation_size(quote_ident(table_name)))
from information_schema.tables
where table_schema = 'public'
order by pg_total_relation_size(quote_ident(table_name));

Pg_total_relation_size将包括索引和表的大小。 如果只需要表大小，那么pg_relation_size就足够了。