查Postgresql 数据库占用磁盘大小

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

关键的数据表: pg_catalog.pg_databaseinformation_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'));

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 撸撸猫 设计师:C马雯娟 返回首页