 查询 PostgreSQL 表大小：从字节数到容量规划)
★ FEATURED ARTICLE 文档教程知识库【免费下载链接】til:memo: Today I Learned项目地址https://gitcode.com/gh_mirrors/ti/til点击查看免费下载PostgreSQL 在系统目录层面对每个表都维护着精确的磁盘占用数据本篇文章讲解如何通过管理函数pg_relation_size()快速查询任意表的字节大小并延伸至pg_size_pretty()的可读格式转换、索引/数据库大小的对照查询以及批量统计全库表的容量规划实战。读完本文你将掌握一套无需安装任何插件、仅靠一条 SQL 就能摸清 PostgreSQL 存储占用情况的完整方案。核心用法一行 SQL 拿到表的字节数在 Get The Size Of A Database 中我们认识了管理函数pg_database_size()它可以返回指定数据库占用的字节数。与之对应PostgreSQL 还提供了pg_relation_size()函数用来获取某张表的磁盘占用大小。以一张reservations表为例直接在psql中执行 select pg_relation_size(reservations); pg_relation_size ------------------ 1531904函数的返回值是bigint类型单位是字节bytes。这里1531904表示reservations表大约占用了 1.5 MB 的磁盘空间1531904 / 1024 ≈ 1496 KB。使用前提使用该函数需要满足两个隐含条件表必须存在于当前连接的数据库中pg_relation_size()解析的是你传入的关系名relation name这个名称必须能映射到当前数据库内的某张表、索引或物化视图表必须在搜索路径search_path上函数按search_path中列出的 schema 顺序查找同名关系。如果表位于非默认 schema例如analytics.reservations则需要显式写成带 schema 限定的名称例如pg_relation_size(analytics.reservations)否则会抛出relation reservations does not exist的错误。注意与部分 PostgreSQL 管理函数不同pg_relation_size()的参数是关系名文本而非 OID。若需要以 OID 查询可以使用pg_relation_size(oid)的重载形式这通常在系统目录查询场景中配合pg_class.oid使用。让输出更可读配合 pg_size_pretty()字节数对人和脚本都不够直观。PostgreSQL 提供了pg_size_pretty()函数它会根据传入的bigint数值自动挑选最合适的人类可读单位bytes、kB、MB、GB进行格式化。参见 Pretty Print Data Sizes select pg_size_pretty(1234::bigint); pg_size_pretty ---------------- 1234 bytes select pg_size_pretty(123456::bigint); pg_size_pretty ---------------- 121 kB select pg_size_pretty(1234567899::bigint); pg_size_pretty ---------------- 1177 MB select pg_size_pretty(12345678999::bigint); pg_size_pretty ---------------- 11 GB把两者组合起来reservations表的占用就一目了然 select pg_size_pretty(pg_relation_size(reservations)); pg_size_pretty ---------------- 1463 kBpg_size_pretty()与pg_database_size()、pg_relation_size()这类字节型管理函数是黄金搭档几乎所有涉及 PostgreSQL 容量查询的技巧都会以这种组合形式出现。区分表本身与表的总占用这里需要澄清一个常见的认知误区pg_relation_size()返回的是主堆main heap即表数据本身的磁盘占用它不包含该表上索引、TOAST 表和 TOAST 索引的空间。PostgreSQL 针对关系大小提供了一组互补的函数可组合出完整的存储视图pg_relation_size(relation)表主堆的字节数即本篇文章的核心函数pg_table_size(relation)主堆 TOAST 表 空闲空间映射FSM与可见性映射VM的大小基本等于这张表自身完整占用的空间pg_indexes_size(relation)表上全部索引占用的总字节数pg_total_relation_size(relation)表 索引 TOAST 的总和即这张表在磁盘上吃掉的全部空间容量规划时应以它为准。例如想快速评估reservations表连同其索引的整体体量select pg_size_pretty(pg_relation_size(reservations)) as table_heap, pg_size_pretty(pg_indexes_size(reservations)) as indexes, pg_size_pretty(pg_total_relation_size(reservations)) as total;对于压着大量数据、又建了一堆索引的表pg_total_relation_size()与pg_relation_size()之间的差距可能相当惊人——索引体积超过表本身是常见现象这在下一节会进一步印证。关联场景一索引的磁盘占用同样可查pg_relation_size()的入参并不局限于表名任何 relation包括索引都可以传入。这意味着统计索引体积时无需另学新函数。参见 Get The Size On Disk Of An Index先用\d table_name替换为实际表名查看表的结构其中会列出该表上所有索引及其名称然后对每个索引名调用函数select pg_size_pretty(pg_relation_size(index_users_on_email)); pg_size_pretty ---------------- 41 MB (1 row)在 Get The Size Of An Index 中还展示了多个索引的对照查询——一张表既有users_pkey主键索引又有users_unique_lower_email_idx唯一索引可以分别测量 select pg_size_pretty(pg_relation_size(users_pkey)); pg_size_pretty ---------------- 240 kB select pg_size_pretty(pg_relation_size(users_unique_lower_email_idx)); pg_size_pretty ---------------- 704 kB索引虽然能带来查询性能提升但绝不是免费的——它们占据真实磁盘空间。对于超大表累积的索引体积可能达到数百 MB 甚至上 GB这是评估扩容与维护窗口时不容忽视的隐性成本。关联场景二数据库级别的对照把视角从表拉高到整个数据库pg_database_size(hr_hotels)可返回某个数据库的字节占用同样可以与pg_size_pretty()组合 select pg_size_pretty(pg_database_size(hr_hotels)); pg_size_pretty ---------------- 12 MB若想一眼看遍服务器上所有数据库的大小Check The Size Of Databases In A Cluster 给出了两种方式psql元命令\l在\l列出所有数据库及 Name、Owner、Encoding、Collate 等字段的基础上额外输出 Size、Tablespace、Description 三列其中 Size 即人类可读的数据库大小纯 SQL 查询系统目录pg_database并结合pg_size_pretty(pg_database_size(...))按大小降序排列select db.datname as db_name, pg_size_pretty(pg_database_size(db.datname)) as db_size from pg_database db order by pg_database_size(db.datname) desc;实战批量统计一张 schema 下所有表的大小单表查询解决了这张表多大的问题而容量规划更常需要这个 schema 下哪张表最大。借助pg_total_relation_size()与系统目录pg_class/pg_namespace一条 SQL 即可得到全部业务表的完整占用排行select n.nspname as schema_name, c.relname as table_name, pg_size_pretty(pg_total_relation_size(c.oid)) as total_size from pg_class c join pg_namespace n on n.oid c.relnamespace where c.relkind r -- 仅普通表 and n.nspname not in (pg_catalog, information_schema) order by pg_total_relation_size(c.oid) desc;pg_class.relkind r过滤出普通表排除视图、序列、物化视图等再排除系统 schema结果按总占用降序排列即可快速定位全库最大的几张表和它们的索引开销。这一查询与pg_relation_size()形成了单表精确值 全局排名的完整工具链。小结pg_relation_size(表名)返回表主堆的字节数前提是表在当前数据库且位于search_path中pg_size_pretty()负责把字节数转换为 bytes/kB/MB/GB 的可读格式二者几乎总是搭配使用pg_relation_size()同样适用于索引名可用来度量索引的磁盘开销组合pg_database_size()与系统目录查询可完成数据库级别乃至整库表排名的容量统计做容量规划时推荐使用pg_total_relation_size()以覆盖表 索引 TOAST 的完整占用。上述函数属于 PostgreSQL 内建的管理函数Database Activity 与 Statistics 类目无需任何扩展或额外配置即可使用是日常巡检、扩容评估与索引治理的可靠起点。赞分享文档教程知识库【免费下载链接】til:memo: Today I Learned项目地址https://gitcode.com/gh_mirrors/ti/til点击查看免费下载相关推荐PostgreSQL大表查询pgx游标使用技巧PostgreSQL大表查询pgx游标使用技巧 引言大表查询的性能困境 当你处理包含数百万行数据的PostgreSQL大表时是否遇到过以下问题 全表扫描数据库后端OneUptime 自托管容量规划指南基于 Helm 的 ClickHouse、PostgreSQL 与 Valkey 集群大小估算OneUptime 自托管容量规划指南基于 Helm 的 ClickHouse、PostgreSQL 与 Valkey 集群大小估算 导读 本文基于 OneU可观测性后端运维前端云原生微服务AI Agent从字节到浮点数NetCDF-Fortran属性类型查询全解析从字节到浮点数NetCDF Fortran属性类型查询全解析 你是否在处理气象数据时因属性类型不匹配导致程序崩溃还在为识别NC_CHAR与NC_STRING创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考
阅读完成 · 觉得有帮助？
