PHP小码农

Appearance

列设计最佳实践 ​

列设计（Column Design）决定了三件事：存储效率、约束强度、查询可行性。同一份业务数据，用不同的类型与约束表达，可能带来数倍的存储差异，以及「能否用上索引」的本质区别。本节从数据类型选择讲到列顺序与对齐、NOT NULL 与 DEFAULT、枚举方案取舍、JSONB 的边界、TOAST 机制，最后给出一张生产级业务表的完整 DDL 与评审清单。

前置知识

阅读本节前，建议先了解：数据类型选择指南

基础概念 ​

列设计可以拆成五个独立决策，每个决策都有明确的判据：

决策关键问题判据
数据类型用什么类型存？语义正确 > 空间效率 > 查询形态
可空性允许 NULL 吗？只有「业务上真的未知」才允许
默认值需要 DEFAULT 吗？能消除应用端填值逻辑就加
约束强度需要 CHECK/UNIQUE 吗？能用约束表达的规则不要留给应用
存储形态会触发 TOAST 吗？单值超约 2KB 就会外置存储

其中数据类型选择是第一位的。原因在于类型一旦选定，它就同时决定了：值的合法范围、比较与排序语义、可用的操作符与函数、可用的索引类型、以及在磁盘上的存储宽度。

几个必须先记住的事实：text 与 varchar(n) 性能几乎无差别（内部都用 varlena，n 只是约束）；numeric 是精确小数而 float8 是近似值（金额用 numeric）；列的物理顺序影响对齐填充；仅当表中存在任一可空列时才建立空值位图（每行约 1 位/列）；单值超约 2KB 会触发 TOAST；生成列只能引用本行、且非 volatile 的表达式。

验证「text 与 varchar 无性能差异」可以直接看系统目录：三者的 pg_type.typlen 都是 -1（变长），typstorage 分别为 x/x/m，内部都用同一套 varlena 布局。可见类型选择只决定语义与校验，不带来存储格式差异。

sql
SELECT typname, typlen, typstorage
FROM pg_type WHERE typname IN ('text','varchar','numeric') ORDER BY typname;
text
 typname | typlen | typstorage
---------+--------+------------
 numeric |     -1 | m
 text    |     -1 | x
 varchar |     -1 | x
(3 rows)

语法与用法 ​

列定义的完整语法要素：

text
column_name data_type
  [ COLLATE collation ]
  [ column_constraint [ ... ] ]
  [ STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT } ]

column_constraint 可以是：
  NOT NULL | NULL
  DEFAULT expression
  GENERATED ALWAYS AS ( expression ) STORED      -- 生成列（PG 12+）
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY  -- 身份列
  UNIQUE [ NULLS [ NOT ] DISTINCT ]
  PRIMARY KEY
  CHECK ( expression ) [ NO INHERIT ]
  REFERENCES table ( column ) [ ON DELETE ... ]

最简可运行示例——一张「处处规范」的表：

sql
CREATE TABLE articles (
  id          bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title       text        NOT NULL CHECK (length(title) BETWEEN 1 AND 200),
  slug        text        NOT NULL UNIQUE,
  body        text        NOT NULL DEFAULT '',
  view_count  integer     NOT NULL DEFAULT 0 CHECK (view_count >= 0),
  is_published boolean    NOT NULL DEFAULT false,
  published_at timestamptz,
  created_at  timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT ck_articles_published_at
    CHECK ((is_published = false) OR (published_at IS NOT NULL))
);
text
CREATE TABLE

注意最后那条 CHECK：它把「已发布必须有发布时间」这条业务规则下沉到数据库，应用层即使有 bug 也无法写入非法数据。

详细说明 ​

选择最合适的数据类型 ​

类型选择的第一原则是语义正确，其次是空间效率。常见的类型误用清单：

误用问题正确做法
金额用 float8/float4二进制浮点有舍入误差，累加后偏差可见numeric(12,2)
所有字符串都用 text 无约束丢失长度语义，无法在库层拦超长数据允许长文本用 text + CHECK
ID 用 varchar(36) 存 UUID36 字节 vs 16 字节，还有解析开销uuid 类型
时间用 varchar 或 bigint丢失时间运算、时区语义timestamptz
布尔用 char(1)/int语义不清，检索别扭boolean
枚举用中文文本无约束拼写错误无处拦截CHECK 或枚举类型
IP 用 text无法做网段包含查询inet/cidr
范围用两个 numeric 列无法用范围索引numrange/int8range

用一段 SQL 把「浮点误差」这件事钉死：

