Skip to content

约 9 分钟阅读

PostgreSQL ​

订单的金额、客户与支付状态需要稳定约束;配送选项经常增减,又希望能按 JSON 字段筛选;运营还需要客户内排名和报表。PostgreSQL 能在同一事务数据库里承载这些需求,但 JSONB、索引和扩展不是“存进去就会自动快”的开关。

本页以 PostgreSQL 16 为语法和运行基线,串联建模、查询、并发、维护与恢复。这里的版本用于复现示例,不代表当前最新主版本;已有系统升级要另做扩展、驱动和备份兼容验证。通用选型见数据库总览,关系型与图模型的边界见图数据库。

订单请求、关系字段与JSONB索引、MVCC旧版本、WAL和备份之间的关系

图中 WAL 是恢复与物理复制的基础,索引服务查询,VACUUM 回收不再需要的行版本;三者解决不同问题。副本接收了 WAL 也不等于已经回放,副本存在更不等于有可恢复的历史备份。

建立独立练习环境 ​

以下命令在 Bash 中执行。容器名、端口和卷专供本页练习;若名称或端口已占用,换一组再运行,不要删除已有数据库。镜像首次启动时创建用户与库,已有数据卷不会因重设初始化变量而改密码或重建库。

bash
export POSTGRES_PASSWORD="$(openssl rand -hex 24)"
docker run --name pg-lab \
  --env POSTGRES_PASSWORD --env POSTGRES_DB=orders_demo \
  --mount source=pg-lab-data,target=/var/lib/postgresql/data \
  --publish 127.0.0.1:15433:5432 \
  --detach postgres:16-alpine
unset POSTGRES_PASSWORD
docker exec -it pg-lab psql -U postgres -d orders_demo

随机密码仍会保存在容器配置中;这是本地练习,生产采用受控凭据、受限来源和独立业务账号。容器内 psql 使用镜像默认的本地连接规则,不代表宿主机 TCP 可以无密码访问。postgres 是管理员,不应作为应用连接池账号。

连接后先执行 SELECT version(), current_database(), current_user;,再用 SHOW config_file;、SHOW hba_file; 定位实际配置。postgresql.conf 管监听与资源,pg_hba.conf 按顺序匹配认证规则;第一条匹配后认证失败不会继续尝试后续规则。远端连接需同时检查监听地址、网络、防火墙、HBA、账号和 TLS。不要为了排错直接开放所有地址或改成 trust。

HBA 修改可通过 SELECT pg_reload_conf(); 重载,并查询 pg_hba_file_rules 的 error 检查语法;这不证明目标客户端已成功连接。shared_buffers、shared_preload_libraries 等需要重启,先查 pg_settings.context、pending_restart。work_mem 可能被每个排序/哈希操作及并行进程使用,不能按“每连接只用一次”估总量;effective_cache_size 是计划估计,不会分配对应内存。

用关系字段守住业务合同 ​

在这个可丢弃的新库中顺序执行:

sql
CREATE TABLE customers (
  id bigint PRIMARY KEY,
  name text NOT NULL
);
CREATE TABLE orders (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id),
  amount numeric(12,2) NOT NULL CHECK (amount >= 0),
  status text NOT NULL CHECK (status IN ('pending','paid','cancelled')),
  created_at timestamptz NOT NULL,
  options jsonb NOT NULL DEFAULT '{}'::jsonb
    CHECK (jsonb_typeof(options) = 'object')
);
INSERT INTO customers VALUES (1,'小林'),(2,'小周'),(3,'小许');
INSERT INTO orders (id, customer_id, amount, status, created_at, options) VALUES
  (101,1,50.00,'paid','2026-10-02 10:00:00+08',
   '{"delivery":"express","gift":true}'),
  (102,1,30.00,'pending','2026-10-02 10:00:00+08','{}'),
  (103,1,39.90,'paid','2026-10-03 11:00:00+08',
   '{"delivery":"standard"}'),
  (104,2,20.00,'cancelled','2026-09-30 23:59:59+08','{}');
SELECT setval(pg_get_serial_sequence('orders','id'),
              (SELECT max(id) FROM orders));

金额用精确 NUMERIC;客户关联、金额和状态范围由约束保证。外键不会自动为引用方的 customer_id 创建索引,需要根据查询和父表删除检查成本决定。显式导入编号后将 identity 序列推进到最大编号,避免下一次自动编号碰撞;序列取号有空洞、回滚不退号,不能当连续发票编号。

