检查数据库和表的空间占用大小
标签
计算机/数据库/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