sql
SELECT (0.1::float8 + 0.2::float8 = 0.3::float8)    AS float_eq,
       (0.1::numeric + 0.2::numeric = 0.3::numeric) AS numeric_eq;
text
 float_eq | numeric_eq
----------+------------
 f        | t
(1 row)

0.1 + 0.2 != 0.3 就是不能用浮点存金额的根本原因。

关于 text vs varchar(n)：PostgreSQL 官方明确说明二者性能相同，varchar(n) 只是加了一个长度检查。因此选择依据是你要不要长度上限：

sql
-- 需要长度上限：varchar(n) 或 text + CHECK 都可以
CREATE TABLE t_varchar (code varchar(10));        -- 超出报错：value too long for type character varying(10)
CREATE TABLE t_text_check (code text CHECK (length(code) <= 10));  -- 语义等价，报错信息更可控

-- 不设上限：直接用 text
CREATE TABLE t_text (body text);

列顺序对存储与对齐的影响 ​

PostgreSQL 的堆表（heap）行格式会把各列按自然对齐排列，并在必要时插入填充字节。因此列的物理顺序会影响每行占用的空间。

规则是：每个类型有其对齐要求（pg_type.typalign：c = 1、s = 2、i = 4、d = 8 字节），为了让列正确对齐，前面可能需要填充。若把「8 字节对齐」的列排在两个「4 字节」列之间，就可能产生填充。

类型宽度对齐
boolean1c
smallint2s
integer4i
bigint / timestamptz8d
uuid16c
numeric / text变长i

看一个对比实验，用 pg_column_size 观察单行的物理大小：

sql
-- 方案 A：不当顺序（对齐宽度交错，产生填充）
CREATE TABLE order_col_bad (
  flag_a boolean, big_a bigint, int_a integer,
  flag_b boolean, big_b bigint, int_b integer
);

-- 方案 B：按对齐宽度降序（8 → 4 → 2 → 1）
CREATE TABLE order_col_good (
  big_a bigint, big_b bigint, int_a integer,
  int_b integer, flag_a boolean, flag_b boolean
);

INSERT INTO order_col_bad  DEFAULT VALUES;
INSERT INTO order_col_good DEFAULT VALUES;

SELECT 'bad' AS layout, pg_column_size(t) AS row_size FROM order_col_bad t
UNION ALL
SELECT 'good', pg_column_size(t) FROM order_col_good t;
text
 layout | row_size
--------+----------
 bad    |       72
 good   |       64
(1 row)

实用建议

不必为每张表做精细的列重排，但养两条习惯就够了：① 把变长列（text/jsonb/bytea）放最后；② 把定长列按宽度降序排列（8 → 4 → 2 → 1）。这两条能覆盖绝大多数收益。

NOT NULL 与 DEFAULT 的默认使用 ​

NOT NULL 应该默认加上，只有「业务上确实未知」时才允许为空。理由有三：

理由说明
约束强度把「必填」语义交给数据库，而非仅靠应用校验
查询简化不需要到处写 IS NOT NULL 或 coalesce
优化器友好优化器知道该列无 NULL，能生成更好的计划

NULL 的代价体现在三值逻辑上——它让比较结果变成「未知」，很容易写出反直觉的 SQL：

sql
-- NULL 的三值逻辑：任何与 NULL 的比较都是 NULL（非真非假）
SELECT NULL = NULL        AS null_eq_null,     -- NULL，不是 true
       NULL <> 1          AS null_ne_1,        -- NULL
       NULL IS NULL       AS null_is_null,     -- true
       NULL NOT IN (1, 2) AS null_not_in;      -- NULL
text
 null_eq_null | null_ne_1 | null_is_null | null_not_in
--------------+-----------+--------------+-------------
              |           | t            |
(1 row)

DEFAULT 的价值在于消除应用端的填值逻辑，并且让「不想传这个字段」变成合法操作：

sql
CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status     text        NOT NULL DEFAULT 'pending',
  currency   char(3)     NOT NULL DEFAULT 'CNY',
  qty        integer     NOT NULL DEFAULT 1,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- 插入时省略有默认值的列
INSERT INTO orders DEFAULT VALUES;
INSERT INTO orders (qty) VALUES (3);

SELECT id, status, currency, qty FROM orders ORDER BY id;
text
 id | status  | currency | qty
----+---------+----------+-----
  1 | pending | CNY      |   1
  2 | pending | CNY      |   3
(2 rows)

一个关键细节：NOT NULL 加上 DEFAULT 之后，INSERT 省略该列不会触发非空错误（因为用的是默认值），但显式写入 NULL 仍然会被拒绝：

