我们部署在香港机房的电商网站，从其业务监控屏上，订单同步的 95 线延迟飙到 900ms，库存扣减队列堆到 12 万条。业务在珠三角、长三角、成渝三线同时打点，跨境链路穿过内地—香港的网络边界，零点大促刚过，秒杀流量却像潮水回灌。CFO 在电话那头只问了一句：“还能稳住吗？”

我蹲在 42U 机柜前，把两台 NVMe 条子重新热插拔做了校验，心里很清楚：数据库已经顶到单机极限，继续加 CPU 没意义。得分片，而且得把最痛的慢 SQL 一口气掐死。这篇就是那一夜我在香港机房上，用 CentOS 7 + PostgreSQL 做分片与索引优化、从系统到数据库全链路落地的完整记录。

一、现场环境与目标

1.1 硬件与网络（香港机房，BGP/CN2 GIA 线路）

1.2 业务画像与瓶颈

多租户（tenant_id），订单写入尖峰高、库存扣减强一致要求高。

热点 SQL：

• 最近订单列表：WHERE tenant_id = ? AND order_time >= ? ORDER BY order_time DESC LIMIT 50
• 库存变更：UPDATE stock SET qty = qty - ? WHERE tenant_id=? AND sku=?
• 模糊搜索：LIKE '%keyword%' 命中 sku/customer_name
• 痛点：单实例索引膨胀 + 锁争用 + I/O 抖动，跨境延迟带来连接数堆积。

1.3 改造目标（SLO）

二、操作系统层：CentOS 7（保持可复现）

注意：CentOS 7 已 EOL，但本案为历史环境复盘与可复现教程。

2.1 BIOS/内核/文件系统

关闭 BIOS C-States（低延迟优先）。

NVMe 调度器使用 none（NVMe 默认如此）。

使用 XFS，挂载 noatime，定期 fstrim.timer。

# XFS + noatime
UUID=<YOUR-UUID> /var/lib/pgsql xfs defaults,noatime 0 0
systemctl enable fstrim.timer --now

2.2 tuned + sysctl + THP
yum install -y tuned
tuned-adm profile throughput-performance
cat >>/etc/sysctl.d/99-pg.conf <<'EOF'
vm.swappiness=1
vm.dirty_background_ratio=5
vm.dirty_ratio=20
vm.overcommit_memory=2
fs.file-max=1000000
net.core.somaxconn=1024
kernel.numa_balancing=0
EOF
sysctl -p /etc/sysctl.d/99-pg.conf

# 关闭 Transparent Huge Pages
echo 'never' > /sys/kernel/mm/transparent_hugepage/enabled
echo 'never' > /sys/kernel/mm/transparent_hugepage/defrag
echo 'echo never > /sys/kernel/mm/transparent_hugepage/enabled' >> /etc/rc.d/rc.local
chmod +x /etc/rc.d/rc.local

# limits
cat >>/etc/security/limits.d/postgres.conf <<'EOF'
postgres soft nofile 100000
postgres hard nofile 100000
EOF

三、安装 PostgreSQL 14（PGDG），开启关键扩展

# PGDG 仓库
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y postgresql14-server postgresql14-contrib
/usr/pgsql-14/bin/postgresql-14-setup initdb
systemctl enable --now postgresql-14

3.1 基础配置（以 256GB RAM 为例）

/var/lib/pgsql/14/data/postgresql.conf：

listen_addresses = '*'
max_connections = 200           # pgbouncer 前置，DB 端尽量小
shared_buffers = 64GB           # ~25%
effective_cache_size = 180GB    # ~70%
work_mem = 64MB                 # 小心连接数放大
maintenance_work_mem = 2GB
wal_level = replica
max_wal_size = 8GB
min_wal_size = 2GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
random_page_cost = 1.1
effective_io_concurrency = 200
autovacuum_naptime = 10s
autovacuum_vacuum_cost_limit = 2000
autovacuum_work_mem = 1GB
log_min_duration_statement = 500ms
log_checkpoints = on
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
password_encryption = scram-sha-256

