简介
在postgres里很多时候想要查看数据库、表、字段的大小，做分析。
示例
查看一个pg cluster里所有数据库的大小
SELECT pg_database.datname,pg_size_pretty(pg_database_size(pg_database.oid)) size
FROM pg_catalog.pg_database
WHERE datistemplate=false
ORDER BY pg_database_size(pg_database.oid) DESC;
查看连接的数据里所有用户表的大小
SELECT
relname AS "Table",
pg_size_pretty(pg_total_relation_size(relid)) as "Size",
pg_size_pretty(pg_total_relation_size(relid)-pg_relation_size(relid)) as "External Size"
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
- Size表示整个表的总大小。
- External Size表示与表关联的主键、索引的大小。
查看每个表、索引、toast表的大小
SELECT
relname AS objectname,
relkind AS objecttype,
reltuples AS "#entries",
pg_size_pretty(relpages::bigint*8*1024) AS size
FROM pg_catalog.pg_class
ORDER BY relpages DESC;
objecttype:
- r 表示普通表
- i 表示索引
- t 表示toast（The Oversized-Attribute Storage Technique）行外存储表
- 更多... Table 51.11. pg_class Columns relkind
只查看用户表相关的表、索引、toast表大小
WITH user_schema AS (
SELECT relnamespace FROM pg_catalog.pg_statio_user_tables urtb join pg_catalog.pg_class cls on urtb.relid=cls.oid group by relnamespace
)
SELECT
relname AS objectname,
relkind AS objecttype,
reltuples AS "#entries",
pg_size_pretty(relpages::bigint*8*1024) AS size
FROM pg_catalog.pg_class JOIN user_schema ON pg_class.relnamespace=user_schema.relnamespace
ORDER BY relpages DESC;
查询某表某列所有项的大小
SELECT pg_size_pretty(pg_column_size("Values")::bigint) valcolsize,"Id","Values" FROM mdm.concept ORDER BY pg_column_size("Values") DESC;
查询按外键聚合的字段的总大小
WITH concept_group AS(
SELECT "CodeSystemId",count(*) count,sum(pg_column_size("Values")) sumsize FROM mdm.concept GROUP BY "CodeSystemId" ORDER BY sumsize DESC
) SELECT "CodeSystemId",count "entries", pg_size_pretty(sumsize) FROM concept_group;
json、jsonb类型字段的大小
postgre中text、json、jsonb等类型的字段会在toast表里存储，故通过pg_column_size表查询出来的列数据是经过toast表序列化、压缩之后的大小，这个大小和它们的字符串表示（包括dump文件）会有较大出入，故建议查询大字段的字符串表示时大小，使用octet_length(col)：
SELECT pg_size_pretty(pg_column_size("Content")::bigint) columnSize,pg_size_pretty(octet_length("Content"::text)::bigint) octSize,* FROM mdm."ESB_mock" ORDER BY octet_length("Content"::text) DESC;
