目录
一、查看所有表所占磁盘空间大小
select
sum(t.size)
from (
SELECT
table_schema || '.' || table_name AS table_full_name
, pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')AS size
FROM information_schema.tables
ORDER by pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC
) t
;二、查看每个表所占用磁盘空间大小
SELECT
table_schema || '.' || table_name AS table_full_name
, pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') AS size_bytes
, pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')/1024/1024 as size_mb
, pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')/1024/1024/1024 as size_gb
FROM information_schema.tables
ORDER by pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC
;三、查看数据库大小
--查看xx数据库大小
select pg_database_size('xx');--查看所有数据库大小
select
pg_database.datname
,pg_database_size(pg_database.datname) AS size
from pg_database;四、查看表大小
--查看表大小
select pg_relation_size('table_name'); --bytes
--以KB\MB\GB方式查看表大小
select pg_size_pretty(pg_relation_size('table_name')); 
--查看表的总大小，包括索引大小
select pg_size_pretty(pg_total_relation_size('table_name')); 
五、查看索引所占磁盘空间大小
select pg_size_pretty(pg_relation_size('table_pkey')); 
六、查看表空间大小
--查看表空间
select spcname from pg_tablespace; 
--查看表空间大小
select pg_size_pretty(pg_tablespace_size('pg_default')); 