Back

postgres - postgres 查询每个schema, table占用空间的大小 与 truncate 一起配合使用 查询内容空间的大小

发布时间: 2022-02-26 08:27:00

参考：https://wiki.postgresql.org/wiki/Disk_Usage

好吧， 1. 先要看schema的内容，然后再看对应的表 和索引的大小

查看schema的内容。 

refer to : https://stackoverflow.com/questions/4418403/list-of-schema-with-sizes-relative-and-absolute-in-a-postgresql-database

SELECT schema_name, 
       sum(table_size),
       (sum(table_size) / database_size) * 100 as percent
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

就可以列出来了：

    schema_name     |     sum     |            percent
--------------------+-------------+--------------------------------
 information_schema |      106496 |     0.000256189892678112574200
 chain1             |   146399232 |         0.35218227477311921600
 sgd1               |  6189580288 |        14.88983539011118838400
 public             |      319488 |     0.000768569678034337722500
 subgraphs          |      606208 |     0.001458311696783102345300
 primary_public     |           0 | 0.0000000000000000000000000000
 pg_catalog         |     8232960 |         0.01980544939550024100
 pg_toast           | 35223928832 |        84.73571352136751799900
 info               |           0 | 0.0000000000000000000000000000
(9 rows)

查看表的使用

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 200

就可以看到各种记录了。

然后

delete也可以， truncate <table_name> cascade 也可以。 后者更快。

Back