查Postgresql 数据库占用磁盘大小

查看数据库、表占用磁盘大小

关键的数据表： pg_catalog.pg_database 、 information_schema.tables。 注意：每种数据库，系统表是不相同的。只需要针对每种表获取到相关表，并使用对应的函数计算大小即可。

一、数据库

查看数据库大小：

SELECT pg_size_pretty(pg_database_size('db_name'));

 SELECT d.datname AS "实例名",  pg_catalog.pg_get_userbyid(d.datdba) AS "所属者",
		CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS "大小"
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC
    LIMIT 20

---

二、表

SELECT table_catalog AS "数据库", table_schema AS "模式", table_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS "大小" 
FROM information_schema.tables
ORDER BY  table_schema, pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC

---

三、其他

可以通过统计信息来获取表和索引相关信息. pg_stat_user_tables

--- 统计索引和表相关信息
SELECT * FROM pg_stat_user_indexes
SELECT * FROM pg_stat_user_tables;

--- 查看某个索引大小
SELECT pg_size_pretty(pg_relation_size(indexrelname));
SELECT pg_size_pretty(pg_relation_size('idx_ka_name'));