Postgresql相关数据库、表占用磁盘大小统计(查看表数据占用空间及表数据+索引占用空间大小) |
您所在的位置:网站首页 › postman查询es索引所占空间 › Postgresql相关数据库、表占用磁盘大小统计(查看表数据占用空间及表数据+索引占用空间大小) |
1. 统计数据库大小
单个数据库的大小
select pg_size_pretty (pg_database_size(‘test_database’); 所有数据库的大小select datname, pg_size_pretty (pg_database_size(datname)) AS size from pg_database; 2. 统计数据表大小 单个表大小select pg_size_pretty(pg_relation_size(‘mytab’)) as size; 查询单个表的总大小,包括该表的索引大小select pg_size_pretty(pg_total_relation_size(‘tab’)) as size; 所有表大小select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables order by pg_relation_size(relid) desc; 分区表大小查询 上述方式不起作用,查到的是0bytes,需要先查出有哪些子表,然后再看每个子表的大小再叠加;– 查询分区表有哪些子表,partition_table_name是主表,还可能会查出来主键,唯一键等;需要再加reltype>0筛选出表名 SELECT * FROM pg_class where relname like ‘partition_table_name_%’ and reltype>0; – 查询每个子表标名,表的大小,及包括索引的总大小,并按总大小倒序排列; SELECT relname,pg_size_pretty(pg_relation_size(relname::text)) as tableSize,pg_size_pretty(pg_total_relation_size(relname::text)) as tableTotalSize,pg_total_relation_size(relname::text) as total, FROM pg_class where relname like ‘partition_table_name_%’ and reltype>0 order by total desc; – 查询分区表总大小 select sum(total),pg_size_pretty(sum(total)) from (SELECT relname,pg_size_pretty(pg_relation_size(relname::text)) as tableSize,pg_size_pretty(pg_total_relation_size(relname::text)) as tableTotalSize,pg_total_relation_size(relname::text) as total, FROM pg_class where relname like ‘partition_table_name_%’ and reltype>0 order by total desc) t 3. 所有表的记录数select relname as TABLE_NAME, reltuples as rowCounts from pg_class where relkind = ‘r’ order by rowCounts desc 4. 查询单个表的数据大小,索引大小,表大小,并按表大小倒序排列 SELECT table_name, pg_size_pretty(table_size) AS table_size, pg_size_pretty(indexes_size) AS indexes_size, pg_size_pretty(total_size) AS total_size FROM ( SELECT table_name, pg_table_size(table_name) AS table_size, pg_indexes_size(table_name) AS indexes_size, pg_total_relation_size(table_name) AS total_size FROM ( SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name FROM information_schema.tables ) AS all_tables ORDER BY total_size DESC ) AS pretty_sizes; 5. 查询数据库及大小 select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database; 参考 https://www.cnblogs.com/liqiu/p/3922288.htmlhttps://www.cnblogs.com/telwanggs/p/13645310.htmlhttps://blog.csdn.net/leo_qi/article/details/84804644-https://blog.csdn.net/DB_su/article/details/77935740https://zhuanlan.zhihu.com/p/592895865https://www.alibabacloud.com/help/zh/apsaradb-for-rds/latest/solutions-to-sharp-increase-in-disk-space-usage-in-postgresql-or-ppas-databases |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |