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

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

这将显示模式名、表名、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,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 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
   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

这样会更清楚。

Pg_size_pretty (<numeric_value>) -转换no。字节转换为人类可读的格式。

Pg_database_size (<db_name>) -以字节为单位获取数据库大小。

Pg_total_relation_size (<relation_name>) -以字节为单位获取表的总大小及其索引。

Pg_relation_size (<relation_name>) -以字节为单位获取关系(表/索引)大小。

Pg_indexes_size (<relation_name>) -以字节为单位获取关系的索引大小。

Current_database() -获取当前正在对其执行查询的数据库。

查询:

select current_database() as database,
       pg_size_pretty(total_database_size) as total_database_size,
       schema_name,
       table_name,
       pg_size_pretty(total_table_size) as total_table_size,
       pg_size_pretty(table_size) as table_size,
       pg_size_pretty(index_size) as index_size
       from ( select table_name,
                table_schema as schema_name,
                pg_database_size(current_database()) as total_database_size,
                pg_total_relation_size(table_name) as total_table_size,
                pg_relation_size(table_name) as table_size,
                pg_indexes_size(table_name) as index_size
                from information_schema.tables
                where table_schema=current_schema() and table_name like 'table_%'
                order by total_table_size
            ) as sizes;

结果:

 database  | total_database_size | schema_name | table_name | total_table_size | table_size | index_size
-----------+---------------------+-------------+------------+------------------+------------+------------
 vigneshdb | 1586 MB             | corpdata    | table_aaa  | 16 kB            | 0 bytes    | 8192 bytes
 vigneshdb | 1586 MB             | corpdata    | table_bbb  | 24 kB            | 0 bytes    | 16 kB
 vigneshdb | 1586 MB             | corpdata    | table_ccc  | 640 kB           | 112 kB     | 488 kB
 vigneshdb | 1586 MB             | corpdata    | table_ddd  | 9760 kB          | 3152 kB    | 6568 kB
 vigneshdb | 1586 MB             | corpdata    | table_eee  | 1120 MB          | 311 MB     | 808 MB

人性化的格式以字节、kB、MB、GB和TB表示。

bytes to kB -从10240字节开始

bytes to MB -从10485248 bytes = 10239.5 kB ~ 10mb开始

bytes to GB -从10736893952开始，字节= 10239.5 MB ~ 10bg

bytes to TB -从10994579406848 bytes = 10239.5 GB ~ 10tb开始

所有单位转换从10 + <单位>开始。

参考- Postgres官方文件

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

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

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

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

我喜欢以下说法:

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 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 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

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

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;

您可以获得总关系大小和关系大小，这可能取决于您的表关系。 下面是如何获取数据库中的前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;

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

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

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

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

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