查看Postgres数据库、表、索引、字段的大小

简介

在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;

1. Size表示整个表的总大小。
2. 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;

参考资料

Check size of tables and objects in PostgreSQL database

Disk Usage

how to find the json size stored in a column of postgres