timestamptz 表达时间点,展示随连接时区变化,不保留原输入的时区名称;预约所属的“上海时间”需要时可另存业务时区。JSONB 适合可变配送选项,但不应把金额、支付状态等核心约束全部藏进去。JSON 保留输入文本,JSONB 以分解形式存储,通常便于索引,但不会保留空白、键顺序或重复键的原貌。

查询结果与索引条件要一致 ​

sql
CREATE INDEX orders_customer_time_idx
  ON orders (customer_id, created_at DESC, id DESC);
CREATE INDEX orders_pending_idx
  ON orders (created_at, id) WHERE status='pending';
CREATE INDEX orders_options_idx ON orders USING gin (options);

SELECT id, amount, options->>'delivery' AS delivery
FROM orders WHERE options @> '{"delivery":"express"}'::jsonb;

SELECT customer_id, count(*) AS paid_orders, sum(amount) AS paid_amount
FROM orders
WHERE status='paid'
  AND created_at >= timestamptz '2026-10-01 00:00:00+08'
  AND created_at < timestamptz '2026-11-01 00:00:00+08'
GROUP BY customer_id HAVING sum(amount)>60;

WITH ranked AS (
  SELECT id, customer_id, amount,
         row_number() OVER (
           PARTITION BY customer_id ORDER BY created_at DESC, id DESC
         ) AS rn
  FROM orders
)
SELECT id, customer_id, amount FROM ranked WHERE rn=1 ORDER BY customer_id;

第一项为 101/50.00/express;支付汇总为客户 1、两单、89.90 元;最新订单为 103、104,最后一项没有限制支付状态。窗口函数保留行,GROUP BY 收缩为分组结果,不能用同一种“去重”解释。时间范围明确使用业务时区的半开边界,避免漏掉月末最后一天。

复合 B-tree 服务客户时间线,部分索引只收 pending 行,GIN 服务 JSONB 包含查询。查询 options->>'delivery'='express' 不会因此自动得到同一条 GIN 访问路径;高频标量比较可考虑匹配的表达式索引。部分索引要求优化器能证明查询条件蕴含索引谓词,参数化通用计划未必能证明 status=$1 等于 pending。索引也有写放大与空间成本,不是每种类型都建一个。

sql
ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount FROM orders
WHERE customer_id=1
ORDER BY created_at DESC, id DESC LIMIT 10;

四行样本可能选择顺序扫描,这是合理成本选择,不能据此宣称索引无效或强制关掉顺序扫描。先比较估计/实际行数、循环次数、扫描与过滤量、排序溢出、缓冲区及 I/O,再考虑统计或索引。EXPLAIN ANALYZE 会实际执行语句;不要直接对生产写操作试跑。即便放入事务后回滚,也未必撤销序列和外部函数副作用。

应用通过驱动参数绑定数据值,动态表名、列名与排序方向走白名单;不能把 PostgreSQL 驱动占位符与其他驱动写法混用。连接池限制并发,并在归还连接前结束事务、恢复会话设置。引入 PgBouncer 的事务池时,应核对所用版本、驱动预处理、临时表及会话状态的兼容性。

MVCC 为什么仍然需要维护 ​

更新不是把所有读者眼中的一行同时覆盖。不同快照可以看见不同版本,旧版本在仍有事务需要时不能回收。默认 READ COMMITTED 下,普通查询每条语句取得新快照;REPEATABLE READ 可让事务中的多次读取保持一致视图,但更容易长期保留旧版本。

两个会话读取订单、更新提交、旧快照保留及VACUUM回收的时间关系

打开两个独立的 psql 会话。先在 A 执行并保持事务:

sql
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT amount FROM orders WHERE id=101;

读到 50.00。再在 B 执行:

sql
UPDATE orders SET amount=55.00 WHERE id=101;
VACUUM (ANALYZE) orders;

回到 A 完成观察:

sql
SELECT amount FROM orders WHERE id=101;
COMMIT;
SELECT amount FROM orders WHERE id=101;

提交前仍读到 50.00,提交后新语句读到 55.00。在 B 中把金额改回 50.00 并再次 VACUUM,再继续其他练习。这个例子验证快照可见性,不能仅靠它断言磁盘已经缩小了多少。

普通 VACUUM 回收可重用空间、维护可见性信息并处理冻结,通常不会把整张表文件立即缩回操作系统;autovacuum 是持续维护机制,不能长期关闭。n_dead_tup 是估计值,不等于精确膨胀率。长事务、复制槽、备库反馈等可能延迟版本或 WAL 清理,应分别检查事务与复制保留边界。VACUUM FULL 重写表、需要额外空间并取得强锁,不应设成每天无条件执行的清理任务。

