跳至内容
04 量体裁衣：数据类型、约束与可靠数据表达
4.5 类型与约束的物理代价

4.5 类型与约束的物理代价

可靠性不是无成本的，但“为了性能去掉约束”也不是成本分析。类型决定每行布局和可用运算，PK/UK/EXCLUDE 带来索引，FK/CHECK/trigger 增加写时检查；这些成本必须测量并与它们阻止的错误一起评估。

4.5.1 行宽、对齐、TOAST 与更新成本

一行不等于各列声明大小简单相加。heap tuple 还有 header、NULL bitmap 与对齐 padding；text、numeric、JSONB、array 等 varlena 值有长度头，足够宽时可能压缩或移到 TOAST table。列顺序、空值分布和具体内容都会改变实际大小。

先用 PostgreSQL 测，而不是凭类型名猜：

SELECT
    pg_column_size(8800::bigint) AS bigint_bytes,
    pg_column_size(88.00::numeric) AS small_numeric_bytes,
    pg_column_size(
      12345678901234567890.1234567890::numeric
    ) AS wide_numeric_bytes;

本章 PG18.4 样例分别得到 8、8、22。它说明“小 numeric 有时与 bigint 同样紧凑”，不说明两者物理/运算成本等价；numeric 是变长、按四位十进制一组存储并带额外开销，值越宽占用越多。选择 integer minor unit 的首要理由仍是单位与范围合同，固定宽度只是可预期的附带收益。

测完整行：

SELECT
    round(avg(pg_column_size(t))) AS avg_row_payload
FROM shop.sales_order AS t;

在当前两行确定性 fixture 上约为 187 bytes；这不是生产容量估算。生产要取有代表性的长文本、NULL 比例和状态，结合：

pg_relation_size(...)
pg_table_size(...)
pg_indexes_size(...)
pg_total_relation_size(...)

区分 heap、TOAST、索引与总占用。pg_column_size(row) 也不包含页面空闲、dead tuple、FSM/VM 和索引。

TOAST 解决页限制，不消除宽值成本

PostgreSQL 常见 page size 为 8 KiB，单个 tuple 不能跨页。TOAST 会对可 TOAST 类型压缩和/或拆成外置 chunk；触发阈值通常约 2 KiB。主 heap 只留 pointer，查询不读取宽列时可少拉取数据。

但：

  • 宽值仍占磁盘、WAL、备份和网络；
  • 读取它需要 detoast/decompress；
  • 更新宽值会产生新版本及新的 TOAST 数据；
  • 一个 table 有 toast relation 不代表当前已经有值被外置。

本章五表都有 text，因此目录显示 reltoastrelid；固定短样例并未因此“免费存储无限文本”。给 provider payload 一个无限 JSONB 列，会把更新竞争、保留期与敏感数据一起带进主行。

更新会创建新行版本

PostgreSQL MVCC 的 UPDATE 通常写一个新 tuple version。若没有修改 indexed column 且同页有空间，可能使用 HOT 降低索引更新；列变宽、索引过多或页面太满会降低机会。stored generated line_total_minor 又增加 8 bytes，并在单价/数量变化时重算。

不要为省几个 padding byte 就随意重排成熟表的列：重写表、应用兼容和迁移锁的代价常远大于收益。新表可以把固定宽、常用非空列放在合理位置，但最终仍用真实数据测量。

4.5.2 隐式转换、操作符与索引可用性

SQL 中的 = 不是一个能比较任意两值的万能函数。PostgreSQL 根据两边类型、可见 operator、implicit cast 和 preferred type 选择具体实现。unknown string literal 常能借另一边类型推断：

WHERE order_id = '1001'

这里 literal 可以解析为 bigint。但 driver parameter 一旦被声明成 text，就不再是 unknown：

PREPARE bad(text) AS
SELECT * FROM shop.sales_order WHERE order_id = $1;
-- operator does not exist: bigint = text

正确做法是让 driver 绑定 bigint，或在确定输入已经验证时显式 cast parameter：

WHERE order_id = $1::bigint

不要为了“兼容所有输入”cast indexed column：

WHERE order_id::text = $1

普通 sales_order_pkey(order_id) 索引保存 bigint operator class；对列包一层 text cast 后，表达式不同，除非另有 matching expression index，否则通常不能用原 PK index 作为相同条件。

三件事必须一致

索引可用性取决于：

  1. query expression；
  2. 解析出的 operator 与类型；
  3. index key expression、collation 与 operator class。

文本大小写查询若写 lower(email)，普通 UNIQUE(email) 不是该表达式的索引。若创建 expression index，查询又必须使用可匹配的表达式与 collation。一个隐式 collation 或 cast 的变化，既可能改变语义，也可能改变计划。

检查 parameter 类型：