sql
-- 错误！显式 NULL 违反 NOT NULL
INSERT INTO orders (status) VALUES (NULL);
text
ERROR:  null value in column "status" of relation "orders" violates not-null constraint
DETAIL:  Failing row contains (3, null, CNY, 1, 2026-09-28 ...).

避免过多可空列与稀疏宽表 ​

NULL 在存储上几乎不占空间（只占空值位图 1 位），但它有两个隐性成本：

成本说明
空值位图只要表里有任一可空列，整行就要加约 (列数+7)/8 字节的位图
语义成本每个 NULL 都要在查询与约束里谨慎处理

真正的反模式是稀疏宽表——为了兼容不同业务而堆出几十个大部分为空的列：

sql
-- 不推荐：稀疏宽表，90% 的行大部分列都是 NULL
CREATE TABLE products_sparse (
  id            bigint PRIMARY KEY,
  name          text NOT NULL,
  voltage       integer,  wattage   integer,  plug_type text,   -- 电器类属性
  size          text,     color     text,     material  text,   -- 服装类属性
  isbn          text,     page_count integer, publisher text,   -- 图书类属性
  extra_attr_1  text,     extra_attr_2 text                      -- ……还会继续长
);

pg_stats.null_frac 可以直接量化稀疏程度（null_frac 超过 0.9 的列，几乎可以断定它属于「另一个业务对象」）：

sql
SELECT attname, null_frac, n_distinct
FROM pg_stats WHERE schemaname = 'public' AND tablename = 'products_sparse'
ORDER BY null_frac DESC LIMIT 4;
text
   attname   | null_frac | n_distinct
-------------+-----------+------------
 isbn        |      0.94 |       1200
 page_count  |      0.93 |        300
 publisher   |      0.91 |        200
 wattage     |      0.71 |         30
(4 rows)

正确姿势有两条路：

sql
-- 方案 A：拆出类型专属表（真正的范式化）
CREATE TABLE product_electronics (
  product_id bigint PRIMARY KEY REFERENCES products(id),
  voltage    integer NOT NULL,
  wattage    integer NOT NULL
);

-- 方案 B：用 JSONB 装易变的扩展属性
CREATE TABLE products_flex (
  id bigint PRIMARY KEY, name text NOT NULL, attrs jsonb NOT NULL DEFAULT '{}'::jsonb
);

用 CHECK 约束收紧语义 ​

CHECK 是最经济的「数据库级业务校验」。它语法简单、执行开销极低，却能拦住一整类脏数据：

sql
CREATE TABLE payments (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount     numeric(12,2) NOT NULL CHECK (amount > 0),
  fee        numeric(12,2) NOT NULL DEFAULT 0 CHECK (fee >= 0),
  status     text NOT NULL CHECK (status IN ('pending','success','failed','refunded')),
  method     text NOT NULL CHECK (method IN ('card','alipay','wechat')),
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT ck_payments_fee_le_amount CHECK (fee <= amount)   -- 跨列约束
);

跨列约束能表达单列约束表达不了的规则：

sql
CONSTRAINT ck_period_valid       CHECK (end_date IS NULL OR end_date >= start_date)   -- 区间有序
CONSTRAINT ck_published_consist  CHECK (status <> 'published' OR published_at IS NOT NULL)
CONSTRAINT ck_contact_present    CHECK (phone IS NOT NULL OR email IS NOT NULL)        -- 至少一个联系方式

关于 CHECK 与 NULL 的交互，有一条必记规则：CHECK 对 NULL 的结果视为通过。因为 NULL 在布尔表达式里是「未知」，而 CHECK 只在结果为 false 时拒绝：

sql
CREATE TABLE demo_check_null (
  id bigint PRIMARY KEY,
  amount numeric(12,2) CHECK (amount > 0)   -- 允许 NULL！
);

INSERT INTO demo_check_null VALUES (1, NULL);   -- 插入成功
SELECT * FROM demo_check_null;
text
 id | amount
----+--------
  1 |
(1 row)

所以要拦住 NULL，必须同时写 NOT NULL；若要把 NULL 纳入语义（允许为空但非空时须合法），则显式写出：

sql
amount numeric(12,2) NOT NULL CHECK (amount > 0)          -- 不允许 NULL
CHECK (amount IS NULL OR amount > 0)                      -- 允许 NULL，有值则必须为正

枚举 vs 查找表 vs TEXT + CHECK ​

「有限取值集合」有三种实现方式，各有明确适用边界：

