PostgreSQL数据库中具有大小（相对和绝对）的模式列表 [英] list of schema with sizes (relative and absolute) in a PostgreSQL database

问题描述

我正在寻找一个查询，该查询返回任何数据库
的表单结果（假设数据库使用的总空间为40GB，请参见下面的示例）

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)

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;

结果类似

  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

似乎需要使用像SUM这样的聚合运算符，到目前为止还没有成功。

It looks like using an aggregation operator like SUM may be necessary, no success with that thus far.

                                                                                 Regards, Faheem

推荐答案

尝试一下：

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

编辑：刚刚注意到汇总所有表以获取数据库大小的解决方法是不必要：

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

这篇关于PostgreSQL数据库中具有大小（相对和绝对）的模式列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！