文档教程知识库【免费下载链接】til:memo: Today I Learned项目地址https://gitcode.com/gh_mirrors/ti/til点击查看免费下载索引加到合适的列上可以为特定查询带来巨大的性能提升但索引并非免费它需要占用磁盘空间。单个索引的体积通常可以忽略不计但在处理大表、或者表上堆叠了多个索引主键约束、唯一约束、表达式索引、覆盖索引等时索引的总磁盘占用可能非常可观。本文基于本仓库 postgres/get-the-size-on-disk-of-an-index.md 的核心思路结合仓库内其他 PostgreSQL 笔记系统讲解如何用 PostgreSQL 内置的管理函数精确测量任意索引的磁盘占用并扩展到表、数据库级别的容量排查场景。一、为什么需要关心索引的磁盘占用索引的本质是额外的数据结构。每次执行insert、update、delete时PostgreSQL 除了维护表本身还必须同步维护表上所有相关的索引这意味着额外的磁盘空间索引内容以 B-Tree 等形式持久化存储写入放大每一条数据变更都要附带更新对应索引缓存竞争索引页与数据页共享缓冲池shared buffers索引越大留给热数据页的空间越少。所以虽然加了索引就快的说法没错但对超大表或索引数量很多的表评估每个索引的磁盘占用是数据库容量规划与性能优化中不可跳过的一步。本仓库的另一篇笔记 postgres/get-the-size-of-an-index.md 也强调想了解某个附加索引到底占了多少磁盘空间直接查询即可。二、第一步查出索引的名称要查询索引大小首先需要拿到索引的名字。PostgreSQL 的psql客户端提供了快捷的元命令\d\d table_name把table_name替换为索引所在的表名输出会展示该表的全部信息包括列、约束以及所有索引及其名称。索引的默认命名规则通常是表名_列名_idx普通索引或表名_列名_key约束自动创建的索引例如index_users_on_email、users_pkey、users_unique_lower_email_idx。如果你只想看到索引清单而不想看整张表的完整定义也可以查询系统目录视图pg_indexesselect indexname, indexdef from pg_indexes where tablename users;三、核心查询pg_relation_size pg_size_pretty拿到索引名之后用 PostgreSQL 的管理函数pg_relation_size()读取其磁盘占用并结合pg_size_pretty()把字节数格式化为易读的单位select pg_size_pretty(pg_relation_size(index_users_on_email));执行结果类似pg_size_pretty ---------------- 41 MB (1 row)这个例子中的索引算很小的41 MB。索引体积可以变得非常大——实践中见过单个索引超过 1 GB 的情况。当你面对的是一个海量表时尤其值得逐一确认每个索引的体积。关于函数行为的两个关键点pg_relation_size()返回的是字节数bigint。本仓库笔记 postgres/get-the-size-of-a-table.md 中展示了不套pg_size_pretty()时的裸输出例如1531904字节。pg_relation_size对表和索引一视同仁传入索引名即可得到该索引主堆的大小。pg_size_pretty()负责把字节数换算成最可读的单位。在 postgres/pretty-print-data-sizes.md 中有多组示例1234字节显示为1234 bytes123456显示为121 kB1234567899显示为1177 MB12345678999显示为11 GB。它既可以单独使用也特别适合与pg_relation_size()、pg_database_size()这类返回bigint的函数组合。一次性查看表上的所有索引大小如果一张表上挂着多个索引逐个查询太繁琐。可以基于pg_indexes与pg_relation_size写一条聚合查询把表上所有索引的名称与大小一次列出来select indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) as index_size from pg_indexes where tablename users order by pg_relation_size(indexname::regclass) desc;注意这里把indexname从text转成regclass用::regclass显式转换是让 PostgreSQL 按当前数据库的搜索路径解析索引名。前提与 postgres/get-the-size-of-a-table.md 中说明的一致——被引用的对象必须属于当前连接的数据库并且位于搜索路径search path上。四、从单个索引扩展到表与数据库的容量视角pg_relation_size只是 PostgreSQL 管理函数家族的一员。如果你想评估这张表整体含所有索引到底占多少仓库里还提供了几个互补的方法表本身不含索引的大小直接pg_relation_size(users)表 其索引的总大小pg_total_relation_size(users)包含 TOAST 等附属对象某张表所有索引的合计大小pg_indexes_size(users)整个数据库的大小pg_database_size(db_name)配合pg_size_pretty使用。仓库笔记 postgres/check-the-size-of-databases-in-a-cluster.md 还展示了如何在集群层面按数据库大小排序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;除此之外psql的\l元命令也会在数据库列表的Size列中直接给出人类可读的大小。把这些方法串起来你就可以自下而上地回答三个问题某个索引多大 → 某张表含索引多大 → 某个数据库多大从而定位磁盘空间的主要消耗点。五、哪些索引最可能膨胀结合建索引方式预估索引的大小与索引类型、列数、数据分布强相关。本仓库的多篇索引笔记可以帮你预判哪些场景会产生体积可观的索引复合索引在多个列上建立的组合索引体积通常大于单列索引。参考 postgres/create-an-index-across-two-columns.md例如create index events_user_id_created_at_idx on events (user_id, created_at);。覆盖索引INCLUDE 子句额外把非索引列塞进叶子页换来 index-only scan但也直接增加磁盘占用。参考 postgres/include-columns-in-a-covering-index.md。表达式/函数索引对表达式求值后建索引存储内容多一份计算结果。唯一约束与主键约束会自动创建索引即使你没显式create index它们同样占空间——例如users_pkey、users_unique_lower_email_idx这类名字的索引。如果担心建索引过程中锁表、需要在线加索引可以参考 postgres/create-an-index-without-locking-the-table.md 使用create index concurrently。不过并发建索引更慢、更耗资源这同样会体现在索引生成过程的时间与临时占用上。六、小结需求推荐写法单个索引的磁盘占用select pg_size_pretty(pg_relation_size(index_name));查索引名称\d 表名或查询pg_indexes表上所有索引大小汇总基于pg_indexespg_relation_size聚合表含索引总大小pg_total_relation_size(table_name)整个数据库大小pg_size_pretty(pg_database_size(db_name))核心结论索引的磁盘占用不是玄学而是可以用 PostgreSQL 内置函数直接量化的指标。日常开发中pg_relation_sizepg_size_pretty这两行组合足以回答这个索引占了多少空间当表体量巨大或索引数量众多时再结合pg_total_relation_size、pg_indexes_size与pg_database_size做整体容量评估就能在索引带来的查询加速与磁盘成本之间做出有数据支撑的权衡。想要更系统地掌握 PostgreSQL 容量管理可继续阅读仓库中的相关笔记postgres/get-the-size-of-a-table.md、postgres/pretty-print-data-sizes.md、postgres/check-the-size-of-databases-in-a-cluster.md 以及 postgres/get-the-size-of-an-index.md。赞分享文档教程知识库【免费下载链接】til:memo: Today I Learned项目地址https://gitcode.com/gh_mirrors/ti/til点击查看免费下载相关推荐TILPostgreSQL用 pg_relation_size pg_size_pretty 精确测量索引占用磁盘空间TILPostgreSQL用 pg_relation_size pg_size_pretty 精确测量索引占用磁盘空间 索引能显著加速查询但并非免费文档教程知识库LeetCode 简单题合集实战指南lucifer 的 easy.md 解题路线与高频 Easy 题解精讲LeetCode 简单题合集实战指南lucifer 的 easy.md 解题路线与高频 Easy 题解精讲 本指南以仓库 collections/easy.m文档教程知识库Papermark 实战PostgreSQL 索引审计与优化查询指南未使用索引 / 重复索引排查Papermark 实战PostgreSQL 索引审计与优化查询指南未使用索引 / 重复索引排查 本文围绕 Papermark 仓库自带的 Postgre后端前端企业应用创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考