方案实现优点缺点
TEXT + CHECKCHECK (status IN (...))简单、无额外对象、值可读改取值要 ALTER 约束
枚举类型（CREATE TYPE ... AS ENUM）独立类型对象类型级复用、有序加值要 ALTER TYPE，删值几乎不可行
查找表（lookup table）独立表 + 外键可加元数据、可动态增删多一次 JOIN

先用 TEXT + CHECK（最常用的默认选择）；增加取值只需替换约束（ADD CONSTRAINT 前要先 DROP 旧的）：

sql
CREATE TABLE orders_status_check (
  id     bigint PRIMARY KEY,
  status text NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending','paid','shipped','completed','cancelled'))
);

ALTER TABLE orders_status_check DROP CONSTRAINT orders_status_check_status_check;
ALTER TABLE orders_status_check ADD CONSTRAINT orders_status_check_status_check
  CHECK (status IN ('pending','paid','shipped','completed','cancelled','refunding'));

枚举类型适合稳定的、跨多表复用的取值集合，它支持按定义顺序排序（独有能力，但与直觉可能不符：cancelled 排在 shipped 之后）：

sql
CREATE TYPE order_status AS ENUM ('pending','paid','shipped','completed','cancelled');
CREATE TABLE orders_enum (
  id bigint PRIMARY KEY, status order_status NOT NULL DEFAULT 'pending'
);
SELECT * FROM orders_enum ORDER BY status;   -- 按枚举定义顺序

枚举删除值几乎不可行

ALTER TYPE ... ADD VALUE 可以加值，但删除枚举值没有直接语法。若某个值已被使用，只能通过新建类型 + 转换列的迂回方式处理。因此取值会动态增删的场景，别用枚举。

查找表适合需要携带元数据或由运营动态维护的场景：

sql
CREATE TABLE order_statuses (
  code text PRIMARY KEY, label text NOT NULL,
  sort_no integer NOT NULL, is_final boolean NOT NULL DEFAULT false
);

INSERT INTO order_statuses VALUES
  ('pending','待支付',10,false), ('paid','已支付',20,false), ('shipped','已发货',30,false),
  ('completed','已完成',40,true), ('cancelled','已取消',50,true);

CREATE TABLE orders_lookup (
  id bigint NOT NULL PRIMARY KEY,
  status text NOT NULL REFERENCES order_statuses(code),
  created_at timestamptz NOT NULL DEFAULT now()
);

三者的选择可以用一句话概括：取值几乎不变且要跨表复用 → 枚举；取值稳定但只需本表校验 → TEXT + CHECK；取值会变或需要元数据 → 查找表。

JSONB：何时适合何时不适合 ​

JSONB 是「免建表的扩展字段」方案，它的边界非常清楚：

适合 JSONB不适合 JSONB
第三方回调的原始报文需要外键约束的关联
稀疏的、每个类型都不同的属性高频 WHERE/ORDER BY 的字段
需要整体读写的配置需要唯一约束的键
结构会频繁变化的扩展字段金额等需要精确类型/精度校验的值
嵌套结构（数组、对象）参与 JOIN 的键

判断标准可以量化为一句：「这个字段会不会用来过滤、排序、约束、关联？」会的话就拆成独立列。

sql
-- 适合：原始报文留档 + 少量扩展属性
CREATE TABLE webhook_events (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  source      text NOT NULL,
  event_type  text NOT NULL,
  raw_payload jsonb NOT NULL,          -- 完整原始报文，只整体读取
  received_at timestamptz NOT NULL DEFAULT now()
);

-- 不适合：把高频过滤的字段埋进 JSONB
CREATE TABLE orders_bad_jsonb (
  id   bigint PRIMARY KEY,
  data jsonb NOT NULL    -- data->>'status' 频繁过滤 → 应拆成 status 列
);

如果确实需要在 JSONB 上过滤，两种索引方案要分清用途：包含查询（@>）用 GIN（如 USING gin (raw_payload jsonb_path_ops)）；取单个键做范围/排序用表达式 B-Tree（如 (((data ->> 'amount')::numeric))）。

JSONB 不做类型校验

JSONB 里的数字只是「JSON 数字」，不会检查 numeric(12,2) 这样的精度。金额放进 JSONB，就失去了数据库层的精度保护。

大字段与 TOAST 的交互 ​

PostgreSQL 的页大小默认 8KB，单行不能跨页。因此当一个列的值过大时，必须外置存储到 TOAST 表（The Oversized-Attribute Storage Technique）：

text
行内存储（< 约 2KB）：  [行头][列A][列B 大值][列C]
触发 TOAST（大值超阈值）：
  主表行: [行头][列A][TOAST 指针][列C]
  TOAST 表: [chunk1][chunk2][chunk3]... ← 值被压缩后切成约 2KB 的块

