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

另一种替代方法

这将显示模式名、表名、size pretty和size(排序所需)。

SELECT
  schema_name,
  relname,
  pg_size_pretty(table_size) AS size,
  table_size

FROM (
       SELECT
         pg_catalog.pg_namespace.nspname           AS schema_name,
         relname,
         pg_relation_size(pg_catalog.pg_class.oid) AS table_size

       FROM pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
     ) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;

我基于PostgreSQL数据库中具有大小(相对大小和绝对大小)的模式列表的解决方案来构建它

select table_name, pg_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'public'
order by 2

如果你有多个模式，这将显示模式公共中所有表的大小，你可能想使用:

select table_schema, table_name, pg_relation_size('"'||table_schema||'"."'||table_name||'"')
from information_schema.tables
order by 3

SQLFiddle示例:http://sqlfiddle.com/#!15/13157/3

手册中所有对象大小函数的列表。

我需要找出哪些表占用了最多的空间。

根据其他人的回答，我使用了这个问题:

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

得到如下结果:

table_name              pg_size_pretty
--------------------------------------
trade_binance           96 GB
closs_v2_binance_stash  46 GB
closs_bitfinex_stash    5725 MB
trade_bitfinex          5112 MB
...
api_requests            0 bytes
trade_huobi             0 bytes

我应该买个更大的固态硬盘。

这里的大多数答案使用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

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