Postgresql相关数据库、表占用磁盘大小统计（查看表数据占用空间及表数据+索引占用空间大小）_postgresql 查看表大小

作者：weixin_40725706 | 2024-03-19 01:41:38

踩

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

5. 查询数据库及大小

select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database;
1

参考

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/weixin_40725706/article/detail/266613