PostgreSQL 数据库设计规范：范式、类型选择与迁移演进

引言

schema 是数据库的「地基」——改起来最痛，错起来最隐蔽。PostgreSQL 给了极大的设计自由（丰富类型、JSONB、可扩展），也意味着你要自己定规范。本文沉淀一套可落地的设计规范：主键选型、命名、类型选择、范式与反范式权衡、约束与索引纪律、审计与软删除、分区演进，最后给出用迁移工具管理 schema 变更的工程流程。

前置：/postgres-index-types/（索引与唯一性）、/postgres-transaction-isolation/（并发与锁）、/postgres-vacuum-bloat/（设计影响存储膨胀）。

目录

• 1. 设计前的三问：读多写多、规模、演进
• 2. 主键策略：序列、UUID 与 Snowflake
• 3. 命名与字段规范
• 4. 类型选择：数值、时间、JSONB 与文本
• 5. 范式与反范式权衡
• 6. 约束、外键与唯一性
• 7. 审计字段与软删除
• 8. 大表演进：分区与分表
• 9. 迁移工具与 schema 演进流程
• 10. 速查表与一句话记忆
• 延伸阅读

1. 设计前的三问：读多写多、规模、演进

动手建表前先回答三问，答案直接决定设计：

设计顺序：先理清业务实体与关系（ER 模型），再落成表——反着来（想到一张建一张）是后续灾难的根源。

2. 主键策略：序列、UUID 与 Snowflake

-- PostgreSQL 推荐：IDENTITY 语法（标准、可控）
CREATE TABLE users (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

-- UUID（应用或扩展生成）
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE users (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid()
);

选型心法：

□ 对外暴露的 ID 一律 UUID（不可枚举、不可猜）
□ 内部主键用 IDENTITY 即可
□ 大表 + 分布式用 UUID v7（时间有序，B-tree 友好）
□ 别用 `serial`（隐式 sequence，语义不如 IDENTITY 清晰）

注意：UUID v4 完全随机 → 索引插入页分裂多、缓存命中差；大表慎用纯随机主键。

3. 命名与字段规范

表名/列名：小写、下划线分隔、复数表名（orders）或单数（order）——团队统一即可，建议单数。

必含字段规范（绝大多数业务表）：

CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers(id),
    status      text NOT NULL DEFAULT 'pending',
    amount      numeric(12,2) NOT NULL CHECK (amount >= 0),
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now(),
    deleted_at  timestamptz            -- 软删除（可空）
);

命名纪律：

□ 布尔列用 is_/has_ 前缀（is_active）
□ 时间列用 _at 后缀（created_at）
□ 外键列：<实体>_id（customer_id）
□ 金额列类型 numeric，别用 float
□ 状态字段用 text + CHECK 或 enum，别用魔法数字

4. 类型选择：数值、时间、JSONB 与文本

JSONB 的使用边界：

□ 适合：可扩展字段、第三方数据、配置快照
□ 不适合：需要强约束/频繁按内部字段查询/跨表 JOIN 的字段
□ 演进：JSONB 字段一旦稳定，尽早拆成正式列（利于约束与索引）

记忆：类型选择一句话——金额 numeric、时间 timestamptz、可扩展 jsonb、其余 text；类型即约束，选对类型省一半校验代码。

5. 范式与反范式权衡

范式（Normalization）：消除冗余、保证一致性。反范式：冗余存储、以空间换查询性能。

实用决策：

□ 事务性核心数据 → 严格范式（订单/账户）
□ 报表/读模型 → 反范式 + 物化视图
□ 高频展示的派生值（如商品销量）→ 冗余列 + 触发器等同步
□ 用「读模型表」承载反范式，别污染事务表

心法：先 3NF 起步，性能瓶颈出现再「按需反范式」——反向设计（一开始全冗余）后患无穷。

6. 约束、外键与唯一性

约束是数据库的「免费保险」，别只靠应用层校验：

-- 非空 + 检查
CREATE TABLE accounts (
    balance numeric(12,2) NOT NULL CHECK (balance >= 0)
);

-- 唯一约束（业务唯一性在数据库兜底）
CREATE UNIQUE INDEX idx_email_unique ON users (lower(email));
CREATE UNIQUE INDEX idx_uniq ON products (category_id, sku);   -- 联合唯一

-- 外键（引用完整性）
CREATE TABLE order_items (
    order_id bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id bigint NOT NULL REFERENCES products(id)
);

外键决策：

记忆：唯一性、非空、CHECK、外键——四个约束是数据质量的四条腿，能加就加，应用校验是第二道防线不是唯一防线。

7. 审计字段与软删除

审计字段：谁建的、谁改的、何时改的。

CREATE TABLE orders (
    ...,
    created_by bigint,                -- 可选
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

软删除（deleted_at）vs 物理删除：

软删除的坑：

□ 唯一约束 + 软删除：`UNIQUE(email)` 会挡恢复——用 `UNIQUE(email, deleted_at)`（NULL 不参与唯一）
□ 查询过滤靠视图或默认过滤列，防止全表漏掉
□ 大表软删除积压 → 定期物理清理归档

建议：业务可恢复的用软删除 + 定期归档；纯日志数据物理删除或用分区 DROP。

8. 大表演进：分区与分表

数据量超过千万级，提前用声明式分区规划：

-- 按时间分区（事件/日志/订单）
CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY,
    created_at timestamptz NOT NULL,
    payload jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_09 PARTITION OF events
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE events_2026_10 PARTITION OF events
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

-- 查询只扫相关分区
CREATE INDEX ON events (created_at);

分区收益：

□ 查询剪枝：只扫命中分区
□ 快速归档：DROP PARTITION 秒级清理旧数据
□ 独立维护：分区级 REINDEX/VACUUM

分区注意：外键/唯一约束必须含分区键、分区数别太多（上千个管理开销大）、配合自动化定期建分区（cron）。

9. 迁移工具与 schema 演进流程

schema 演进必须走迁移工具，禁止手改生产库：

-- Alembic（Python）示例
-- migrations/xxx_add_status.py
def upgrade():
    op.add_column('orders', sa.Column('status', sa.Text(), nullable=False,
        server_default='pending'))
def downgrade():
    op.drop_column('orders', 'status')

演进纪律：

□ 迁移脚本唯一、顺序、可回滚（upgrade + downgrade）
□ 加列用默认值或先 NULL 后填，避免锁长表
□ 大表加索引用 CONCURRENTLY（不锁写）
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);
□ 上线流程：开发→测试→灰度→生产，每环境跑同套迁移
□ 迁移即代码评审的一部分

记忆：schema 演进 = 迁移脚本 + 顺序回滚 + CONCURRENTLY 避锁——「改表」从来不是「删了重建」，而是版本化的、可回退的、不停服的工程。

10. 速查表与一句话记忆

一句话记忆：schema 即地基——主键选型、类型即约束、范式起步按需反范式、四类约束兜底质量、大表分区演进、迁移版本化可回滚——把「改表的痛」前置到「设计时」，十年不后悔。

延伸阅读

• /postgres-index-types/ — 索引与唯一性设计
• /postgres-transaction-isolation/ — 并发与锁对 schema 的影响
• /postgres-vacuum-bloat/ — 高频更新表的膨胀设计预防
• /postgres-extensions-scale/ — 分区与分布式扩展
• /postgres-jsonb-performance/ — JSONB 字段取舍
• [[postgresql]] — PostgreSQL 数据库专题