SELECT
    name,
    parameter_types,
    statement
FROM pg_catalog.pg_prepared_statements;

检查 cast 策略：

SELECT
    castsource::regtype,
    casttarget::regtype,
    castcontext
FROM pg_catalog.pg_cast
WHERE castsource IN ('text'::regtype, 'bigint'::regtype)
   OR casttarget IN ('text'::regtype, 'bigint'::regtype);

castcontext 区分 implicit、assignment 和 explicit；不是目录里存在 cast 就能自动应用。

由计划验证，不靠规则口诀

在小 fixture 上 planner 选择 seq scan 很正常，不能据此判定索引“失效”。ch07 会用有规模的数据与 EXPLAIN (ANALYZE, BUFFERS)。本章先保留方法：

  • 确认 column/parameter 精确类型；
  • 查看 predicate 中是否对 indexed column 做函数/cast；
  • 查看实际 operator 和 index definition；
  • 在代表性数据量、统计信息与配置下比较计划；
  • 不用长期关闭 enable_seqscan 来“逼出答案”。

金额 API 同理：把 amount_minor 作为整数传输，不能在 SQL 中反复 amount_minor / 100.0 再与 numeric 参数比较并期待原索引语义不变。展示单位转换放投影层，过滤/连接使用存储单位。

4.5.3 约束、索引与写放大的关系

每个 INSERT/UPDATE 不只写 heap：

  • PK/UNIQUE 要维护 B-tree 并检查冲突；
  • EXCLUDE 要维护指定 index 并检查 operator 冲突；
  • FK 要查询 referenced key，parent 更新/删除还要查 child；
  • CHECK 计算表达式；
  • transition trigger 查询私有边表；
  • WAL、replica、backup 和 cache 都会承受更多字节。

v1 的五张业务表合计只有 12 行 fixture，却已经有 13 个 constraint-backed indexes：

SELECT
    tablename,
    indexname,
    pg_size_pretty(
      pg_relation_size(
        format('%I.%I', schemaname, indexname)::regclass
      )
    ) AS size
FROM pg_catalog.pg_indexes
WHERE schemaname = 'shop'
ORDER BY tablename, indexname;

在本章 PG18.4 空间分配下，每个小 index 即使只有数行也显示 16 KiB。这是页面级最低分配的演示，不应线性外推；但它直观说明“多一个 unique”永远不是零成本。

给每个索引一个理由

index 来源本章理由
五表 PK行身份、FK target、点查
customer_ref/email、SKU、order_no已批准业务唯一性
customer + request_key并发幂等命令
provider + provider ref/idempotency外部/命令作用域唯一
order_id + currency复合 FK 保证订单聚合单币种

最后一个是有意冗余 index：order_id 已全局唯一，但 PostgreSQL 要求复合 FK 指向合格 unique key。我们用额外索引换取数据库可声明的跨表币种一致性。若生产写入证明它太贵，可重审多币种建模或约束实现，不能只删索引后假装规则仍在。

FK child columns没有自动 index。本章 fixture 很小，暂不为每条 FK 增加可能重复的索引；ch07 根据查询与 parent delete/update 路径统一设计。漏建和盲建同样是问题。

约束的收益也要计量

一次 23505 可能阻止两个并发请求生成重复订单，一次 FK 可能避免数月后才暴露的孤儿，一次 migration precheck 可能阻止静默舍入历史金额。把它们只归类为“写性能开销”会漏掉修复、对账和事故成本。

优化顺序应当是：

  1. 证明具体写路径受哪个检查/索引限制；
  2. 检查冗余 index、错误列序和不必要更新；
  3. 批量写入遵守事务/锁/WAL预算；
  4. 在不改变不变量时优化表达；
  5. 若必须改变合同，走业务 ADR，而不是 DBA 私删约束。

后续用 pg_stat_user_indexes、pg_stat_all_tables、WAL 与 latency 指标验证长期成本。刚创建的 index “scan count=0”也不能立即判废，它可能只为 rare integrity path 或 FK parent delete 服务。

本节验收

  • 能用 pg_column_size 与 relation size 函数区分值、heap、index、TOAST 和总量；
  • 不把 TOAST 误解为宽字段免费，也不从 toast relation 存在推断已经外置；
  • driver parameter 使用列的真实类型，indexed column 不被无谓 cast；
  • 能从 expression/operator/collation/opclass 四层解释索引匹配；
  • 列出 v1 的 13 个索引及每一个不变量理由；
  • 明确复合币种 unique 的可靠性收益与写放大；
  • 性能优化以证据为入口，不用删约束代替建模。

参考资料

上一节：用约束表达不变量 · 返回本章目录 · 下一节：分区决策门 · 查看全书目录 · 查看索引中心

最后更新于
4.4 用约束表达不变量4.6 分区决策门