pg_hba.conf（仅示意）：

hostssl all all 10.0.0.0/16 scram-sha-256

启用扩展：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS btree_gin;

四、连接池：pgbouncer（事务级）

yum install -y epel-release pgbouncer
cat >/etc/pgbouncer/pgbouncer.ini <<'EOF'
[databases]
erp = host=127.0.0.1 port=5432 dbname=erp auth_user=pgbouncer

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 3000
default_pool_size = 100
server_reset_query = DISCARD ALL
ignore_startup_parameters = extra_float_digits
EOF

# 用户
cat >/etc/pgbouncer/userlist.txt <<'EOF'
"pgbouncer" "SCRAM-SHA-256$<hash>"
"erpapp" "SCRAM-SHA-256$<hash>"
EOF

systemctl enable --now pgbouncer

坑 1：部分 ORM 开启 server-side prepared statements，搭配 pool_mode=transaction 可能重用失败，建议对 ORM 关闭或在连接串上禁用 prepare。

五、索引优化：先把最疼的慢 SQL 止血

5.1 捕获热点

-- 热点 SQL 摘要
SELECT calls, mean_exec_time, query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

5.2 针对场景的索引策略

最近订单列表（覆盖 + 顺序一致）

SQL 模式：

SELECT id, status, amount
FROM orders
WHERE tenant_id = $1 AND order_time >= now() - interval '7 days'
ORDER BY order_time DESC
LIMIT 50;

索引（在数据节点上）：

-- 注意：DESC 顺序一致以避免排序回表
CREATE INDEX CONCURRENTLY idx_orders_tenant_time_desc
  ON orders (tenant_id, order_time DESC)
  INCLUDE (status, amount);

库存更新（点查 + 行级锁尽量短）

-- 确保 tenant_id + sku 唯一
ALTER TABLE stock ADD CONSTRAINT uk_stock_tenant_sku UNIQUE (tenant_id, sku);

-- 定位索引
CREATE INDEX CONCURRENTLY idx_stock_tenant_sku ON stock (tenant_id, sku);

模糊搜索（SKU/姓名）

CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- GIN + trigram，加速 LIKE '%xxx%'
CREATE INDEX CONCURRENTLY idx_orders_sku_trgm ON orders USING gin (sku gin_trgm_ops);
CREATE INDEX CONCURRENTLY idx_orders_cust_trgm ON orders USING gin (customer_name gin_trgm_ops);

-- 对前缀匹配较多的列，可加 pattern_ops
CREATE INDEX CONCURRENTLY idx_orders_sku_prefix ON orders (sku varchar_pattern_ops);

效果对比（EXPLAIN 片段，简化）：

坑 2：多列组合索引要与 WHERE/ORDER BY 的实际顺序一致；INCLUDE 覆盖列减少回表带来的随机 I/O。

六、分片方案：PostgreSQL 原生分区 + FDW 外部分区

目标：按 tenant_id 做哈希分片，COORD 作为“协调入口”，SHARD-0/1 作为数据节点。
方案优点：无侵入 SQL（分区路由自动），易扩展（新增分片即可）。
要求：PostgreSQL ≥ 11（支持将外部表挂为分区）。

6.1 数据节点（SHARD-0/1）建库

-- 每个数据节点执行
CREATE DATABASE erp;
\c erp

-- 订单表（与现有单表结构一致）
CREATE TABLE orders (
  id bigint PRIMARY KEY,
  tenant_id int NOT NULL,
  order_time timestamptz NOT NULL DEFAULT now(),
  status smallint NOT NULL DEFAULT 0,
  amount numeric(12,2) NOT NULL,
  sku text,
  customer_name text
);

