PostgreSQLNotes

第 31 章：项目实战

zjc 于 2026-01-31 发布

这是《PostgreSQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 本章设计一个电商交易与订单分析系统，覆盖表结构、索引、事务、分区、备份和监控。

31.1 业务流程

用户下单
 -> 创建订单
 -> 支付
 -> 扣库存
 -> 发货
 -> 售后

核心要求：

  1. 订单号唯一；
  2. 金额精确；
  3. 状态流转合法；
  4. 库存不能超卖；
  5. 支付必须幂等；
  6. 历史订单可归档。

31.2 表设计

CREATE TYPE order_status AS ENUM (
    'CREATED', 'PAID', 'SHIPPED', 'CANCELLED', 'REFUNDED'
);

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY,
    order_no text NOT NULL,
    user_id bigint NOT NULL,
    status order_status NOT NULL DEFAULT 'CREATED',
    amount numeric(12,2) NOT NULL CHECK (amount >= 0),
    paid_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    created_date date GENERATED ALWAYS AS (created_at::date) STORED,
    CONSTRAINT pk_orders PRIMARY KEY (created_date, id),
    CONSTRAINT uq_orders_order_no UNIQUE (order_no)
)
PARTITION BY RANGE (created_date);

31.3 索引

CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);

CREATE INDEX idx_orders_status_created
ON orders(status, created_at)
WHERE status IN ('CREATED', 'PAID');

按订单号查询必须确保唯一索引可用；按用户查询使用复合索引；后台任务使用部分索引。

31.4 状态流转

UPDATE orders
SET status = 'PAID', paid_at = now()
WHERE order_no = 'O202608250001'
  AND status = 'CREATED'
RETURNING id, amount;

如果返回 0 行，说明状态已变化或订单不存在，不能直接重试扣款。

31.5 库存扣减

UPDATE sku_stock
SET available = available - 1
WHERE sku_id = 1001
  AND available >= 1
RETURNING version;

高并发热点 SKU 可考虑排队、预占、批量合并或应用层限流。

31.6 支付幂等

INSERT INTO payment_requests(payment_no, order_no, amount)
VALUES ('P001', 'O001', 99.00)
ON CONFLICT (payment_no)
DO UPDATE SET request_time = excluded.request_time
WHERE payment_requests.status = 'INIT'
RETURNING id, status;

支付回调必须以支付平台流水为准，并记录请求日志。

31.7 分区维护

CREATE TABLE orders_2026_09 PARTITION OF orders
FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

定时任务提前创建未来分区，并归档过期分区。

31.8 监控

指标 目标
下单成功率 核心告警
死锁 立即告警
慢 SQL 持续治理
复制延迟 RPO 相关
死元组 autovacuum 健康度
备份成功率 每日检查

31.9 上线清单

1. 约束和索引 review
2. 事务边界 review
3. 压测
4. HA 演练
5. 备份恢复演练
6. 权限最小化
7. 监控告警
8. 回滚预案

本章小结

电商系统要把唯一性、状态流、幂等、库存一致性和归档策略放在模型层解决。PostgreSQL 的约束、事务、分区和索引能力能提供较强兜底，但仍需业务重试和监控闭环。

思考题

  1. 订单表为什么按时间分区？
  2. 部分索引适合哪些后台查询？
  3. 状态更新为什么要带状态条件？
  4. 支付幂等如何设计？
  5. 热点库存有哪些优化方向？