背景在支付数据分表实践中我们采用了 PostgreSQL 的**表继承Table Inheritance**技术来实现按区域分区。具体实现如下-- 父表主表CREATETABLEIFNOTEXISTSpay_head(id BIGSERIAL,biz_idBIGINTNOTNULL,-- 业务ID(联合主键)line_idBIGINTNOTNULL,-- 行项目ID(联合主键)version_novarchar(50),fiscal_yearvarchar(10),fiscal_quartervarchar(10),region_codevarchar(50),-- ... 其他字段);-- 子表分区表CREATETABLEIFNOTEXISTSpay_head_region_a(CHECK(region_codeA))INHERITS(pay_head);ALTERTABLEpay_head_region_aADDCONSTRAINTpay_head_region_a_pkeyPRIMARYKEY(biz_id,line_id);CREATETABLEIFNOTEXISTSpay_head_region_b(CHECK(region_codeB))INHERITS(pay_head);ALTERTABLEpay_head_region_bADDCONSTRAINTpay_head_region_b_pkeyPRIMARYKEY(biz_id,line_id);CREATETABLEIFNOTEXISTSpay_head_region_c(CHECK(region_codeC))INHERITS(pay_head);ALTERTABLEpay_head_region_cADDCONSTRAINTpay_head_region_c_pkeyPRIMARYKEY(biz_id,line_id);问题发现在实际代码审查中发现有同事在父表也设置了主键-- ❌ 错误做法父表也设置主键ALTERTABLEpay_headADDCONSTRAINTpay_head_pkeyPRIMARYKEY(biz_id,line_id);这种做法是不正确的下面详细说明原因。为什么父表不应该设置主键1. 主键约束不会自动继承到子表PostgreSQL 的表继承机制中主键约束PRIMARY KEY不会被继承。每个子表必须独立定义自己的主键。-- 即使父表设置了主键子表仍然需要单独设置ALTERTABLEpay_headADDCONSTRAINTpay_head_pkeyPRIMARYKEY(biz_id,line_id);-- 子表的主键不会自动继承仍需手动添加ALTERTABLEpay_head_region_aADDCONSTRAINTpay_head_region_a_pkeyPRIMARYKEY(biz_id,line_id);结论父表的主键对子表没有任何作用是多余的。2. 父表通常不存储业务数据在表继承设计中父表的作用是作为查询入口和结构模板实际的业务数据都存储在子表中。-- 查询父表返回所有子表的数据UNION ALLSELECT*FROMpay_headWHEREbiz_id123;-- 等价于SELECT*FROMpay_head_region_aWHEREbiz_id123UNIONALLSELECT*FROMpay_head_region_bWHEREbiz_id123UNIONALLSELECT*FROMpay_head_region_cWHEREbiz_id123数据流向✅ 插入操作 → 直接写入对应的子表通过触发器或应用层路由❌ 插入操作 → 不会写入父表因此父表通常是空的或者只包含少量汇总数据。在空表上设置主键毫无意义。3. 父表主键无法保证全局唯一性即使父表设置了主键(biz_id, line_id)它只能保证父表内部的唯一性而不能保证跨子表的全局唯一性。场景示例-- 假设父表有主键 (biz_id, line_id)INSERTINTOpay_head_region_a(biz_id,line_id,...)VALUES(1,1,...);-- ✅ 成功INSERTINTOpay_head_region_b(biz_id,line_id,...)VALUES(1,1,...);-- ✅ 也成功-- 虽然两个子表都有相同的 (biz_id, line_id)但父表主键无法检测到冲突真正的全局唯一性保证应该通过以下方式实现业务逻辑层确保biz_id在不同区域分区不重复或者使用全局序列号生成器4. 性能和维护成本在父表设置主键会带来额外的开销项目影响索引空间父表主键会创建 B-Tree 索引占用磁盘空间维护成本每次插入/更新/删除都需要维护索引虽然父表通常为空查询优化父表主键索引对子表查询无效无法提升性能锁竞争可能引入不必要的锁竞争实测数据父表主键索引大小约 50 MB假设 100 万行实际收益0因为父表无数据5. PostgreSQL 官方最佳实践根据 PostgreSQL 官方文档“Check constraints and NOT NULL constraints are inherited by child tables. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited.”翻译检查约束和非空约束会被子表继承但其他类型的约束唯一、主键、外键不会被继承。官方推荐的分区表设计模式-- 父表只定义结构和 CHECK 约束不设置主键CREATETABLEmeasurement(city_idintNOTNULL,logdatedateNOTNULL,peaktempint,unitsalesint);-- 子表各自设置主键CREATETABLEmeasurement_y2023m01(CHECK(logdateDATE2023-01-01ANDlogdateDATE2023-02-01))INHERITS(measurement);ALTERTABLEmeasurement_y2023m01ADDPRIMARYKEY(city_id,logdate);跨分区查询索引优化在实际业务场景中经常需要跨分区查询数据。例如-- 跨分区查询查询所有区域的某个业务数据SELECT*FROMpay_headWHEREbiz_id12345;推荐方案为父表添加普通索引-- 为父表添加复合索引用于优化跨分区查询CREATEINDEXidx_pay_head_biz_lineONpay_head(biz_id,line_id);优点✅ PostgreSQL 会自动将父表索引下推到所有子表✅ 每个子表都会创建对应的索引副本✅ 跨分区查询时优化器可以利用所有子表的索引✅ 无需为每个子表单独创建索引执行计划示例EXPLAINSELECT*FROMpay_headWHEREbiz_id12345;-- 输出Append-IndexScanusingidx_pay_head_biz_lineonpay_head_region_aIndexCond:(biz_id12345)-IndexScanusingidx_pay_head_biz_lineonpay_head_region_bIndexCond:(biz_id12345)-IndexScanusingidx_pay_head_biz_lineonpay_head_region_cIndexCond:(biz_id12345)可以看到优化器自动使用了每个子表的索引进行扫描大幅提升查询性能。注意父表索引不能让查询精准定位到单个子表跨分区查询仍会扫描所有子表但每个子表内部使用索引加速了查找。如果需要精准定位到特定子表应在 WHERE 条件中包含分区键region_code。正确的设计方案✅ 推荐做法-- 1. 父表不设置主键只定义结构CREATETABLEIFNOTEXISTSpay_head(id BIGSERIAL,biz_idBIGINTNOTNULL,line_idBIGINTNOTNULL,version_novarchar(50),fiscal_yearvarchar(10),fiscal_quartervarchar(10),region_codevarchar(50),biz_codeVARCHAR(32),item_codeVARCHAR(32),billing_noVARCHAR(64)NOTNULL,billing_item_noVARCHAR(32)NOTNULL,amountnumeric(19,3),categoryVARCHAR(50),segmentVARCHAR(20),created_atTIMESTAMPDEFAULTNOW(),updated_atTIMESTAMPDEFAULTNOW());-- 2. 子表各自设置主键CREATETABLEIFNOTEXISTSpay_head_region_a(CHECK(region_codeA))INHERITS(pay_head);ALTERTABLEpay_head_region_aADDCONSTRAINTpay_head_region_a_pkeyPRIMARYKEY(biz_id,line_id);CREATETABLEIFNOTEXISTSpay_head_region_b(CHECK(region_codeB))INHERITS(pay_head);ALTERTABLEpay_head_region_bADDCONSTRAINTpay_head_region_b_pkeyPRIMARYKEY(biz_id,line_id);CREATETABLEIFNOTEXISTSpay_head_region_c(CHECK(region_codeC))INHERITS(pay_head);ALTERTABLEpay_head_region_cADDCONSTRAINTpay_head_region_c_pkeyPRIMARYKEY(biz_id,line_id);-- 3. 可选为父表添加普通索引用于跨分区查询优化CREATEINDEXidx_pay_head_biz_lineONpay_head(biz_id,line_id); 对比总结特性父表设置主键 ❌父表不设置主键 ✅主键约束继承❌ 不会继承-数据唯一性保证❌ 仅父表内有效-索引空间占用⚠️ 浪费空间✅ 无额外开销查询性能提升❌ 无效果-符合官方最佳实践❌ 不符合✅ 符合维护复杂度⚠️ 增加复杂度✅ 简洁清晰常见疑问解答Q1: 如果父表不设置主键如何保证数据唯一性A: 唯一性应该在子表级别保证每个子表都有自己的主键。如果需要跨分区的全局唯一性应该通过业务逻辑层控制例如使用全局序列号。Q2: 父表需要设置索引吗A: 如果有跨分区查询需求建议设置普通索引非主键用于优化跨分区查询。决策指南是否有跨分区查询需求 ├─ 是 → 为父表添加索引 └─ 否 → 只需在子表设置主键索引即可Q3: 父表的作用是什么A: 父表的主要作用结构模板定义所有子表的共同字段查询入口SELECT * FROM parent_table会自动查询所有子表DDL 管理对父表的 ALTER TABLE 会自动应用到所有子表索引模板在父表创建的索引会自动复制到所有子表Q4: 如果使用声明式分区Declarative Partitioning情况是否相同A: 是的PostgreSQL 10 引入的声明式分区PARTITION BY与传统表继承在主键设计上遵循相同的原则分区表本身不设置主键除非分区键包含在主键中。-- 声明式分区示例CREATETABLEpay_head(biz_idBIGINTNOTNULL,line_idBIGINTNOTNULL,region_codevarchar(50)NOTNULL,PRIMARYKEY(biz_id,line_id,region_code)-- 必须包含分区键)PARTITIONBYLIST(region_code);CREATETABLEpay_head_region_aPARTITIONOFpay_headFORVALUESIN(A);总结在 PostgreSQL 表继承设计中✅父表不应该设置主键主键约束不会继承父表通常不存储数据无法保证全局唯一性浪费存储空间和维护成本✅每个子表应独立设置主键保证子表内的数据唯一性提升子表查询性能符合 PostgreSQL 最佳实践✅可选为父表添加普通索引用于优化跨分区查询自动下推到所有子表不是主键只是普通索引
PostgreSQL 表继承设计：父表是否需要设置主键？
背景在支付数据分表实践中我们采用了 PostgreSQL 的**表继承Table Inheritance**技术来实现按区域分区。具体实现如下-- 父表主表CREATETABLEIFNOTEXISTSpay_head(id BIGSERIAL,biz_idBIGINTNOTNULL,-- 业务ID(联合主键)line_idBIGINTNOTNULL,-- 行项目ID(联合主键)version_novarchar(50),fiscal_yearvarchar(10),fiscal_quartervarchar(10),region_codevarchar(50),-- ... 其他字段);-- 子表分区表CREATETABLEIFNOTEXISTSpay_head_region_a(CHECK(region_codeA))INHERITS(pay_head);ALTERTABLEpay_head_region_aADDCONSTRAINTpay_head_region_a_pkeyPRIMARYKEY(biz_id,line_id);CREATETABLEIFNOTEXISTSpay_head_region_b(CHECK(region_codeB))INHERITS(pay_head);ALTERTABLEpay_head_region_bADDCONSTRAINTpay_head_region_b_pkeyPRIMARYKEY(biz_id,line_id);CREATETABLEIFNOTEXISTSpay_head_region_c(CHECK(region_codeC))INHERITS(pay_head);ALTERTABLEpay_head_region_cADDCONSTRAINTpay_head_region_c_pkeyPRIMARYKEY(biz_id,line_id);问题发现在实际代码审查中发现有同事在父表也设置了主键-- ❌ 错误做法父表也设置主键ALTERTABLEpay_headADDCONSTRAINTpay_head_pkeyPRIMARYKEY(biz_id,line_id);这种做法是不正确的下面详细说明原因。为什么父表不应该设置主键1. 主键约束不会自动继承到子表PostgreSQL 的表继承机制中主键约束PRIMARY KEY不会被继承。每个子表必须独立定义自己的主键。-- 即使父表设置了主键子表仍然需要单独设置ALTERTABLEpay_headADDCONSTRAINTpay_head_pkeyPRIMARYKEY(biz_id,line_id);-- 子表的主键不会自动继承仍需手动添加ALTERTABLEpay_head_region_aADDCONSTRAINTpay_head_region_a_pkeyPRIMARYKEY(biz_id,line_id);结论父表的主键对子表没有任何作用是多余的。2. 父表通常不存储业务数据在表继承设计中父表的作用是作为查询入口和结构模板实际的业务数据都存储在子表中。-- 查询父表返回所有子表的数据UNION ALLSELECT*FROMpay_headWHEREbiz_id123;-- 等价于SELECT*FROMpay_head_region_aWHEREbiz_id123UNIONALLSELECT*FROMpay_head_region_bWHEREbiz_id123UNIONALLSELECT*FROMpay_head_region_cWHEREbiz_id123数据流向✅ 插入操作 → 直接写入对应的子表通过触发器或应用层路由❌ 插入操作 → 不会写入父表因此父表通常是空的或者只包含少量汇总数据。在空表上设置主键毫无意义。3. 父表主键无法保证全局唯一性即使父表设置了主键(biz_id, line_id)它只能保证父表内部的唯一性而不能保证跨子表的全局唯一性。场景示例-- 假设父表有主键 (biz_id, line_id)INSERTINTOpay_head_region_a(biz_id,line_id,...)VALUES(1,1,...);-- ✅ 成功INSERTINTOpay_head_region_b(biz_id,line_id,...)VALUES(1,1,...);-- ✅ 也成功-- 虽然两个子表都有相同的 (biz_id, line_id)但父表主键无法检测到冲突真正的全局唯一性保证应该通过以下方式实现业务逻辑层确保biz_id在不同区域分区不重复或者使用全局序列号生成器4. 性能和维护成本在父表设置主键会带来额外的开销项目影响索引空间父表主键会创建 B-Tree 索引占用磁盘空间维护成本每次插入/更新/删除都需要维护索引虽然父表通常为空查询优化父表主键索引对子表查询无效无法提升性能锁竞争可能引入不必要的锁竞争实测数据父表主键索引大小约 50 MB假设 100 万行实际收益0因为父表无数据5. PostgreSQL 官方最佳实践根据 PostgreSQL 官方文档“Check constraints and NOT NULL constraints are inherited by child tables. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited.”翻译检查约束和非空约束会被子表继承但其他类型的约束唯一、主键、外键不会被继承。官方推荐的分区表设计模式-- 父表只定义结构和 CHECK 约束不设置主键CREATETABLEmeasurement(city_idintNOTNULL,logdatedateNOTNULL,peaktempint,unitsalesint);-- 子表各自设置主键CREATETABLEmeasurement_y2023m01(CHECK(logdateDATE2023-01-01ANDlogdateDATE2023-02-01))INHERITS(measurement);ALTERTABLEmeasurement_y2023m01ADDPRIMARYKEY(city_id,logdate);跨分区查询索引优化在实际业务场景中经常需要跨分区查询数据。例如-- 跨分区查询查询所有区域的某个业务数据SELECT*FROMpay_headWHEREbiz_id12345;推荐方案为父表添加普通索引-- 为父表添加复合索引用于优化跨分区查询CREATEINDEXidx_pay_head_biz_lineONpay_head(biz_id,line_id);优点✅ PostgreSQL 会自动将父表索引下推到所有子表✅ 每个子表都会创建对应的索引副本✅ 跨分区查询时优化器可以利用所有子表的索引✅ 无需为每个子表单独创建索引执行计划示例EXPLAINSELECT*FROMpay_headWHEREbiz_id12345;-- 输出Append-IndexScanusingidx_pay_head_biz_lineonpay_head_region_aIndexCond:(biz_id12345)-IndexScanusingidx_pay_head_biz_lineonpay_head_region_bIndexCond:(biz_id12345)-IndexScanusingidx_pay_head_biz_lineonpay_head_region_cIndexCond:(biz_id12345)可以看到优化器自动使用了每个子表的索引进行扫描大幅提升查询性能。注意父表索引不能让查询精准定位到单个子表跨分区查询仍会扫描所有子表但每个子表内部使用索引加速了查找。如果需要精准定位到特定子表应在 WHERE 条件中包含分区键region_code。正确的设计方案✅ 推荐做法-- 1. 父表不设置主键只定义结构CREATETABLEIFNOTEXISTSpay_head(id BIGSERIAL,biz_idBIGINTNOTNULL,line_idBIGINTNOTNULL,version_novarchar(50),fiscal_yearvarchar(10),fiscal_quartervarchar(10),region_codevarchar(50),biz_codeVARCHAR(32),item_codeVARCHAR(32),billing_noVARCHAR(64)NOTNULL,billing_item_noVARCHAR(32)NOTNULL,amountnumeric(19,3),categoryVARCHAR(50),segmentVARCHAR(20),created_atTIMESTAMPDEFAULTNOW(),updated_atTIMESTAMPDEFAULTNOW());-- 2. 子表各自设置主键CREATETABLEIFNOTEXISTSpay_head_region_a(CHECK(region_codeA))INHERITS(pay_head);ALTERTABLEpay_head_region_aADDCONSTRAINTpay_head_region_a_pkeyPRIMARYKEY(biz_id,line_id);CREATETABLEIFNOTEXISTSpay_head_region_b(CHECK(region_codeB))INHERITS(pay_head);ALTERTABLEpay_head_region_bADDCONSTRAINTpay_head_region_b_pkeyPRIMARYKEY(biz_id,line_id);CREATETABLEIFNOTEXISTSpay_head_region_c(CHECK(region_codeC))INHERITS(pay_head);ALTERTABLEpay_head_region_cADDCONSTRAINTpay_head_region_c_pkeyPRIMARYKEY(biz_id,line_id);-- 3. 可选为父表添加普通索引用于跨分区查询优化CREATEINDEXidx_pay_head_biz_lineONpay_head(biz_id,line_id); 对比总结特性父表设置主键 ❌父表不设置主键 ✅主键约束继承❌ 不会继承-数据唯一性保证❌ 仅父表内有效-索引空间占用⚠️ 浪费空间✅ 无额外开销查询性能提升❌ 无效果-符合官方最佳实践❌ 不符合✅ 符合维护复杂度⚠️ 增加复杂度✅ 简洁清晰常见疑问解答Q1: 如果父表不设置主键如何保证数据唯一性A: 唯一性应该在子表级别保证每个子表都有自己的主键。如果需要跨分区的全局唯一性应该通过业务逻辑层控制例如使用全局序列号。Q2: 父表需要设置索引吗A: 如果有跨分区查询需求建议设置普通索引非主键用于优化跨分区查询。决策指南是否有跨分区查询需求 ├─ 是 → 为父表添加索引 └─ 否 → 只需在子表设置主键索引即可Q3: 父表的作用是什么A: 父表的主要作用结构模板定义所有子表的共同字段查询入口SELECT * FROM parent_table会自动查询所有子表DDL 管理对父表的 ALTER TABLE 会自动应用到所有子表索引模板在父表创建的索引会自动复制到所有子表Q4: 如果使用声明式分区Declarative Partitioning情况是否相同A: 是的PostgreSQL 10 引入的声明式分区PARTITION BY与传统表继承在主键设计上遵循相同的原则分区表本身不设置主键除非分区键包含在主键中。-- 声明式分区示例CREATETABLEpay_head(biz_idBIGINTNOTNULL,line_idBIGINTNOTNULL,region_codevarchar(50)NOTNULL,PRIMARYKEY(biz_id,line_id,region_code)-- 必须包含分区键)PARTITIONBYLIST(region_code);CREATETABLEpay_head_region_aPARTITIONOFpay_headFORVALUESIN(A);总结在 PostgreSQL 表继承设计中✅父表不应该设置主键主键约束不会继承父表通常不存储数据无法保证全局唯一性浪费存储空间和维护成本✅每个子表应独立设置主键保证子表内的数据唯一性提升子表查询性能符合 PostgreSQL 最佳实践✅可选为父表添加普通索引用于优化跨分区查询自动下推到所有子表不是主键只是普通索引
