PostgeSQL 存储空间的查询

最新推荐文章于 2024-06-20 21:52:33 发布

对对对我是佩奇

最新推荐文章于 2024-06-20 21:52:33 发布

阅读量663

点赞数

分类专栏： PostgreSQL 文章标签： PostgreSQL SQL 空间大小运维

本文链接：https://blog.csdn.net/weixin_38779390/article/details/82877703

版权

PostgreSQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

运维的同事要去检查数据库表的大小,分享一下:

单表:

SELECT 
	pg_size_pretty ( pg_relation_size ( [表名] ) );

所有表:

SELECT
	table_schema || '.' || TABLE_NAME AS table_full_name,
	pg_size_pretty ( 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

数据与索引占比:

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