PG数据库表占用空间大小查询

1.查看所有表所占磁盘空间大小

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

2.查看每个表所占用磁盘空间大小

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

3.查看数据库大小

-- \加上字母l,相当于mysql的，mysql> show databases;
playboy=> \l                         
        List of databases  
   Name    |  Owner   | Encoding  
-----------+----------+----------  
 playboy   | postgres | UTF8  
 postgres  | postgres | UTF8  
 template0 | postgres | UTF8  
 template1 | postgres | UTF8  
  
-- 查看playboy数据库的大小
playboy=> select pg_database_size('playboy');      
 pg_database_size  
------------------  
          3637896  
(1 row)  
  
-- 查看所有数据库的大小
playboy=> select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database;      
  datname  |  size  
-----------+---------  
 postgres  | 3621512  
 playboy   | 3637896  
 template1 | 3563524  
 template0 | 3563524  
(4 rows)  
  
-- 以KB，MB，GB的方式来查看数据库大小
playboy=> select pg_size_pretty(pg_database_size('playboy'));        
 pg_size_pretty  
----------------  
 3553 kB  
(1 row)  

 

4.查看表大小

-- 相当于mysql的，mysql> desc test; 
playboy=> \d test;                  
            Table "public.test"  
 Column |         Type          | Modifiers  
--------+-----------------------+-----------  
 id     | integer               | not null  
 name   | character varying(32) |  
Indexes: "playboy_id_pk" PRIMARY KEY, btree (id)  
  
-- 查看表大小
playboy=> select pg_relation_size('test');     
 pg_relation_size  
------------------  
                0  
(1 row)  
  
-- 以KB，MB，GB的方式来查看表大小  
playboy=> select pg_size_pretty(pg_relation_size('test'));   
 pg_size_pretty  
----------------  
 0 bytes  
(1 row)  
  
-- 查看表的总大小，包括索引大小
playboy=> select pg_size_pretty(pg_total_relation_size('test'));     
 pg_size_pretty  
----------------  
 8192 bytes  
(1 row)  

 

5.查看所有所占磁盘空间大小

-- 相当于mysql的，mysql> show index from test; 
playboy=> \di                       
                List of relations  
 Schema |     Name      | Type  |  Owner  | Table  
--------+---------------+-------+---------+-------  
 public | playboy_id_pk | index | playboy | test  
(1 row)  
  
-- 查看索引大小
playboy=> select pg_size_pretty(pg_relation_size('playboy_id_pk'));      
 pg_size_pretty  
----------------  
 8192 bytes  
(1 row)  

 

6.查看表空间大小

-- 查看所有表空间  
playboy=> select spcname from pg_tablespace;         
  spcname  
------------  
 pg_default  
 pg_global  
(2 rows)  
  
-- 查看表空间大小
playboy=> select pg_size_pretty(pg_tablespace_size('pg_default'));     
 pg_size_pretty  
----------------  
 14 MB  
(1 row)