pg_column_size(): 眼见不一定为实

pg_column_size(): 眼见不一定为实摘要本文探讨了 PostgreSQL 的pg_column_size()函数并揭示了一个令人惊讶的行为对于以行外方式存储的 TOASTed 值该函数仅返回 18 字节的指针大小而非实际数据大小这可能导致在估算表存储需求时出现重大误差。原文链接感谢我的同事 Ozair他给我发了一张 JIRA 工单说我需要删除那个大字段有什么后果我的第一个问题是有多大就在这时候发现了这个问题。看起来很简单。确实也很简单。只需使用管理函数pg_column_size()。直到你遇到 toast 属性。这时候就有意思了。一点历史pg_column_size()由 Mark Kirkwood 在 PostgreSQL 8.1 中添加commita9236028。发布说明简单写道添加pg_column_size()(Mark Kirkwood)没什么特别的。就是一个默默工作了二十年的有用管理函数。基本用法函数签名很简单pg_column_size(any)-integer它返回存储给定值所使用的字节数。让我们从显而易见的用例开始。定长类型selectpg_column_size(1::smallint),pg_column_size(1::integer),pg_column_size(1::bigint);pg_column_size|pg_column_size|pg_column_size------------------------------------------------2|4|8不出所料。定长类型总是返回其类型大小。 ### 变长类型 sqlcreatetablet(idinteger,contenttext);insertintotvalues(1,hello);insertintotvalues(2,repeat(x,10));insertintotvalues(3,repeat(x,100));selectid,pg_column_size(content)fromt;id|pg_column_size--------------------1|62|113|101大小反映了实际内容长度加上 varlena 头。到目前为止一切正常。 ### TOAST 开始介入 当值超过 TOAST[^1] 阈值时PostgreSQL 首先尝试行内压缩。如果压缩后能放入就留在主元组中 sqlinsertintotvalues(4,repeat(x,10000));selectid,pg_column_size(content),length(content)fromtwhereid4;id|pg_column_size|length----------------------------4|125|10000用 125 字节存储 10000 个字符。值仍在主元组中已压缩。如果压缩不够PostgreSQL 将值移出行外到单独的 TOAST 表。剩下的就是一个指针总是恰好 18 字节。我尝试用重复的文本数据来演示。PostgreSQL 的压缩效果足够好以至于我无法通过这种方式触发行外存储。想一想这其实是一个特性。文档问题官方文档说显示存储任何单个数据值所使用的字节数。如果直接应用于表列则反映任何已应用的压缩。最后一句话作用很大。“任何已应用的压缩”。好的。但是 toast 属性呢文档只字未提。这很重要。源代码实际说了什么让我们看看src/backend/utils/adt/varlena.c。对于 varlena 类型pg_column_size()委托给toast_datum_size()/* varlena type, possibly toasted */resulttoast_datum_size(value);而src/backend/access/common/detoast.c中toast_datum_size()的注释明确写道返回 varlena 数据的物理存储大小可能已压缩物理存储大小。不是逻辑大小。也不是未压缩大小。对于行外存储的 toast 值主元组中剩下的是一个 TOAST 指针总是恰好 18 字节无论原始值有多大。解读聚合结果这就是陷阱闭合的地方。真正的信号是物理大小和逻辑大小之间的差距selectavg(pg_column_size(content))asphysical_avg,avg(length(content))aslogical_avgfromt;大差距意味着 TOAST 压缩在起作用。小差距意味着值确实很小或者压缩没起什么作用。 还有一点pg_column_size(NULL)返回 NULL。它是一个普通函数不是聚合函数。因此列为 NULL 的行会被静默排除在avg()之外。你的平均值只反映非 NULL 行。如果你的列有很多 NULL这个平均值比看起来的代表性要差。一定要同时检查非 NULL 比率。 ## 大表上的 TABLESAMPLE 当你在大型生产表上运行pg_column_size()进行诊断时不要对整个表运行。扫描数百万行代价很高。使用 TABLESAMPLE sql-- Bernoulli随机行级采样约 1% 的行selectavg(pg_column_size(content))asphysical_avg,avg(length(content))aslogical_avg,count(content)::float/count(*)asnon_null_ratiofromt tablesample bernoulli(1);-- System块级采样更快随机性更差约 0.1% 的块selectavg(pg_column_size(content))asphysical_avg,avg(length(content))aslogical_avg,count(content)::float/count(*)asnon_null_ratiofromt tablesample system(0.1);BERNOULLI(1)给你一个 proper 统计样本每行有 1% 的概率被选中。SYSTEM(0.1)更快因为它在块级别采样但样本不那么均匀。对于粗略的平均值两者都可以用。对于要放在报告里的东西使用BERNOULLI。补丁文档应该把这个说清楚。现在的措辞让任何处理 toast 列的人都摸不着头脑。这是为func.sgml中pg_column_size()条目提出的澄清对于变长varlena类型此函数返回物理存储大小。对于行内存储在主元组中的值这反映了实际数据大小包括任何已应用的 TOAST 压缩。对于行外存储的 toast 值这返回的是 TOAST 指针的大小18 字节而不是原始数据的大小。要获取值的逻辑大小根据类型使用length()或octet_length()。我计划将此作为文档补丁提交。如果你之前踩过这个坑或者对措辞有想法我很乐意反馈。可以在 pgsql-hackers 列表或 Bluesky 上找到我。什么是 TOASTTOASTThe Oversized-Attribute Storage Technique超大属性存储技术是 PostgreSQL 存储大值的机制。当一行超过大小阈值默认 2kB时PostgreSQL 压缩和/或移动大列值到单独的 TOAST 表只在主元组中存储一个指针。详见官方文档。