全文、递归与分区的适用边界 ​

商品说明的英文检索可以用内置全文搜索。搜索向量用生成列维护,避免只 UPDATE 一次后新写入的数据无法被搜到:

sql
CREATE TABLE product_docs (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  content text,
  search_vector tsvector GENERATED ALWAYS AS (
    to_tsvector('english'::regconfig,
                coalesce(title,'') || ' ' || coalesce(content,''))
  ) STORED
);
CREATE INDEX product_docs_search_idx ON product_docs USING gin(search_vector);
INSERT INTO product_docs (title,content) VALUES
  ('PostgreSQL guide','Database transactions and indexes'),
  ('Delivery guide',NULL);
SELECT id,title FROM product_docs
WHERE search_vector @@ plainto_tsquery('english','database transactions');
UPDATE product_docs SET content='Database transactions for delivery' WHERE id=2;
SELECT id FROM product_docs
WHERE search_vector @@ plainto_tsquery('english','database transactions')
ORDER BY id;

先返回 1,更新后返回 1、2。固定 regconfig 也让生成表达式语义明确;内置 english 配置不是中文分词器。中文搜索需要核对分词扩展、字典、部署支持和检索质量,不能只换成 simple 就宣称分词问题已解决。

层级查询要处理路径类型和环路。以下从指定员工向直属下级展开,以编号数组阻止回访,名称显式转成 text,避免递归项与起始项类型不一致:

sql
CREATE TABLE employees (
  id int PRIMARY KEY,
  name varchar(100) NOT NULL,
  manager_id int REFERENCES employees(id)
);
INSERT INTO employees VALUES (1,'负责人',NULL),(2,'销售主管',1),(3,'店员',2);
WITH RECURSIVE org AS (
  SELECT id,name,manager_id,1 AS depth,name::text AS path,ARRAY[id] AS seen
  FROM employees WHERE id=1
  UNION ALL
  SELECT e.id,e.name,e.manager_id,o.depth+1,
         o.path || ' / ' || e.name,o.seen || e.id
  FROM employees e JOIN org o ON e.manager_id=o.id
  WHERE NOT e.id=ANY(o.seen)
)
SELECT id,depth,path FROM org ORDER BY depth,id;

外键只保证上级存在,不能阻止组织成环。这里从指定节点可安全终止,但“跳过回访节点”不等于已经修好组织数据,应用仍需校验父子变更。窗口排序、递归路径与后续页面分页都要显式定义稳定顺序。

订单审计事件按月清理时可以考虑分区。分区裁剪依赖查询条件和边界,不是创建分区后每个查询都更快:

sql
CREATE TABLE order_events (
  occurred_at timestamptz NOT NULL,
  event_id bigint NOT NULL,
  order_id bigint NOT NULL,
  PRIMARY KEY (occurred_at,event_id)
) PARTITION BY RANGE (occurred_at);
CREATE TABLE order_events_202610 PARTITION OF order_events
  FOR VALUES FROM ('2026-10-01 00:00:00+08') TO ('2026-11-01 00:00:00+08');
INSERT INTO order_events VALUES ('2026-10-03 11:00:00+08',1,103);

上界不包含;未覆盖日期的写入会失败,需提前创建后续分区或明确 DEFAULT 分区的处置。分区表的主键/唯一约束必须包含全部分区键,示例的 event_id 本身不是全局唯一。分区删除、分离和归档仍需评估锁、外键及恢复流程。

PostgreSQL 内置 POINT 等是平面几何类型,经纬度直接 <-> 得到的值不能当米或公里。地理坐标、投影与空间查询应使用正确安装的 PostGIS,并明确 geometry/geography、SRID 和距离单位。

从慢请求定位到阻塞者 ​

sql
SELECT pid,usename,application_name,state,
       now()-xact_start AS transaction_age,
       wait_event_type,wait_event,pg_blocking_pids(pid) AS blockers,query
FROM pg_stat_activity
WHERE datname=current_database() AND pid<>pg_backend_pid()
ORDER BY xact_start NULLS LAST;

SELECT schemaname,relname,n_live_tup,n_dead_tup,
       last_autovacuum,last_autoanalyze,pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables ORDER BY relname;
SELECT schemaname,relname,indexrelname,idx_scan,idx_tup_read,idx_tup_fetch
FROM pg_stat_user_indexes ORDER BY relname,indexrelname;