触发条件与列的存储策略（typstorage）有关：

策略含义典型类型
PLAIN不压缩不外置，值必须放行内int8、uuid、boolean
EXTENDED先压缩，仍过大则外置text、bytea、jsonb
EXTERNAL不压缩，直接外置可用于 varchar(n) 关闭压缩
MAIN优先压缩，尽量不外置numeric

用系统函数观察同一个值在行内与 TOAST 后的差异：

sql
CREATE TABLE toast_demo (id bigint, body text);
INSERT INTO toast_demo VALUES (1, repeat('x', 200000));   -- 200KB，远超 8KB 页

SELECT pg_size_pretty(pg_column_size(body)::bigint) AS logical_size FROM toast_demo;

SELECT pg_size_pretty(pg_relation_size('toast_demo'))  AS main_table,
       pg_size_pretty(pg_relation_size(reltoastrelid)) AS toast_table
FROM pg_class WHERE relname = 'toast_demo';

-- 关闭压缩，换取向「不解压就能读子串」的能力
ALTER TABLE toast_demo ALTER COLUMN body SET STORAGE EXTERNAL;
text
 logical_size
--------------
 195 kB
(1 row)

 main_table | toast_table
------------+-------------
 8192 bytes | 8192 bytes
(2 rows)

ALTER TABLE

（repeat('x', 200000) 高度可压缩，可能恰好放回行内；换用随机数据则会看到 TOAST 表显著增长。）

实践结论

text/bytea 存大值不是「不能用」，而是要知道代价：读大字段会额外访问 TOAST 表，且可能触发解压。因此查询列表页时应避免 SELECT *，只取需要的列——这能直接避免 TOAST 读放大。

生成列与默认值：派生值与 volatile 函数 ​

生成列（PostgreSQL 12+）由数据库自动计算并存储，应用无法写入。它适合由本行其它列派生的值：

sql
CREATE TABLE order_lines (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  qty        integer NOT NULL CHECK (qty > 0),
  unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0),
  tax_rate   numeric(5,4)  NOT NULL DEFAULT 0.1300 CHECK (tax_rate >= 0 AND tax_rate < 1),
  -- 生成列：自动维护，应用无法写入
  net_amount numeric(14,2) GENERATED ALWAYS AS (qty * unit_price) STORED,
  tax_amount numeric(14,2) GENERATED ALWAYS AS (round(qty * unit_price * tax_rate, 2)) STORED,
  gross_amount numeric(14,2)
    GENERATED ALWAYS AS (qty * unit_price + round(qty * unit_price * tax_rate, 2)) STORED
);

INSERT INTO order_lines (qty, unit_price, tax_rate) VALUES (2, 99.90, 0.1300);

SELECT id, qty, unit_price, net_amount, tax_amount, gross_amount FROM order_lines;
text
 id | qty | unit_price | net_amount | tax_amount | gross_amount
----+-----+------------+------------+------------+--------------
  1 |   2 |      99.90 |     199.80 |      25.97 |       225.77
(1 row)

生成列的关键限制：

限制说明
只能 STORED虚拟生成列（VIRTUAL）在 PostgreSQL 18 之前不可用
不能引用其它行表达式只能引用本行的列
不能引用 volatile 函数now()、random() 被禁止
不能是身份列一个列不能既是生成列又是 IDENTITY
不能直接写入INSERT/UPDATE 指定该列会报错
sql
-- 错误！生成列不能显式写入
INSERT INTO order_lines (qty, unit_price, net_amount) VALUES (1, 10, 999);
text
ERROR:  cannot insert a non-DEFAULT value into column "net_amount"
DETAIL:  Column "net_amount" is a generated column.

注意 now() 是 volatile 函数（每次调用返回值可能不同），所以不能用它做生成列：

sql
-- 错误！generation expression is not immutable
ALTER TABLE order_lines ADD COLUMN created_at timestamptz
  GENERATED ALWAYS AS (now()) STORED;

-- 正确：用 DEFAULT 而非生成列
ALTER TABLE order_lines ADD COLUMN created_at timestamptz NOT NULL DEFAULT now();

DEFAULT 与生成列的分工：DEFAULT 在每次插入时求值（可包含 volatile 函数），生成列则在写入时按表达式计算并持久化。二者不能互相替代：

sql
CREATE TABLE events (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT now(),          -- 这一行插入的时刻
  uuid       uuid        NOT NULL DEFAULT gen_random_uuid(),
  day        date        NOT NULL DEFAULT CURRENT_DATE
);

