pg统计磁盘占用大小_pg查询表空间使用率

目录

一、查看所有表所占磁盘空间大小

二、查看每个表所占用磁盘空间大小

三、查看数据库大小

四、查看表大小

五、查看索引所占磁盘空间大小

六、查看表空间大小

---

一、查看所有表所占磁盘空间大小

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'));