pg_stat_user_tables 用 relname,pg_stat_user_indexes 用 indexrelname;需要对象大小时用 OID relid,避免跨 schema 同名表误关联。统计值多为累计量或估计值,有重置和异步更新边界;单次零扫描不构成删索引依据。部分其他用户活动字段需要相应监控权限,不应为读监控直接给应用超级用户。

慢请求先看业务分位延迟和错误,再看当前活动、等待事件、阻塞者及执行计划。idle in transaction 会话可能持锁或保留快照;不要只筛 active,更不要批量杀掉所有空闲连接。先找事务所有者,优先让应用回滚;pg_cancel_backend 取消当前语句,pg_terminate_backend 断开会话,两者对事务与重试的影响不同。

长期 SQL 聚合可使用 pg_stat_statements,但必须在支持该扩展的实例上配置 shared_preload_libraries 并重启,再在目标库 CREATE EXTENSION;单独执行 CREATE 不会完成采集链路。查询文本可能含业务信息,日志与监控要有访问、保留和容量策略。不要默认 log_statement='all',先用阈值慢日志并控制采样。

备份要以恢复结果验收 ​

先练习逻辑备份和恢复到新库,以下命令在宿主机 Bash 执行,不在 psql 内执行:

bash
umask 077
docker exec pg-lab pg_dump -U postgres -d orders_demo -Fc > orders-demo.dump
docker exec pg-lab createdb -U postgres orders_restore
docker exec -i pg-lab pg_restore -U postgres -d orders_restore \
  --exit-on-error --no-owner --no-privileges < orders-demo.dump
docker exec pg-lab psql -X -v ON_ERROR_STOP=1 -U postgres -d orders_restore \
  -c 'SELECT count(*), sum(amount) FROM orders;'

恢复应得到四单、139.90 元,前提是已按 MVCC 练习说明把 101 改回 50.00。逐条检查命令退出码,dump 失败产生的文件不能继续当备份;恢复的新库已存在时先核对归属,不直接清空重试。--no-owner/--no-privileges 只便于独立练习,生产要另行恢复并核对所有者、角色、授权、扩展和应用连接。逻辑 dump 默认不包含集群全局角色;SQL 文本恢复用 psql -X -v ON_ERROR_STOP=1,防止出错后仍继续造成“似乎成功”。

物理恢复则是基础备份 + 连续 WAL + 时间线与目标。只有 WAL 文件或只有一份基准备份,都不能随意恢复到任意时间。pg_basebackup 需要专用复制账号、允许的来源、空目标目录和足够 WAL 保留;归档脚本要防覆盖、正确报告失败并验证完整性,不用无条件 cp 当可靠归档。

流复制备库用 pg_basebackup -R 等方式建立一致基础并生成相应恢复配置,凭据通过受限文件管理;复制槽能保留所需 WAL,也可能在消费者落后时撑满磁盘。监控主库 pg_stat_replication 的发送/写入/刷盘/回放位置,备库看 pg_is_in_recovery()、pg_last_wal_replay_lsn();空闲时的 replay timestamp 年龄不应直接当业务延迟。

故障切换先隔离旧主写入,再确认候选数据进度和可接受丢失范围;在仍是备库的候选上用 SQL SELECT pg_promote(); 或操作系统命令 pg_ctl promote -D ...,随后核对角色、写入口及业务幂等。没有名为 pg_promote -D 的标准命令,删除 standby.signal 也不是在线自动切换流程。旧主不能直接带着旧写入口归队,应按分叉和时间线选择 pg_rewind 或重建;这些步骤需要独立高可用演练,单机练习不证明故障转移已通过。

验收与排错 ​

本页的达成标志是:能解释 89.90 元汇总和 JSONB 条件对应的索引,复现 A/B 快照差异,验证新增/更新说明会进入全文搜索,说明分区漏建的写入后果,并把备份恢复到新库核对数据与权限。

连接失败沿“地址与端口 → 监听与网络 → HBA 首条匹配 → 认证/TLS → 数据库权限”检查;SQL 报错先核对版本、schema 和实际定义。事务内语句报错后会进入失败状态,通常需 ROLLBACK,或回滚到错误前建立的保存点后继续。性能问题先区分阻塞、访问量和 I/O,空间问题再区分数据、索引、旧版本与 WAL 保留;不能用加连接、加 work_mem、VACUUM FULL 或主备切换作为统一修复按钮。

别急,先让缓存热一下。