因此 DEFAULT now() 是「这一行插入的时刻」，而 DEFAULT '2026-01-01'::timestamptz 是常量；二者差异在历史数据补齐时很明显。但「用其它列的值计算」只能由生成列承担，DEFAULT 做不到：

sql
-- 错误！DEFAULT 表达式不能引用其它列
CREATE TABLE bad_default (
  a int,
  b int DEFAULT a * 2
);
text
ERROR:  cannot use column reference in DEFAULT expression
LINE 2:   b int DEFAULT a * 2;

列级注释与约束的在线代价 ​

列注释 ​

用 COMMENT 给列加业务语义，它能出现在 \d+ 输出与各种 GUI 工具里：

sql
COMMENT ON TABLE orders IS '订单主表';
COMMENT ON COLUMN orders.status IS '订单状态：pending/paid/shipped/completed/cancelled';
COMMENT ON COLUMN orders.amount IS '订单总金额，单位元，含税';

-- 查看注释
SELECT a.attname, col_description(a.attrelid, a.attnum) AS comment
FROM pg_attribute a
WHERE a.attrelid = 'orders'::regclass AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
text
 attname |                      comment
---------+---------------------------------------------------
 id      |
 status  | 订单状态：pending/paid/shipped/completed/cancelled
 amount  | 订单总金额，单位元，含税
(3 rows)

加约束的在线代价 ​

给已有表加约束的代价差异很大，必须分清：

操作是否扫描全表是否阻塞写在线做法
ADD COLUMN ... DEFAULT 常量否（PG 11+ 元数据级）极短 ACCESS EXCLUSIVE直接加
SET NOT NULL是（PG 12+ 可用已有 CHECK 跳过）短锁先加 CHECK ... NOT VALID
ADD CHECK是短锁ADD CONSTRAINT ... NOT VALID + VALIDATE
ADD FOREIGN KEY是（校验引用）短锁NOT VALID + VALIDATE
ADD UNIQUE/PRIMARY KEY是（建索引）长锁CREATE UNIQUE INDEX CONCURRENTLY + USING INDEX

NOT VALID 是「先立规矩、后补校验」的关键机制——加约束时立即生效于新数据，已有数据的校验推迟到 VALIDATE，从而把一次性 ACCESS EXCLUSIVE 拆成两段短锁：

sql
-- 第 1 步：加约束但不校验存量数据（只持极短锁）
ALTER TABLE payments
  ADD CONSTRAINT ck_payments_fee_le_amount CHECK (fee <= amount) NOT VALID;

-- 第 2 步：在较弱的锁下校验存量数据
ALTER TABLE payments VALIDATE CONSTRAINT ck_payments_fee_le_amount;

SET NOT NULL 也有在线做法。PostgreSQL 12+ 可以利用已有的 CHECK 约束跳过全表扫描：

sql
-- 在线给已有列加 NOT NULL 的标准两步法
ALTER TABLE big_table
  ADD CONSTRAINT ck_big_table_col_not_null CHECK (col IS NOT NULL) NOT VALID;

ALTER TABLE big_table VALIDATE CONSTRAINT ck_big_table_col_not_null;

-- 校验完成后，SET NOT NULL 会复用这个 CHECK，无需再扫表
ALTER TABLE big_table ALTER COLUMN col SET NOT NULL;

-- 收尾：删掉临时的 CHECK（NOT NULL 已经接管）
ALTER TABLE big_table DROP CONSTRAINT ck_big_table_col_not_null;

加列默认值的旧版本行为

PostgreSQL 11 之前，ADD COLUMN ... DEFAULT 常量 会重写整张表；11 起改为元数据级操作，瞬间完成。但 DEFAULT 是非常量表达式（如 now()）时，仍会重写全表。给大表加带 now() 默认值的列要格外小心。

实战示例 ​

场景：为电商系统设计一张生产级的 payments 表，完整覆盖类型选择、约束、索引、注释与在线变更，并逐条评审。

第 1 步：完整 DDL

sql
DROP TABLE IF EXISTS payments CASCADE;

