技术债清点：DDL 重整与列类型
复盘一个月攒下的债：status 靠裸字符串、有人问你金额能不能用 float、id 会不会溢出。今天重整 DDL，把类型的坑一次踩明白。
学 · 45 min
01CREATE / ALTER / DROP TABLE；大表 ALTER 加列什么时候会长时间锁表
场景线上 250 万行的 order_items 要加一列，操作不当全站写入卡死。
关键在「要不要重写整张表」：加列带 DEFAULT（PG 11+）只改元数据，秒回；varchar(20) 放宽到 varchar(50) 不重写，很快；收窄长度或跨类型转换（varchar 改 int）要全表重写，期间持锁。动手前先在测试库跑一遍看耗时。
alter table order_items add column note text default ''; -- 秒回 alter table order_items alter column note type varchar(50); -- 不重写，快 alter table order_items alter column note type integer; -- 全表重写，锁到跑完易错「加列带默认值会锁表」是 PG 10 及更早的老黄历；但「改类型」的雷是真的--生产 DDL 前必须测耗时。
02主键选型：bigserial vs GENERATED ALWAYS AS IDENTITY vs uuid
场景新建 payments 表：主键用自增还是 uuid？这不是口味题。
bigserial是老语法（本质是个宏：建序列 + 默认值）；identity是 SQL 标准（PG 10+），语义更严--always版阻止手动插 id，细节不受干扰；uuid全局唯一，多库合并、分布式生成不冲突。练习库 orders 用的 uuid（导出文件就是无序编号），payments 可以用 identity。create table payments ( id bigint generated always as identity primary key, ... );易错serial 不是一种「类型」--面试官爱问这个。新项目默认 identity，需要跨系统唯一才上 uuid。
03uuid 主键的两个代价：索引膨胀与随机写放大
场景后端说 uuid 好看又安全，全表都上--先看账单。
① 索引膨胀：uuid 是 16 字节，bigint 是 8 字节，索引天然大一倍；② 随机写放大：uuid 无序，新行落在 B+ 树随机位置，频繁页分裂、缓存局部性差。写入量大的表，uuid 索引的插入明显慢。缓解：uuidv7（时间前缀、单调递增）。
易错面试答「uuid 有什么问题」只说「占空间」不够--写放大和页分裂才是核心。
04金额为什么必须 numeric；timestamptz 存的到底是什么；text vs varchar(n)
场景三个最高频的类型问题一次结清。
① 金额：
numeric是精确十进制，float 是二进制近似（0.1 存不准）；②timestamptz磁盘上存的是 UTC 时刻（自 2000-01-01 的微秒数），时区只是显示层的换算；③ PG 的text无长度上限，varchar(n)有硬上限（超长报错不截断）--真要限制长度，text + check(length(x) <= n) 更灵活。select 0.1::float8 + 0.2::float8, -- 0.30000000000000004 0.1::numeric + 0.2::numeric; -- 0.3 show timezone; -- 只影响显示，不影响存储 select now() at time zone 'Asia/Shanghai';易错「timestamptz 存的是带时区的时间」是常见错答--存的是 UTC 时刻，时区是会话显示参数。
05jsonb / enum / 数组类型各自适用场景；生成列
场景商品的扩展属性老变、状态值固定、标签一对多但懒得建表--三种「不走寻常路」的类型。
jsonb：半结构化数据（扩展属性、接口报文），可 GIN 索引查询；enum：值集固定不变的状态（可读性好），加值要 ALTER 类型；数组：一对多的轻量场景（标签、权限位）。生成列generated always as (表达式) stored：写入时自动算好存盘，读取零成本。create table order_items ( ..., line_total numeric generated always as (qty * unit_price) stored ); insert into order_items(order_id, product_id, qty, unit_price) values (1, 1, 3, 9.90); -- line_total 自动 = 29.70易错enum 加值容易删值难（删要重排）；值集将来会变就用 text + check。jsonb 滥用会把「无 schema 的灵活」变成「无 schema 的泥潭」。
练 · 60 min
- 跑
select 0.1::float8 + 0.2::float8与 numeric 版对比，记下结果参考答案
float 是二进制近似：0.1 在磁盘上就不是 0.1，误差随累加放大，对账永远差几分钱；numeric 是十进制精确存储。钱的类型只有 numeric 一个答案。
select 0.1::float8 + 0.2::float8 as float版, -- 0.30000000000000004 0.1::numeric + 0.2::numeric as numeric版; -- 0.3 - 用 IDENTITY 重建一张表，对比 serial 的差异
参考答案
\d 两张表长得一样（都是序列供默认值）；差异在语义：identity 是 SQL 标准，always 版拦手动插 id，serial 只是「建序列 + 设默认值」的旧语法宏。
create table demo_serial (id bigserial primary key, val text); create table demo_identity (id bigint generated always as identity primary key, val text); insert into demo_serial(val) values ('a'); insert into demo_identity(val) values ('a'); -- 日常用法没区别 insert into demo_serial(id, val) values (100, '手动插'); -- serial 不拦 insert into demo_identity(id, val) values (100, '手动插'); -- ERROR: cannot insert a non-DEFAULT value into column "id" ... -- 除非显式写 overriding system value drop table demo_serial, demo_identity; -- 练习完清理 - 给 order_items 加生成列
line_total = qty * unit_price参考答案
生成列写入时算好存盘、读取零成本；表达式必须是 immutable（乘法没问题）。对比「每次查询现算 sum」：典型的用写成本换读成本。
-- 加生成列：存量行会现场算好，12 万行要几秒（这是会重写表的操作） alter table order_items add column line_total numeric generated always as (qty * unit_price) stored; select order_id, qty, unit_price, line_total from order_items limit 5; -- 顺手对账：明细合计应等于 total_amount，对不上的 ≈100 单正是埋的负金额脏数据 select count(*) as 对不上的订单数 from orders o join (select order_id, sum(line_total) as s from order_items group by order_id) t on t.order_id = o.id where o.total_amount <> t.s; - 在 12 万行的 order_items 上加一个带 DEFAULT 的列，记录耗时；再把一列 varchar(20) 改成 varchar(50)、改成 int，对比两次耗时
参考答案
①② 是元数据操作所以秒回，③ 要重写 12 万行且期间持锁--§F 冲到 250 万行后同样操作就是分钟级锁。生产 DDL 前先在测试库测耗时，测的就是这个差距。
\timing on -- ① 加带 DEFAULT 的列：PG 11+ 只改元数据，毫秒级返回 alter table order_items add column memo varchar(20) default ''; -- ② varchar(20) 放宽到 varchar(50)：不重写表，毫秒级 alter table order_items alter column memo type varchar(50); -- ③ 改成 int：全表重写 + 逐行转换，肉眼可见地慢 update order_items set memo = '0'; -- 空串转不了 int，先换成数字文本 alter table order_items alter column memo type integer using memo::integer; alter table order_items drop column memo; -- 还原 - 用
pg_dump -s导出整库结构存档参考答案
-s 只导结构不含数据。这份存档是今天 DDL 重整的「验收快照」：之后每次改表重新导一份，diff 一眼看出改了什么。
docker exec pg16 pg_dump -U postgres -s shop > shop_schema.sql -- 本机装了客户端的话也可以： pg_dump -h localhost -U postgres -s shop > shop_schema.sql