PostgreSQL 数据库中具有大小（相对和绝对）的模式列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4418403/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
list of schema with sizes (relative and absolute) in a PostgreSQL database
提问by Faheem Mitha
I'm looking for a query that returns a result of the form for any database (see example below supposing total space used by the database is 40GB)
我正在寻找一个查询，它返回任何数据库的表单结果（请参见下面的示例，假设数据库使用的总空间为 40GB）
schema | size | relative size
----------+-------------------
foo | 15GB | 37.5%
bar | 20GB | 50%
baz | 5GB | 12.5%
I've managed to concoct a list of space using entities in the database sorted by schema, which has been useful, but getting a summary per schema from this doesn't look so easy. See below.
我已经设法使用按模式排序的数据库中的实体编造了一个空间列表，这很有用，但从中获取每个模式的摘要看起来并不那么容易。见下文。
SELECT relkind,
relname,
pg_catalog.pg_namespace.nspname,
pg_size_pretty(pg_relation_size(pg_catalog.pg_class.oid))
FROM pg_catalog.pg_class
INNER JOIN pg_catalog.pg_namespace
ON relnamespace = pg_catalog.pg_namespace.oid
ORDER BY pg_catalog.pg_namespace.nspname,
pg_relation_size(pg_catalog.pg_class.oid) DESC;
This gives results like
这给出了类似的结果
relkind | relname | nspname | pg_size_pretty
---------+---------------------------------------+--------------------+----------------
r | geno | btsnp | 11 GB
i | geno_pkey | btsnp | 5838 MB
r | anno | btsnp | 63 MB
i | anno_fid_key | btsnp | 28 MB
i | ix_btsnp_anno_rsid | btsnp | 28 MB
[...]
r | anno | btsnp_shard | 63 MB
r | geno4681 | btsnp_shard | 38 MB
r | geno4595 | btsnp_shard | 38 MB
r | geno4771 | btsnp_shard | 38 MB
r | geno4775 | btsnp_shard | 38 MB
It looks like using an aggregation operator like SUM may be necessary, no success with that thus far.
看起来可能需要使用像 SUM 这样的聚合运算符，但到目前为止还没有成功。
Regards, Faheem
回答by a_horse_with_no_name
Try this:
尝试这个：
SELECT schema_name,
sum(table_size),
(sum(table_size) / database_size) * 100
FROM (
SELECT pg_catalog.pg_namespace.nspname as schema_name,
pg_relation_size(pg_catalog.pg_class.oid) as table_size,
sum(pg_relation_size(pg_catalog.pg_class.oid)) over () as database_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name, database_size
Edit: just noticed the workaround with summing up all tables to get the database size is not necessary:
编辑：刚刚注意到不需要汇总所有表以获得数据库大小的解决方法：
SELECT schema_name,
pg_size_pretty(sum(table_size)::bigint),
(sum(table_size) / pg_database_size(current_database())) * 100
FROM (
SELECT pg_catalog.pg_namespace.nspname as schema_name,
pg_relation_size(pg_catalog.pg_class.oid) as table_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY schema_name