-- 该节点的 shard_id 固定函数
CREATE OR REPLACE FUNCTION shard_id() RETURNS smallint LANGUAGE sql STABLE AS $$ SELECT 0; $$; -- 在 SHARD-1 改成 SELECT 1

-- 雪花式简化：shard_id 前缀 + 序列
CREATE SEQUENCE order_id_seq START 1 INCREMENT 1;
ALTER TABLE orders ALTER COLUMN id SET DEFAULT (shard_id()::bigint*1000000000000 + nextval('order_id_seq'));

-- 必要索引（与上节一致）
CREATE INDEX idx_orders_tenant_time_desc ON orders (tenant_id, order_time DESC) INCLUDE (status, amount);
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_orders_sku_trgm ON orders USING gin (sku gin_trgm_ops);

坑 3（ID 冲突）：分片前请设计 全局唯一 ID。这里用 “shard_id × 10^12 + 序列”。迁移时注意旧数据 ID。

6.2 协调节点（COORD）配置 FDW 与分区

CREATE DATABASE erp;
\c erp
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- 连接到两个数据节点
CREATE SERVER shard0 FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host '10.0.0.11', port '5432', dbname 'erp');
CREATE SERVER shard1 FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host '10.0.0.12', port '5432', dbname 'erp');

CREATE USER MAPPING FOR erpapp SERVER shard0 OPTIONS (user 'erp', password '<pwd>');
CREATE USER MAPPING FOR erpapp SERVER shard1 OPTIONS (user 'erp', password '<pwd>');

-- 分区母表
CREATE TABLE orders (
  id bigint PRIMARY KEY,
  tenant_id int NOT NULL,
  order_time timestamptz NOT NULL DEFAULT now(),
  status smallint NOT NULL DEFAULT 0,
  amount numeric(12,2) NOT NULL,
  sku text,
  customer_name text
) PARTITION BY HASH (tenant_id);

-- 外部分区（映射到各自数据节点实际表）
CREATE FOREIGN TABLE orders_s0 (
  id bigint,
  tenant_id int,
  order_time timestamptz,
  status smallint,
  amount numeric(12,2),
  sku text,
  customer_name text
) SERVER shard0 OPTIONS (schema_name 'public', table_name 'orders');

CREATE FOREIGN TABLE orders_s1 (...) SERVER shard1 OPTIONS (schema_name 'public', table_name 'orders');

-- 挂载为哈希分区
ALTER TABLE orders ATTACH PARTITION orders_s0 FOR VALUES WITH (MODULUS 2, REMAINDER 0);
ALTER TABLE orders ATTACH PARTITION orders_s1 FOR VALUES WITH (MODULUS 2, REMAINDER 1);

插入路由：应用只需 INSERT INTO orders(...) VALUES(...)，协调节点按 tenant_id 哈希自动路由到对应 SHARD。
索引位置：必须在数据节点建索引；协调节点的外部分区不需要本地索引。

6.3 扩容（新增分片）思路

新增 SHARD-2，创建同结构表与索引，作为空分片。

创建新外部分区 orders_s2，改为 MODULUS 4, REMAINDER 2 等；在线 Rehash 可通过：

双写（应用层）+ 后台 rehash 迁移；或

维护 “路由表（tenant_id -> shard）”，过渡期路由不必严格哈希；

迁移用 INSERT ... SELECT + 逻辑复制，分租户批次切换。

七、零停机迁移（单表 -> 分片表）

读写分离窗口：先把非关键读走只读库，写压保持。

创建分片母表与分区（如上）。

逻辑复制（PG 原生 PUBLICATION/SUBSCRIPTION）：

-- 旧单表库
CREATE PUBLICATION pub_orders FOR TABLE orders;

-- COORD
CREATE SUBSCRIPTION sub_orders
  CONNECTION 'host=OLD_DB port=5432 dbname=erp user=replicator password=***'
  PUBLICATION pub_orders WITH (copy_data = true);

按 tenant_id 批次切换写入：应用路由到新库（COORD），对已迁完租户强制在新表写；

