如果你正在寻找一个总的分解，吐司和索引大小使用这个:

SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
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
          , c.reltuples AS row_estimate
          , pg_total_relation_size(c.oid) AS total_bytes
          , pg_indexes_size(c.oid) AS index_bytes
          , pg_total_relation_size(reltoastrelid) AS toast_bytes
      FROM pg_class c
      LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
      WHERE relkind = 'r'
  ) a
) a ORDER BY total_bytes DESC;

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

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

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

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

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

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就足够了。

如果你正在寻找一个总的分解，吐司和索引大小使用这个:

SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
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
          , c.reltuples AS row_estimate
          , pg_total_relation_size(c.oid) AS total_bytes
          , pg_indexes_size(c.oid) AS index_bytes
          , pg_total_relation_size(reltoastrelid) AS toast_bytes
      FROM pg_class c
      LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
      WHERE relkind = 'r'
  ) a
) a ORDER BY total_bytes DESC;

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

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

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

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