SELECT
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "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;

从这里截取https://wiki-bsse.ethz.ch/display/ITDOC/Check+size+of+tables+and+objects+in+PostgreSQL+database

我喜欢以下说法:

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

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

 select uv.a tablename, pg_size_pretty(uv.b) sizepretty 
 from (select tb.tablename a, pg_table_size('schemaname.'||tb.tablename::text) b 
        from pg_tables tb 
        where tb.schemaname ilike 'schemaname' 
        order by 2 desc
       ) uv

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

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

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

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

这可以很容易地从psql控制台完成。

表大小:\dt+ 数据库大小\l+ 索引大小:\di+

对于表大小，首先使用\c db_name连接数据库。

请注意:上面不会按OP要求的大小排序，但它仍然可以提供帮助。

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  ;

信贷: https://makandracards.com/makandra/52141-postgresql-how-to-show-table-sizes