Skip to content

检查数据库和表的空间占用大小

标签
计算机/数据库/postgres
计算机/数据库/postgresql
运维
运维/数据库管理
开发/语言/SQL
字数
95 字
阅读时间
1 分钟

列出当前库中各表占用大小

sql
SELECT
  table_name,
  pg_size_pretty(pg_total_relation_size(quote_ident(table_name))),
  pg_total_relation_size(quote_ident(table_name))
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY 3 DESC;

案例输出:

csv
table_name,pg_size_pretty,pg_total_relation_size
some_large_logs,802 MB,840671232
some_medium_table,53 MB,55427072
fairly_small_table,24 kB,24576

列出当前实例中各库占用大小

sql
SELECT
	t1.datname AS db_name,  
    pg_size_pretty(pg_database_size(t1.datname)) AS db_size
FROM pg_database t1
ORDER BY pg_database_size(t1.datname) DESC;
db_name,db_size
postgres,915 MB
template1,7804 kB
template0,7724 kB

贡献者

页面历史

撰写