CREATE TABLE payments (
  id              bigint        GENERATED ALWAYS AS IDENTITY,   -- 主键：bigint 代理键
  payment_no      text          NOT NULL,                        -- 业务编号，独立唯一键
  order_id        bigint        NOT NULL,                        -- 外键：<单数表名>_id
  amount          numeric(14,2) NOT NULL CHECK (amount > 0),     -- 金额：numeric 精确小数
  fee             numeric(14,2) NOT NULL DEFAULT 0 CHECK (fee >= 0),
  status          text          NOT NULL DEFAULT 'pending'        -- 枚举语义：TEXT + CHECK
                                CHECK (status IN ('pending','success','failed','refunded')),
  method          text          NOT NULL
                                CHECK (method IN ('card','alipay','wechat','balance')),
  currency        char(3)       NOT NULL DEFAULT 'CNY',           -- 币种：定长 3 字符
  paid_at         timestamptz,
  created_at      timestamptz   NOT NULL DEFAULT now(),
  updated_at      timestamptz   NOT NULL DEFAULT now(),
  gateway_payload jsonb         NOT NULL DEFAULT '{}'::jsonb,     -- 第三方报文

  CONSTRAINT pk_payments              PRIMARY KEY (id),
  CONSTRAINT uq_payments_no           UNIQUE (payment_no),
  CONSTRAINT ck_payments_fee_le_amt   CHECK (fee <= amount),
  CONSTRAINT ck_payments_paid_at      CHECK ((status = 'success') = (paid_at IS NOT NULL))
);

-- 索引：外键列、状态列、时间列
CREATE INDEX idx_payments_order_id   ON payments (order_id);
CREATE INDEX idx_payments_status     ON payments (status) WHERE status <> 'success';
CREATE INDEX idx_payments_created_at ON payments (created_at DESC);

-- 注释
COMMENT ON TABLE  payments           IS '支付流水表';
COMMENT ON COLUMN payments.payment_no IS '对外支付单号，全局唯一';
COMMENT ON COLUMN payments.amount     IS '支付金额（正数，单位：元）';
COMMENT ON COLUMN payments.status     IS '状态：pending/success/failed/refunded';
text
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
COMMENT

第 2 步：逐条评审——验证约束真的生效

sql
-- 正常插入
INSERT INTO payments (payment_no, order_id, amount, fee, status, method, paid_at)
VALUES ('PAY-20260928-0001', 1, 199.80, 1.20, 'success', 'alipay', now());
text
INSERT 0 1
sql
-- 违规 1：金额为负
INSERT INTO payments (payment_no, order_id, amount, method) VALUES ('X1', 1, -1, 'card');
text
ERROR:  new row for relation "payments" violates check constraint "payments_amount_check"
DETAIL:  Failing row contains (2, X1, 1, -1.00, 0.00, pending, card, CNY, null, ...).
sql
-- 违规 2：手续费大于金额
INSERT INTO payments (payment_no, order_id, amount, fee, method) VALUES ('X2', 1, 10, 999, 'card');
-- 违规 3：status 与 paid_at 不一致
INSERT INTO payments (payment_no, order_id, amount, method, status) VALUES ('X3', 1, 10, 'card', 'success');
text
ERROR:  new row for relation "payments" violates check constraint "ck_payments_fee_le_amt"
ERROR:  new row for relation "payments" violates check constraint "ck_payments_paid_at"

第 3 步：验证索引被正确使用

sql
EXPLAIN (COSTS OFF)
SELECT id, payment_no, amount FROM payments
WHERE status = 'failed' AND created_at >= now() - interval '7 days';
text
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Bitmap Heap Scan on payments
   Recheck Cond: (status = 'failed'::text)
   Filter: (created_at >= (now() - '7 days'::interval))
   ->  Bitmap Index Scan on idx_payments_status
         Index Cond: (status = 'failed'::text)
(5 rows)

第 4 步：在线给大表补一个 NOT NULL 列（演示代价控制）

sql
-- 大表上加列：PG 11+ 常量默认值是元数据操作，几乎瞬间完成
ALTER TABLE payments ADD COLUMN channel_code text NOT NULL DEFAULT 'default';
text
ALTER TABLE
sql
-- 再补一个约束，用 NOT VALID + VALIDATE 避免长时间持锁
ALTER TABLE payments
  ADD CONSTRAINT ck_payments_channel_len CHECK (length(channel_code) <= 32) NOT VALID;

ALTER TABLE payments VALIDATE CONSTRAINT ck_payments_channel_len;
text
ALTER TABLE
ALTER TABLE

第 5 步：用 pg_stats 复核列设计的合理性

sql
ANALYZE payments;

SELECT attname, null_frac, n_distinct, avg_width
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'payments'
ORDER BY avg_width DESC
LIMIT 6;
text
    attname     | null_frac | n_distinct | avg_width
----------------+-----------+------------+-----------
 gateway_payload |         0 |         -1 |        34
 payment_no      |         0 |         -1 |        19
 order_id        |         0 |         -1 |         8
 status          |         0 |          4 |         7
 method          |         0 |          1 |         6
 currency        |         0 |          1 |         4
(6 rows)

