PostgreSQL 实战进阶（2）：表设计与数据类型

上一篇建立了可重复实例、角色边界和 MVCC 心智模型。本篇继续构建订单库先从业务不变量反推表、类型和约束再用可执行的失败用例证明数据库确实守住了边界而不是把正确性寄托在某个应用版本上。一、先写不变量再画实体关系表设计不是把接口 JSON 原样摊平。订单需要保留购买时的商品名称和成交单价不能每次查询都回到当前商品表订单行属于订单其生命周期可以用外键级联约束支付状态变化频繁使用带CHECK的文本比修改数据库枚举更灵活。字段是否可空要表达业务语义未知、尚未发生与空字符串不是一回事。主键选择也有取舍。连续bigint identity索引紧凑、写入局部性好适合内部关联随机 UUID 可以在多节点先生成却扩大索引并带来随机写。对外暴露时可另设 UUID内部仍使用 bigint。金额不要用浮点数二进制浮点无法精确表达许多十进制小数账务字段应使用最小货币单位整数或受精度约束的numeric。以下脚本独立创建演示 schema。它把自然唯一性、金额范围、数量范围、状态集合和时间关系都落实为约束并为订单行保存商品快照。\setON_ERROR_STOPonDROPSCHEMAIFEXISTSdesign_labCASCADE;CREATESCHEMAdesign_lab;SETsearch_pathdesign_lab,pg_catalog;CREATETABLEcustomer(customer_idbigintGENERATED ALWAYSASIDENTITYPRIMARYKEY,emailtextNOTNULL,display_nametextNOTNULLCHECK(length(btrim(display_name))BETWEEN1AND80),created_at timestamptzNOTNULLDEFAULTnow(),CONSTRAINTcustomer_email_uniqueUNIQUE(email),CONSTRAINTcustomer_email_lowercaseCHECK(emaillower(email)));CREATETABLEproduct(product_idbigintGENERATED ALWAYSASIDENTITYPRIMARYKEY,skutextNOTNULLUNIQUECHECK(sku~^[A-Z0-9-]{3,32}$),nametextNOTNULLCHECK(length(name)BETWEEN1AND200),price_centbigintNOTNULLCHECK(price_cent0),activebooleanNOTNULLDEFAULTtrue);CREATETABLEsales_order(order_idbigintGENERATED ALWAYSASIDENTITYPRIMARYKEY,customer_idbigintNOTNULLREFERENCEScustomer(customer_id),statustextNOTNULLDEFAULTpendingCHECK(statusIN(pending,paid,shipped,cancelled)),placed_at timestamptzNOTNULLDEFAULTnow(),paid_at timestamptz,CHECK(paid_atISNULLORpaid_atplaced_at));CREATETABLEorder_item(order_idbigintNOTNULLREFERENCESsales_order(order_id)ONDELETECASCADE,line_nosmallintNOTNULLCHECK(line_no0),product_idbigintNOTNULLREFERENCESproduct(product_id),product_nametextNOTNULL,unit_price_centbigintNOTNULLCHECK(unit_price_cent0),quantityintegerNOTNULLCHECK(quantityBETWEEN1AND10000),PRIMARYKEY(order_id,line_no));运行输出DROP SCHEMA CREATE SCHEMA SET CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLEtimestamptz存的是绝对时刻显示时按会话时区转换适合下单与支付时间门店每天 09:00 营业这种“墙上时间”才适合time生日适合date。不要为了“统一”把时间存成字符串或 epoch那会丢失类型检查、运算符和清晰语义。二、约束是并发下最后一道防线应用先查邮箱是否存在、再插入用户在并发下会有两个请求同时通过检查。唯一约束由索引在数据库内原子裁决才是真正防重。外键保证被引用行存在但 PostgreSQL 不会自动为引用端创建索引删除客户时需要检查订单缺少sales_order(customer_id)索引可能造成慢扫描甚至扩大锁等待。约束名称应稳定且可读便于应用识别违反的是哪条规则。下面事务同时展示正常写入、快照字段与可预期失败。使用 savepoint 捕获错误后继续事务适合在迁移验收中建立反例测试。\setON_ERROR_STOPonSETsearch_pathdesign_lab,pg_catalog;BEGIN;INSERTINTOcustomer(email,display_name)VALUES(buyerexample.com,示例客户);INSERTINTOproduct(sku,name,price_cent)VALUES(PG-BOOK-01,PostgreSQL 实战手册,9900);INSERTINTOsales_order(customer_id)SELECTcustomer_idFROMcustomerWHEREemailbuyerexample.com;INSERTINTOorder_item(order_id,line_no,product_id,product_name,unit_price_cent,quantity)SELECTo.order_id,1,p.product_id,p.name,p.price_cent,2FROMsales_orderASoCROSSJOINproductASpWHEREp.skuPG-BOOK-01;SAVEPOINTinvalid_quantity;\setON_ERROR_STOPoffINSERTINTOorder_item(order_id,line_no,product_id,product_name,unit_price_cent,quantity)SELECTorder_id,2,product_id,name,price_cent,0FROMsales_orderCROSSJOINproductLIMIT1;\setON_ERROR_STOPonROLLBACKTOSAVEPOINTinvalid_quantity;COMMIT;SELECTo.order_id,sum(i.unit_price_cent*i.quantity)AStotal_cent,count(*)ASlinesFROMsales_orderASoJOINorder_itemASiUSING(order_id)GROUPBYo.order_id;运行输出ERROR: new row for relation order_item violates check constraint order_item_quantity_check DETAIL: Failing row contains a quantity value of 0. order_id | total_cent | lines ----------------------------- 1 | 19800 | 1 (1 row)三、规范化、派生值与演进成本第三范式的实用目标是让一个事实只有一个权威写入点。客户邮箱属于客户商品当前价格属于商品成交价格属于订单行。为了查询性能而反规范化并非禁忌但必须明确同步机制、容忍延迟并能重建。订单总额可在查询时求和若读频率极高可持久化总额却要通过同一事务或触发器保证订单行与总额一致。数组适合“整体读写、不参与复杂关联”的小集合例如标签若要对元素设置外键、属性或频繁单独更新就应拆表。jsonb适合结构变化快的附加属性不适合隐藏所有核心字段。域domain可以复用格式约束但修改域会影响所有使用列生成列适合由同一行不可变表达式得到的值不能直接聚合子表。修改大表时要考虑锁与重写。新增带常量默认值的列在现代 PostgreSQL 通常无需重写全表但类型转换、某些默认表达式和SET NOT NULL验证仍可能昂贵。稳妥流程是先加可空列、分批回填、添加NOT VALID检查约束、在线验证再设置非空并删除辅助约束。每一步都要设置lock_timeout避免迁移在流量高峰无限等待并阻塞后续请求。最后补上外键引用端索引并检查表结构CREATEINDEXsales_order_customer_id_idxONdesign_lab.sales_order(customer_id);CREATEINDEXorder_item_product_id_idxONdesign_lab.order_item(product_id);SELECTconrelid::regclassAStable_name,conname,contypeFROMpg_constraintWHEREconnamespacedesign_lab::regnamespaceORDERBYtable_name::text,conname;运行输出CREATE INDEX CREATE INDEX 查询列出 design_lab 中的主键、唯一、检查和外键约束contype 分别使用 p、u、c、f 标识。好的模型让非法状态难以表达也让后续查询有明确语义。下一篇会在这些真实访问路径上研究 B-tree、复合索引、部分索引与覆盖索引并用EXPLAIN (ANALYZE, BUFFERS)判断索引究竟帮了忙还是增加了写放大。参考来源PostgreSQL数据类型PostgreSQL约束PostgreSQL日期与时间类型PostgreSQL修改表定义 觉得有用就点个赞 收藏方便回头查阅有疑问直接在评论区留言我看到都会回。 本文属于《PostgreSQL 实战进阶》系列持续更新关注不迷路。 文章里的代码都能直接跑。想要可直接 clone 的完整工程 配套部署脚本 / 踩坑清单评论一声或发邮件到cj2664qq.com我免费发你。如果你正好在做类似系统、或有工程化难题想找人做也欢迎邮件聊一句——我按实际情况评估能落地的就接单或出方案。评论和邮件都能直接找到我不用跳别的平台。