一致性校验：每批校验行数与校验和；

切换完成：撤销旧库写入，清理订阅。

坑 4（排序/Collation 差异）：请确保各节点 DB 的 lc_collate 与 lc_ctype 一致；否则 ORDER BY 在不同分片上可能顺序差异，汇总排序出错。统一 en_US.UTF-8 或一致的 zh_CN.UTF-8。

八、写放大与 VACUUM：别让膨胀吞掉 NVMe

大表（orders/stock）设置表级参数（在数据节点）：

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_vacuum_threshold = 50000);
ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.02);
ALTER TABLE orders SET (toast.autovacuum_vacuum_scale_factor = 0.03);

周期性 REINDEX CONCURRENTLY 针对重写成本高的 GIN：

REINDEX INDEX CONCURRENTLY idx_orders_sku_trgm;

Checkpoint 调优已在 postgresql.conf 体现；观察 pg_stat_bgwriter，保证 checkpoint 时间占比平滑。

九、压测与结果

9.1 压测脚本（简化）

# 自定义混合事务：50% 查询最近订单，30% 下单，20% 库存更新
# 可用 pgbench -f mix.sql -j 16 -c 128 -T 600

9.2 结果摘要

十、运维要点与故障备忘

• 连接池与预编译：部分驱动（如 PgJDBC）默认 prepared statements，pool_mode=transaction 下建议禁用或调大 max_prepared_statements 并谨慎使用。
• WAL 压力：大促期间 max_wal_size 适度加大（如 16–32GB），并启用 wal_compression = on（PG14 默认支持 LZ4/PLAIN）。
• 备份：推荐 pgBackRest；不要在峰值做全量备份，走增量 + WAL 归档。
• 跨片事务：FDW 非分布式两段提交，涉及多分片强一致要求的业务，要么路由到同片、要么在应用层设计幂等/补偿。
• 监控：pg_stat_statements + auto_explain（仅在 COORD 开启，慎控开销）；I/O 层 iostat -x 1，观测 NVMe QD 与 await。
• Collation 一致性：前文强调再强调。

十一、完整配置片段（可直接复用）

11.1 auto_explain（仅 COORD，避免全局开销）

shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = '300ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_nested_statements = off

11.2 psql 连接串（通过 pgbouncer）

postgresql://erpapp:***@coord-vip:6432/erp?sslmode=require

十二、收尾：3 点 20 分，报警恢复

回酒店的时候是 3:20。地铁口的 7-11 还亮着，我买了瓶水，脑子里回放了一遍：先索引止血，再分片疏洪，连接池把高并发“压平”，VACUUM 把写放大“磨平”。CFO 后来发来一条消息：“库存损益今天小于 0.03% 了。”
那一刻我知道：这套在香港机房里跑起来的 CentOS 7 + PostgreSQL 分片架构，值了。下一步，我们会把分片数从 2 拉到 4，在深圳边界再加一组只读，继续把链路时延往下拧。

附：一步到位的部署清单（Checklist）

•  CentOS 7 tuned、sysctl、THP 关闭、XFS noatime
•  PGDG 安装 PostgreSQL 14，开启 pg_stat_statements/pg_trgm
•  pgbouncer 事务级池化，连接数降至 200 内
•  针对热点 SQL 的顺序一致与覆盖索引
•  FDW 外部分区 + 哈希分片（按 tenant_id）
•  雪花式 ID（shard_id 前缀）避免跨片冲突
•  autovacuum 表级参数 + 周期性 REINDEX
•  逻辑复制做零停机迁移，按租户分批切换
•  监控与备份策略在峰值期间做“降冲击”配置

如果你也在香港机房为跨境 ERP 扛压，照着这篇把“索引先止血、分片再疏洪”的顺序走一遍，先把单点拧到极致，再把水平分出去。当报警静下来、库存对上数的那一刻，你会知道一切都值得。