关注两点：null_frac 都应接近 0（说明 NOT NULL 用得充分）；avg_width 不应出现异常大的定长列。

第 6 步：清理——DROP TABLE IF EXISTS payments CASCADE;，以及本节演示过的临时表 articles, order_col_bad, order_col_good, demo_check_null, orders_status_check, orders_enum, orders_lookup, order_statuses, order_lines, events, webhook_events, orders_bad_jsonb, toast_demo, products_sparse, products_flex, product_electronics, orders, t_varchar, t_text_check, t_text，并 DROP TYPE IF EXISTS order_status;。

注意事项 ​

  • 错误！不要用 float4/float8 存金额。 浮点是二进制近似值，0.1 + 0.2 != 0.3，累加会累积误差。金额一律 numeric(14,2)。

    sql
    amount float8 NOT NULL        -- 错误！
amount numeric(14,2) NOT NULL -- 正确

  • 错误！不要用 varchar(36) 存 UUID。 浪费 20 字节且丢失类型语义，无法使用 uuid 的专用函数。用 uuid 类型即可（id uuid PRIMARY KEY DEFAULT uuidv7()）。

  • 错误！不要用 timestamp（不带时区）存业务时间。 跨时区存储会产生歧义，一律用 timestamptz。

    sql
    created_at timestamp   NOT NULL DEFAULT now()  -- 不推荐
created_at timestamptz NOT NULL DEFAULT now()  -- 推荐

  • 错误！只写 CHECK 不写 NOT NULL 拦不住 NULL。 CHECK 对 NULL 结果视为通过，必须两者同写。

  • 错误！生成列不能引用 volatile 函数（如 now()、random()），也不能引用其它行。需要「插入时刻」就用 DEFAULT now()。

  • 错误！DEFAULT 表达式不能引用其它列。 需要派生就用生成列。

  • 错误！不要用枚举类型存会动态增删的取值。 ALTER TYPE ... ADD VALUE 可以加值，但删值没有直接语法。

  • 不要把高频过滤字段埋进 JSONB。 需要过滤就用独立列 + 索引；JSONB 只用来承载稀疏、易变、整体读写的属性。

  • 注意 ADD COLUMN ... DEFAULT now() 会重写整表。 常量默认值是元数据操作，但非常量表达式不是。

  • 注意大字段会触发 TOAST。 列表页查询避免 SELECT *（body 之类大列会带来额外的 TOAST 表访问与解压），只取必要列。

  • 注意 ADD COLUMN 后列的物理位置在末尾。 变更列顺序必须重建表，不要为了「视觉顺序」频繁重建。

最佳实践 ​

  • 类型选择优先级：语义正确 > 空间效率 > 查询形态。 金额 numeric、时间 timestamptz、ID bigint/uuid、IP inet、布尔 boolean。

  • NOT NULL 默认加上（如 status text NOT NULL DEFAULT 'pending'），只有业务上确实未知才允许为空。

  • DEFAULT 只放常量或稳定的函数，用 now() 表达「插入时刻」，别用 random() 之类需要可复现的字段。

  • 能表达的规则全部用约束表达，CHECK/UNIQUE/FOREIGN KEY 比应用层校验可靠得多：

    sql
    CHECK (status IN ('pending','success','failed','refunded')),
CHECK (fee <= amount),
CHECK ((status = 'success') = (paid_at IS NOT NULL))

  • 列顺序：定长列按宽度降序（8→4→2→1），变长列放最后，例如 bigint, bigint, integer, integer, boolean, boolean, text, jsonb。

  • 大表加约束一律用 NOT VALID + VALIDATE 两步法，把持锁时间从「全表扫描级」压到「元数据级」。

    sql
    ALTER TABLE big_table ADD CONSTRAINT ck_x CHECK (...) NOT VALID;
ALTER TABLE big_table VALIDATE CONSTRAINT ck_x;

  • 大表加 NOT NULL 用 CHECK 复用技巧（PostgreSQL 12+），避免全表扫描。

  • 给每个列写 COMMENT（如 COMMENT ON COLUMN payments.status IS '状态：...'），把命名无法表达的业务语义固化在库内。

  • 大表加带默认值的列时确认版本行为（PostgreSQL 11+ 常量默认值为元数据操作），非常量默认值要评估重写代价。

  • 上线前用 pg_stats 复核 null_frac（是否该收紧 NOT NULL）与 avg_width（是否存在异常宽的列）。

  • 列表/详情查询避免 SELECT *，既避免 TOAST 读放大，也避免新增列带来的意外行为变化。

下一节

继续学习：表继承概述

参考链接 ​