1. 把业务核心全压到 PG 上之前我做过哪些推演“问鼎PG天下”这个名字是内部立项评审时的一句随口话后来一直没改。当时我正被三套异构存储来回同步的事情折腾得够呛一急就说干脆把核心交易、日志明细、报表聚合全收进 PostgreSQL 一套引擎里不再把数据分散到别的系统。名字听着狂做的事其实很土让一套日增两三千万行、峰值写入五六千 TPS、单表最大几百 GB 的业务完整、稳定地跑在 PG 上还指望它撑三年不用整体重做。这篇内容写给两类人一类是正在纠结要不要把 MySQL 或商业库迁到 PostgreSQL 的后端同学另一类是已经上了 PG、正被慢查询、表膨胀、锁等待、连接数打满折磨的运维和 DBA。我不复述文档只讲这个项目里做过的取舍、算过的参数、踩过的坑以及那些文档不写但线上一定会遇到的东西。1.1 需求拆解原来的架构为什么让人睡不着原来的架构是典型的三件套关系库扛交易搜索引擎扛列表页和模糊查询列式库扛报表。单看每一块都挺合理凑在一起就是一场灾难。第一是一致性交易写进关系库之后要靠异步管道推到搜索引擎中间那几百毫秒的窗口里用户下单成功刷新列表却看不到自己的订单客服每天都要解释几十遍第二是口径报表库和交易库各自算一遍 GMV小数点后两位经常对不上财务月底对账要拉三个人对两天第三是运维成本三套系统三套备份三套监控三套故障预案任何一次大促前的容量评估都要做三遍人力是实打实的三倍。真正的转折点是那次夜间批量任务。报表侧的聚合任务跑起来之后把磁盘 IO 打满交易库的写入延迟从 3ms 涨到 200ms连续三次触发告警。事后复盘发现问题的根子不在任何一个单点上而在于“同一份事实被存了三份还被三种引擎用三种方式解释”。那时候我就定了个方向把事实收敛到一处其余都是它的投影。PG 之所以成为那个“一处”不是因为它在任何一个单项上最强而是它在事务、JSON、全文检索、窗口函数、地理类型、分区、逻辑复制这几件事上都不弱能让我用一个引擎覆盖掉原来三个引擎的八成场景。1.2 三套方案摆在桌上我算了一笔什么账选型那两周我把三条路都推演了一遍最后做成一张表摆在评审会上。这里要说明的是表里的成本是按我们团队六个后端加两个运维的规模估的不同团队数字会差很多但比较的维度是通用的。维度方案A维持三件套方案B全量收敛到 PG方案CPG 为主 列存做报表投影开发改造成本几乎为零高查询层要重写约 40%中只改报表链路数据一致性最终一致存在窗口强一致单事务内完成强一致 报表秒级延迟运维复杂度三套故障面最大一套备份恢复链路统一两套但报表侧可降级单表规模上限关系库先顶不住分区 归档能撑到 TB 级同左复杂分析能力靠列存库窗口函数 并行查询够用更强三年人力投入三人常驻两人常驻两点五人常驻方案 C 看起来最稳但我最后没选理由很实在只要还留一套独立的报表库口径不一致的问题就永远治不干净而口径问题恰恰是当初逼我动手的原始动机。方案 B 的风险集中在“PG 能不能扛住那几张超大表的分析查询”这个风险可以用分区裁剪、覆盖索引、物化视图和只读从库分担来化解。所以我最终选的是 B但给自己留了一个后门报表类查询全部走专用的只读从库并且把最重的几张宽表预先做成物化视图定时刷新。真到扛不住那天再把报表链路摘出去做方案 C改动面被刻意限制在视图层业务代码不用动。这里补一句我个人的判断标准如果你的复杂查询占比低于两成全量收敛到 PG 的收益远大于风险如果超过四成老老实实保留一条分析链路别硬扛。硬扛的结果通常是交易库被分析查询拖死然后你被迫在半夜做一次紧急拆分。1.3 定架构一主两从不是抄来的是算出来的最终落地的形态是一主两从。主库只接受写和“写完立刻要读”的请求从库一给在线读、一给后台任务和报表。这个划分听着简单落地时有两个细节必须提前想清楚否则上线第一周就会出事。第一个细节是读写分离的粘主策略。无脑把读打到从库会立刻遇到“用户改完昵称刷新页面还是旧的”这类问题。我的做法是在会话层打标记任何写操作之后的 2 秒内这个会话的读请求强制回主库超过 2 秒再走从库。2 秒这个数字不是拍脑袋我们实测从库回放延迟的 P99 在 300ms 上下取 2 秒相当于留了六倍余量同时把回主库的读比例压在 5% 以内主库完全扛得住。第二个细节是从库的角色不能混用。在线读从库要求低延迟、高并发、连接数多后台报表从库要求长事务、大排序、能吃满 CPU。这两类负载挤在同一个实例上结果一定是报表把在线读拖垮。把它们分开之后我甚至给报表从库单独放宽了work_mem和maintenance_work_mem让它慢一点没关系、别影响别人就行。2. PG 的几块硬骨头核心机制拆解与实操要点PG 用起来上手极快装完就能跑但真正决定它能撑多大的是四个机制MVCC、WAL、索引、分区。这四个东西不理解透你会在半年内遇到“磁盘莫名涨满”“写入抖动”“索引建了不用”“分区反而更慢”这一整套问题。我按踩坑的先后顺序讲。2.1 MVCC为什么删了一亿行磁盘一点没瘦刚上线三个月有一张日志表被清理任务删掉了将近一亿行结果磁盘占用纹丝不动运维直接来找我。这不是 bug是 MVCC 的必然结果。PG 的更新和删除都不是原地修改而是给旧版本打上“已失效”的标记新版本另起一行。那些已经没人看得见的旧版本叫死元组它们占的空间只有等 vacuum 扫过、确认没有任何事务还需要看到它们之后才会被标记为可复用。关键在于“可复用”和“还给操作系统”是两回事。普通的 vacuum 只是把空间标成空闲留给后续的插入复用文件本身不会缩小。想让磁盘真的降下来只有两条路一是VACUUM FULL它会重写整张表并持有排他锁几百 GB 的表在业务时段做这个基本等于自杀二是pg_repack这类在线重建工具靠触发器和逻辑复制做双写能在几乎不停机的情况下把表重写一遍。真正治本的是别让死元组堆积。我们做了三件事对更新极其频繁的订单状态表把fillfactor调到 75预留出页面内的空闲空间让更新后的新版本尽量落在同一个页面里这就是 HOT 更新可以完全不碰索引对大表的 autovacuum 单独调参把autovacuum_vacuum_scale_factor从默认的 0.2 降到 0.02触发阈值从“20% 的行变了”变成“2% 的行变了”加了一条监控每小时统计一次每张表的死元组占比超过 15% 就告警。-- 找出死元组占比最高的表按需人工 vacuum SELECT relname, n_live_tup, n_dead_tup, round(n_dead_tup::numeric / greatest(n_live_tup n_dead_tup, 1) * 100, 2) AS dead_pct, last_autovacuum FROM pg_stat_user_tables WHERE n_dead_tup 10000 ORDER BY dead_pct DESC LIMIT 20;这里有个反直觉的点长事务是表膨胀的真正推手。只要有一个事务开了快照却迟迟不提交vacuum 就不敢回收任何比它更晚的死元组。我们曾经有一个报表连接忘了关自动提交挂了一整夜第二天三张核心表全胖了一圈。后来强制给所有连接设了idle_in_transaction_session_timeout 300s这类事故再没出现过。2.2 WAL 与 checkpoint写入吞吐的真正闸门很多人以为 PG 写慢是磁盘不行其实大部分时候瓶颈在 checkpoint。每一次数据页的修改PG 都要先把变更写进 WAL 日志这是顺序写很快但 checkpoint 要把内存里所有脏页刷到磁盘这是随机写很慢。更麻烦的是full_page_writes为了防止断电导致的页面断裂每次 checkpoint 之后的第一次页面修改PG 会把整个 8KB 页面原样写进 WAL。这意味着 checkpoint 越频繁WAL 里写的冗余数据就越多写放大越严重。我最初用了默认配置max_wal_size只有 1GB结果在高峰写入时 checkpoint 每两三分钟就触发一次日志里全是 “checkpoints are occurring too frequently” 的提示写入延迟呈现规律的锯齿状。调参思路很直接把 checkpoint 的间隔拉长让每次刷脏更平缓。参数默认值我们的取值调整理由max_wal_size1GB16GB减少 checkpoint 触发频次削掉写入锯齿min_wal_size80MB4GB避免 WAL 文件频繁创建删除checkpoint_timeout5min20min与 max_wal_size 配合两者谁先到谁触发checkpoint_completion_target0.50.9把刷脏摊到整个周期的 90%削峰wal_compressionoffon压缩 full page writes典型可省 30% 到 50% WAL 量wal_buffers-1自动64MB高并发小事务下减少 WAL 写系统调用次数调完之后最直观的变化是写入延迟的 P99 从 180ms 降到 27ms锯齿没了。这里要注意一个副作用max_wal_size拉大意味着崩溃恢复时要回放的 WAL 更多恢复时间会变长。16GB 的 WAL 在普通 SSD 上回放大概需要一到两分钟这个量级我们能接受。如果你的业务对恢复时间极其敏感那就得在“写入平稳”和“恢复快”之间重新找平衡点。2.3 索引选型建对索引比建多索引值钱得多我接手这套库的时候有一张表上一共 11 个索引实际被用到的只有 3 个。多出来的索引不只是浪费磁盘更要命的是每次写入都要维护它们写入性能被硬生生削掉三成。判断该建什么索引先看查询模式的形状索引类型适用查询形状我们的实际用例注意事项B-tree等值、范围、排序、前缀匹配订单号、用户 ID、时间范围最通用但要注意字段顺序把选择性高的放前面GIN包含、重叠、全文检索、JSONB 键值商品标签数组、全文搜索、扩展字段查询写入慢、体积大别滥用fastupdate可调BRIN超大表的顺序相关字段按时间顺序写入的日志表时间列体积极小但要求物理顺序与值强相关否则几乎无效GiST几何、范围类型、近邻检索配送范围判断需要装对应扩展Hash纯等值查询极少使用不支持范围多数场景不如 B-tree举个具体的例子。日志表原来在created_at上建了 B-tree 索引一张表 400GB索引本身就 9GB。后来改成 BRIN索引缩到 200KB 出头查询性能反而没降因为这张表的数据是按时间顺序插入的物理顺序和值高度相关BRIN 的块级摘要足够精准。这就是典型的“选对类型直接把成本打下来一个数量级”。另一个实战经验是复合索引的字段顺序。我们有条查询是WHERE tenant_id ? AND status ? ORDER BY created_at DESC LIMIT 20。最优索引是(tenant_id, status, created_at DESC)三个字段一次命中排序都省了。如果顺序写错比如把status放前面那么查询虽然还能用上索引但要做一次过滤加排序大租户下的耗时差了将近二十倍。-- 查索引使用率找出从没被用过的“僵尸索引” SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS idx_size FROM pg_stat_user_indexes WHERE idx_scan 0 AND indexrelid NOT IN (SELECT conindid FROM pg_constraint WHERE conindid 0) ORDER BY pg_relation_size(indexrelid) DESC;提醒一句idx_scan 0不代表立刻就能删。统计是从上次重置开始算的可能刚好没赶上一个月度报表周期。我的习惯是观察满一个完整的业务周期比如包含月末结账确认确实是零使用再动手。2.4 分区表什么量级才值得上什么时候上了反而更慢分区这件事很容易走极端。有人几百兆的表就急着分区结果管理复杂度上去了性能没变有人几百 GB 还不分区导致 vacuum 跑不完、历史数据删不掉。我的判断标准是三条满足任意一条就该考虑分区单表超过 100GB 且还在增长存在明显的“按时间批量删除历史数据”的需求查询条件里天然带有时间或租户维度能靠分区裁剪砍掉大部分数据。分区键的选择比分区本身更重要。我们用得最多的是按时间做范围分区Range按月一个分区同时用DEFAULT分区兜底防止写入因为找不到匹配分区而报错。分区数量要严格控制经验值是一张表的分区总数别超过两三百个再多的话查询规划阶段本身就要花掉可观的时间。批量删除历史数据是分区带来的最大红利。没分区之前删三个月前的数据要跑一条DELETE跑几个小时、产生海量 WAL、还得配一次 vacuum分区之后一条DROP TABLE或者DETACH PARTITION秒级完成磁盘立刻释放。-- 按月分区 默认分区兜底 CREATE TABLE event_log ( id bigserial, tenant_id bigint NOT NULL, created_at timestamptz NOT NULL, payload jsonb ) PARTITION BY RANGE (created_at); CREATE TABLE event_log_2024_05 PARTITION OF event_log FOR VALUES FROM (2024-05-01) TO (2024-06-01); CREATE TABLE event_log_default PARTITION OF event_log DEFAULT; -- 归档时先摘下来再删两段式避免影响其他会话 ALTER TABLE event_log DETACH PARTITION event_log_2023_01; DROP TABLE event_log_2023_01;一个必须知道的坑PG 的分区表上不能建全局索引每个分区的索引是各自独立的。这意味着你在分区表上按非分区键查询优化器要把所有分区的索引都扫一遍。所以分区键一定要和最高频的查询条件对齐否则分区带来的收益会被索引扫描摊薄掉。3. 从裸机到生产一份可以照着抄的落地流程机制讲完说落地。这一节是我实际走了一遍的流程包含目录规划、参数计算过程、连接池、备份演练和上线压测。参数的具体数值是基于一台 64 核 128GB、NVMe SSD 的机器算的你换成别的规格按同样的逻辑重算就行。3.1 目录规划与安装把数据盘和 WAL 盘分开安装本身没什么难度难的是目录规划。我的原则是数据、WAL、备份三样东西物理上分开条件不允许至少要把 WAL 和数据分到不同盘上。原因很实在数据文件的读写是随机 IOWAL 是顺序写混在一块盘上它们会互相干扰尤其在做 checkpoint 的时候大量随机刷脏会把 WAL 的写入延迟带起来。目录结构我固定成这样/data/pgdata放数据/wal/pgwal放 WAL/backup/base放基础备份/backup/archive放归档日志。软件用系统的包管理器装或者自己编译都行我倾向于用官方仓库的稳定版本省得为了个小特性自己编译引入依赖问题。# 初始化数据目录WAL 单独放到另一块盘 /usr/pgsql-16/bin/initdb -D /data/pgdata -E UTF8 --localeC.UTF-8 -U pgadmin # 先把 WAL 目录建好并授权再改配置指向它 mkdir -p /wal/pgwal chown pgadmin:pgadmin /wal/pgwal psql -c ALTER SYSTEM SET wal_directory /wal/pgwal;--localeC.UTF-8这个选择我特意说一下。用 C 排序规则字符串比较是按字节走的索引构建和排序都会更快代价是中文按拼音排序这类需求得在应用层做。我们的业务里没有强依赖数据库排序规则的地方所以选了性能。如果你的业务里ORDER BY大量依赖中文语义排序那还是老老实实用zh_CN.UTF-8。另外从 PG 16 开始支持 ICU 排序规则可以做到“库级用 C某个字段用 ICU”这一点在混合场景下很值得用。3.2 内存参数把每一项的计算过程摊开内存参数是最容易被抄错的地方。网上到处是“shared_buffers 设成内存的 25%”但没人告诉你这个 25% 是相对什么而言的、剩下的 75% 都去哪儿了。我把我的推导过程完整写一遍。机器 128GB先扣掉操作系统和其他进程可用给 PG 的按 80GB 算shared_buffers取可用内存的 25%也就是 20GB。为什么不设更大因为 PG 依赖操作系统的页缓存做二级缓存两个缓存各管一摊shared_buffers太大反而会增加两者之间的同步开销。20GB 这个量级在我们这套读多写多的负载下实测最平衡。effective_cache_size这个参数不占内存它只是告诉优化器“整个系统大概有多少缓存可用”用来估算索引扫描的成本。设成可用内存的 60%48GB。设小了优化器会误以为随机 IO 很贵明明该走索引的查询它去走顺序扫描。work_mem这是最容易被误解的参数。它不是“每个连接的内存”而是“每个排序或哈希操作的内存”一条查询里有三个排序节点就会用三份。用常见公式算(可用内存 - shared_buffers) / (max_connections × 3) 60GB / (200 × 3) ≈ 100MB。这个数字看着很美但根本不能这么设因为一旦有几十个连接同时跑复杂查询内存瞬间打爆。我的做法是全局设 16MB 保底然后给报表类查询在会话级单独放开到 256MB。maintenance_work_mem给 vacuum 和建索引用设 2GB。这个值越大vacuum 跑得越快大表建索引也越快性价比很高。temp_buffers默认 8MB我们调到 64MB减少临时表反复申请。# postgresql.conf 关键内存项128GB 机器分配给 PG 约 80GB shared_buffers 20GB effective_cache_size 48GB work_mem 16MB maintenance_work_mem 2GB temp_buffers 64MB huge_pages tryhuge_pages try这一行值得单独提。开启大页之后PG 的页表项数量能降两个数量级TLB 命中率上去了高并发下的性能提升在 5% 到 10% 之间属于白捡的收益。设成try而不是on是为了避免系统没配好大页时数据库起不来。3.3 存储与并行相关的参数SSD 上必须改的两个默认值默认配置是十几年前为机械盘写的放到 NVMe SSD 上会严重误导优化器。最典型的是random_page_cost默认 4.0意思是随机读一次的成本是顺序读的四倍。这个比例在机械盘上成立在 SSD 上随机读和顺序读的成本差不多通常设成 1.1 左右。不改这个参数优化器会系统性地低估索引扫描的性价比大量本该走索引的查询被改成全表扫描。配套的还有effective_io_concurrency它控制同时能发起多少个异步 IO 请求机械盘设 2SSD 可以设到 200。另外并行查询的几个参数也按核数调一下64 核的机器上max_parallel_workers_per_gather设 4max_parallel_workers设 16别一次开太多并行 worker 抢 CPU 反而会让整体吞吐下降。random_page_cost 1.1 effective_io_concurrency 200 max_worker_processes 32 max_parallel_workers 16 max_parallel_workers_per_gather 4 max_parallel_maintenance_workers 4关于random_page_cost我给一个快速验证的方法改完之后拿几条典型的慢查询跑EXPLAIN (ANALYZE, BUFFERS)看执行计划有没有从 Seq Scan 翻成 Index Scan。如果翻了而且实际耗时下降说明改对了如果翻了但耗时反而变长说明统计信息不准先跑ANALYZE再看。3.4 连接池为什么 PG 必须加一层 PgBouncerPG 是每连接一个进程的模型每个连接背后是一个操作系统进程占内存、占 CPU 调度。连接数上到三四百光是进程切换的开销就能吃掉可观的比例而连接数上千基本就是灾难。这不是配置问题是架构决定的。所以生产环境上 PG连接池不是可选项。我选的是 PgBouncer模式用transaction。这个模式的含义是一个客户端连接只在事务期间占用一个真实数据库连接事务一提交就把连接还回池子。带来的好处是 2000 个客户端只需要 50 个真实连接代价是不支持会话级的状态比如SET的会话变量、预处理语句、会话级临时表在事务模式下都可能串到别的客户端上。[pgbouncer] listen_addr 0.0.0.0 listen_port 6432 pool_mode transaction max_client_conn 2000 default_pool_size 50 reserve_pool_size 10 reserve_pool_timeout 3 server_idle_timeout 60 query_wait_timeout 10query_wait_timeout是我特意加的。没有这个参数的话数据库一旦变慢请求会在池子里无限排队客户端超时重试重试又加重排队最后雪崩。设成 10 秒排队超过 10 秒直接快速失败应用层拿到明确错误比无限等待健康得多。配套地数据库侧也要设死超时防止一个慢查询霸占连接不放ALTER SYSTEM SET statement_timeout 30s; ALTER SYSTEM SET lock_timeout 5s; ALTER SYSTEM SET idle_in_transaction_session_timeout 300s; ALTER SYSTEM SET idle_session_timeout 1800s;这四个超时是我们线上最有效的“防雪崩四件套”。statement_timeout挡住跑飞的查询lock_timeout挡住等锁等到天荒地老idle_in_transaction_session_timeout挡住开了事务不干活的连接idle_session_timeout清理池子里挂着的僵尸连接。上线之后因为单个慢查询拖垮整个库的事故基本绝迹。3.5 备份与恢复演练没演练过的备份等于没有备份这句话我说过很多遍。备份的价值不在于“备份成功”而在于“能恢复”。我们的方案是基础备份加 WAL 归档能做到任意时间点恢复PITR。# 基础备份流式带上所需 WAL pg_basebackup -D /backup/base/$(date %F) -Ft -z -P -X stream -h 127.0.0.1 -U replicator # 归档命令写进 postgresql.conf先 cp 再校验失败要能重试 archive_command test ! -f /backup/archive/%f cp %p /backup/archive/%farchive_command这里有两个细节。第一一定要加test ! -f判断否则重试时会因为文件已存在而失败WAL 就堆积起来了。第二archive_command返回非零PG 会不停重试并保留 WAL 文件磁盘会被慢慢撑满所以必须给归档目录配监控。演练的频率我们是每月一次流程固定从备份恢复到一个临时实例指定一个三天前的时间点恢复完成后跑一组校验 SQL核对几张核心表的行数和金额汇总。有一次演练真的发现了问题——归档目录因为权限变更导致两周没写入新文件备份其实是断的。要不是演练等到真出事才发现后果不敢想。3.6 上线前压测关注三类指标不要只看 TPS压测最容易犯的错是拿一个漂亮的 TPS 数字交差。我关注的是三类东西。第一类是延迟分布P50 好看没用要看 P99 和 P999因为用户体验由长尾决定。第二类是资源饱和度CPU 使用率超过 70% 之后延迟会非线性上升IO await 超过 5ms 说明磁盘快满了。第三类是内部等待事件用pg_stat_activity采样看连接到底卡在LWLock、IO还是ClientRead上这决定了你该优化哪一层。压测期间我还会专门跑一次“故障注入”人为 kill 掉一个从库看主库写入是否受影响、应用是否自动切到另一个从库、报警是否在 30 秒内到达。这一步比跑出多高的 TPS 有价值得多。4. 踩坑实录那些凌晨两点教我的事4.1 慢查询EXPLAIN 之后还有三件事要看拿到一条慢查询多数人的第一反应是EXPLAIN ANALYZE。但执行计划经常是“看起来没问题”的因为计划里估算的行数和实际行数可能差了几个数量级。我现在的固定动作是三步。第一步看估算行数与实际行数的偏差。如果rows100而actual rows180000说明统计信息严重失真先去跑ANALYZE或者给这个字段调高default_statistics_target。这一步能解决大概一半的“莫名其妙走错计划”。第二步看Buffers。EXPLAIN (ANALYZE, BUFFERS)会告诉你读了多少个块、命中了多少缓存。如果read的数字很大说明热数据没进缓存要么是shared_buffers不够要么是查询扫了太多不相干的数据。第三步看这条查询是不是被某个函数拖慢的。我们曾经有条查询单看计划只要 20ms实际跑 3 秒最后发现是 WHERE 里调用了一个自定义函数而那个函数内部又查了一次库2000 万行数据调用了 2000 万次。这类问题执行计划上完全看不出来必须靠auto_explain或者pg_stat_statements的时间分布去定位。-- 按总耗时排序找最值得优化的查询 SELECT round(total_exec_time::numeric, 2) AS total_ms, calls, round(mean_exec_time::numeric, 2) AS mean_ms, round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct, left(query, 120) AS query_head FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;判断优先级我有个简单规则看总耗时占比不看单次耗时。一条 2 秒但一天只跑一次的查询不值得优化一条 80ms 但一秒跑一千次的查询才是金矿。4.2 锁等待为什么一个 ALTER 能让全站卡住有一次加索引我图省事直接在业务时段跑CREATE INDEX。结果它拿的是SHARE锁会阻塞所有写入队列越排越长五分钟内全站写入停滞。这件事之后我给自己立了三条规矩。第一建索引必须用CREATE INDEX CONCURRENTLY。它不阻塞写入代价是耗时更长、失败后会留下一个无效索引需要手动清理。清理的 SQL 是DROP INDEX CONCURRENTLY别用普通的 DROP。第二任何 DDL 之前先查锁。执行前先看一眼有没有长事务挂着否则ALTER TABLE会因为等锁而排队而它一旦排在队列里后面所有的查询都会被它挡住这叫锁队列级联。这种故障的杀伤力远超想象。第三给所有 DDL 加lock_timeout。设成 3 秒拿不到锁就快速失败重试总比把库拖死强。-- 谁在阻塞谁一次性看清楚 SELECT pid, pg_blocking_pids(pid) AS blocked_by, state, wait_event_type, now() - query_start AS duration, left(query, 100) AS query_head FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) 0 ORDER BY duration DESC;4.3 autovacuum 追不上事务 ID 回卷警告不是开玩笑PG 的事务 ID 是 32 位循环使用的如果老数据长期不被冻结最终会触发保护机制数据库直接进入只读状态。这东西听起来像远古传说但真遇到的时候非常吓人——日志里刷出一堆警告然后整个库拒绝写入只能紧急手工 vacuum。根因几乎都是 autovacuum 跑得太慢或者被阻塞。常见原因有三个一是大表上 autovacuum 的默认阈值太保守20% 的行变更才触发一次几百 GB 的表根本等不到二是autovacuum_max_workers默认只有 3表多的时候排不上队三是长事务挡住了回收。我们的应对是把大表逐个单独设置参数同时把 worker 数量提到 8autovacuum_naptime缩到 15 秒。另外加了一条针对age(datfrozenxid)的监控超过 10 亿就告警留出充足的处置时间。-- 查看各库的事务年龄越接近 20 亿越危险 SELECT datname, age(datfrozenxid) AS xid_age, round(age(datfrozenxid)::numeric / 2000000000 * 100, 2) AS pct_used FROM pg_database ORDER BY xid_age DESC;4.4 连接雪崩数据库变慢如何演变成全站不可用这是最有教育意义的一次事故。数据库因为一次意外的 IO 抖动慢了 3 秒应用层的连接池配置了 5 秒超时请求超时后不释放连接而是重试重试产生新连接连接数在 40 秒内从 300 冲到 1800直接把数据库顶死然后连正常的健康检查都连不上了。整个过程是一个标准的正反馈回路变慢导致重试重试导致更慢。治这个病要三管齐下。第一是池子要有限流max_client_conn和default_pool_size就是把闸门宁可拒绝一部分请求也别让所有请求一起死。第二是超时要分层客户端超时要大于服务端超时服务端超时要大于正常查询的 P99我们最后的配置是客户端 15 秒、服务端statement_timeout30 秒因为有些报表确实慢、PgBouncer 排队超时 10 秒。第三是重试要退避重试必须带随机抖动的指数退避而且最多两次很多雪崩就是无脑重试打出来的。4.5 常见问题速查表现象最可能的原因快速定位手段处置方向磁盘持续增长但数据量没变死元组堆积或 WAL 积压查pg_stat_user_tables与归档目录调 autovacuum、检查归档命令写入延迟周期性锯齿checkpoint 太频繁日志里搜 “checkpoints are occurring too frequently”调大max_wal_size、checkpoint_timeout查询突然变慢计划没变统计信息过期或缓存被挤掉EXPLAIN (ANALYZE, BUFFERS)看估算与实际偏差手动ANALYZE、调统计目标建了索引但没用上字段顺序不对或类型不匹配看执行计划是否出现 Seq Scan重建复合索引、检查隐式类型转换连接数突然暴涨慢查询堆积或应用重试风暴pg_stat_activity按 state 分组加超时、限流、退避重试分区表反而变慢分区键与查询条件不匹配看计划里是否出现全分区扫描换分区键或补局部索引备份恢复失败归档链路中断检查归档目录文件时间修复权限与归档命令、加监控最后分享一个我一直在用的排查习惯出问题时先看pg_stat_activity的状态分布再看等待事件最后才去看单条 SQL。因为绝大多数“数据库慢”不是某条 SQL 慢而是资源被抢光了。先判断是等锁、等 IO 还是等客户端方向对了后面的事情就快了。这套东西跑到现在一年半中间经历过两次大促最惊险的一次是主库所在机器的网卡抖动触发了自动切换从库接管用了 12 秒业务侧只出现了少量超时报错。我个人最大的体会是PG 的上手成本极低但它把复杂度藏在了后面——你得主动去理解 MVCC、WAL、锁和统计信息它才会在关键时刻回报你。反过来说如果只是把 SQL 塞进去能跑就行那它在数据量涨到某个点之后一定会用一种很痛的方式提醒你补课。那些凌晨两点被叫起来处理问题的经历本质上都是在补这些课。
1. 把业务核心全压到 PG 上之前我做过哪些推演“问鼎PG天下”这个名字是内部立项评审时的一句随口话后来一直没改。当时我正被三套异构存储来回同步的事情折腾得够呛一急就说干脆把核心交易、日志明细、报表聚合全收进 PostgreSQL 一套引擎里不再把数据分散到别的系统。名字听着狂做的事其实很土让一套日增两三千万行、峰值写入五六千 TPS、单表最大几百 GB 的业务完整、稳定地跑在 PG 上还指望它撑三年不用整体重做。这篇内容写给两类人一类是正在纠结要不要把 MySQL 或商业库迁到 PostgreSQL 的后端同学另一类是已经上了 PG、正被慢查询、表膨胀、锁等待、连接数打满折磨的运维和 DBA。我不复述文档只讲这个项目里做过的取舍、算过的参数、踩过的坑以及那些文档不写但线上一定会遇到的东西。1.1 需求拆解原来的架构为什么让人睡不着原来的架构是典型的三件套关系库扛交易搜索引擎扛列表页和模糊查询列式库扛报表。单看每一块都挺合理凑在一起就是一场灾难。第一是一致性交易写进关系库之后要靠异步管道推到搜索引擎中间那几百毫秒的窗口里用户下单成功刷新列表却看不到自己的订单客服每天都要解释几十遍第二是口径报表库和交易库各自算一遍 GMV小数点后两位经常对不上财务月底对账要拉三个人对两天第三是运维成本三套系统三套备份三套监控三套故障预案任何一次大促前的容量评估都要做三遍人力是实打实的三倍。真正的转折点是那次夜间批量任务。报表侧的聚合任务跑起来之后把磁盘 IO 打满交易库的写入延迟从 3ms 涨到 200ms连续三次触发告警。事后复盘发现问题的根子不在任何一个单点上而在于“同一份事实被存了三份还被三种引擎用三种方式解释”。那时候我就定了个方向把事实收敛到一处其余都是它的投影。PG 之所以成为那个“一处”不是因为它在任何一个单项上最强而是它在事务、JSON、全文检索、窗口函数、地理类型、分区、逻辑复制这几件事上都不弱能让我用一个引擎覆盖掉原来三个引擎的八成场景。1.2 三套方案摆在桌上我算了一笔什么账选型那两周我把三条路都推演了一遍最后做成一张表摆在评审会上。这里要说明的是表里的成本是按我们团队六个后端加两个运维的规模估的不同团队数字会差很多但比较的维度是通用的。维度方案A维持三件套方案B全量收敛到 PG方案CPG 为主 列存做报表投影开发改造成本几乎为零高查询层要重写约 40%中只改报表链路数据一致性最终一致存在窗口强一致单事务内完成强一致 报表秒级延迟运维复杂度三套故障面最大一套备份恢复链路统一两套但报表侧可降级单表规模上限关系库先顶不住分区 归档能撑到 TB 级同左复杂分析能力靠列存库窗口函数 并行查询够用更强三年人力投入三人常驻两人常驻两点五人常驻方案 C 看起来最稳但我最后没选理由很实在只要还留一套独立的报表库口径不一致的问题就永远治不干净而口径问题恰恰是当初逼我动手的原始动机。方案 B 的风险集中在“PG 能不能扛住那几张超大表的分析查询”这个风险可以用分区裁剪、覆盖索引、物化视图和只读从库分担来化解。所以我最终选的是 B但给自己留了一个后门报表类查询全部走专用的只读从库并且把最重的几张宽表预先做成物化视图定时刷新。真到扛不住那天再把报表链路摘出去做方案 C改动面被刻意限制在视图层业务代码不用动。这里补一句我个人的判断标准如果你的复杂查询占比低于两成全量收敛到 PG 的收益远大于风险如果超过四成老老实实保留一条分析链路别硬扛。硬扛的结果通常是交易库被分析查询拖死然后你被迫在半夜做一次紧急拆分。1.3 定架构一主两从不是抄来的是算出来的最终落地的形态是一主两从。主库只接受写和“写完立刻要读”的请求从库一给在线读、一给后台任务和报表。这个划分听着简单落地时有两个细节必须提前想清楚否则上线第一周就会出事。第一个细节是读写分离的粘主策略。无脑把读打到从库会立刻遇到“用户改完昵称刷新页面还是旧的”这类问题。我的做法是在会话层打标记任何写操作之后的 2 秒内这个会话的读请求强制回主库超过 2 秒再走从库。2 秒这个数字不是拍脑袋我们实测从库回放延迟的 P99 在 300ms 上下取 2 秒相当于留了六倍余量同时把回主库的读比例压在 5% 以内主库完全扛得住。第二个细节是从库的角色不能混用。在线读从库要求低延迟、高并发、连接数多后台报表从库要求长事务、大排序、能吃满 CPU。这两类负载挤在同一个实例上结果一定是报表把在线读拖垮。把它们分开之后我甚至给报表从库单独放宽了work_mem和maintenance_work_mem让它慢一点没关系、别影响别人就行。2. PG 的几块硬骨头核心机制拆解与实操要点PG 用起来上手极快装完就能跑但真正决定它能撑多大的是四个机制MVCC、WAL、索引、分区。这四个东西不理解透你会在半年内遇到“磁盘莫名涨满”“写入抖动”“索引建了不用”“分区反而更慢”这一整套问题。我按踩坑的先后顺序讲。2.1 MVCC为什么删了一亿行磁盘一点没瘦刚上线三个月有一张日志表被清理任务删掉了将近一亿行结果磁盘占用纹丝不动运维直接来找我。这不是 bug是 MVCC 的必然结果。PG 的更新和删除都不是原地修改而是给旧版本打上“已失效”的标记新版本另起一行。那些已经没人看得见的旧版本叫死元组它们占的空间只有等 vacuum 扫过、确认没有任何事务还需要看到它们之后才会被标记为可复用。关键在于“可复用”和“还给操作系统”是两回事。普通的 vacuum 只是把空间标成空闲留给后续的插入复用文件本身不会缩小。想让磁盘真的降下来只有两条路一是VACUUM FULL它会重写整张表并持有排他锁几百 GB 的表在业务时段做这个基本等于自杀二是pg_repack这类在线重建工具靠触发器和逻辑复制做双写能在几乎不停机的情况下把表重写一遍。真正治本的是别让死元组堆积。我们做了三件事对更新极其频繁的订单状态表把fillfactor调到 75预留出页面内的空闲空间让更新后的新版本尽量落在同一个页面里这就是 HOT 更新可以完全不碰索引对大表的 autovacuum 单独调参把autovacuum_vacuum_scale_factor从默认的 0.2 降到 0.02触发阈值从“20% 的行变了”变成“2% 的行变了”加了一条监控每小时统计一次每张表的死元组占比超过 15% 就告警。-- 找出死元组占比最高的表按需人工 vacuum SELECT relname, n_live_tup, n_dead_tup, round(n_dead_tup::numeric / greatest(n_live_tup n_dead_tup, 1) * 100, 2) AS dead_pct, last_autovacuum FROM pg_stat_user_tables WHERE n_dead_tup 10000 ORDER BY dead_pct DESC LIMIT 20;这里有个反直觉的点长事务是表膨胀的真正推手。只要有一个事务开了快照却迟迟不提交vacuum 就不敢回收任何比它更晚的死元组。我们曾经有一个报表连接忘了关自动提交挂了一整夜第二天三张核心表全胖了一圈。后来强制给所有连接设了idle_in_transaction_session_timeout 300s这类事故再没出现过。2.2 WAL 与 checkpoint写入吞吐的真正闸门很多人以为 PG 写慢是磁盘不行其实大部分时候瓶颈在 checkpoint。每一次数据页的修改PG 都要先把变更写进 WAL 日志这是顺序写很快但 checkpoint 要把内存里所有脏页刷到磁盘这是随机写很慢。更麻烦的是full_page_writes为了防止断电导致的页面断裂每次 checkpoint 之后的第一次页面修改PG 会把整个 8KB 页面原样写进 WAL。这意味着 checkpoint 越频繁WAL 里写的冗余数据就越多写放大越严重。我最初用了默认配置max_wal_size只有 1GB结果在高峰写入时 checkpoint 每两三分钟就触发一次日志里全是 “checkpoints are occurring too frequently” 的提示写入延迟呈现规律的锯齿状。调参思路很直接把 checkpoint 的间隔拉长让每次刷脏更平缓。参数默认值我们的取值调整理由max_wal_size1GB16GB减少 checkpoint 触发频次削掉写入锯齿min_wal_size80MB4GB避免 WAL 文件频繁创建删除checkpoint_timeout5min20min与 max_wal_size 配合两者谁先到谁触发checkpoint_completion_target0.50.9把刷脏摊到整个周期的 90%削峰wal_compressionoffon压缩 full page writes典型可省 30% 到 50% WAL 量wal_buffers-1自动64MB高并发小事务下减少 WAL 写系统调用次数调完之后最直观的变化是写入延迟的 P99 从 180ms 降到 27ms锯齿没了。这里要注意一个副作用max_wal_size拉大意味着崩溃恢复时要回放的 WAL 更多恢复时间会变长。16GB 的 WAL 在普通 SSD 上回放大概需要一到两分钟这个量级我们能接受。如果你的业务对恢复时间极其敏感那就得在“写入平稳”和“恢复快”之间重新找平衡点。2.3 索引选型建对索引比建多索引值钱得多我接手这套库的时候有一张表上一共 11 个索引实际被用到的只有 3 个。多出来的索引不只是浪费磁盘更要命的是每次写入都要维护它们写入性能被硬生生削掉三成。判断该建什么索引先看查询模式的形状索引类型适用查询形状我们的实际用例注意事项B-tree等值、范围、排序、前缀匹配订单号、用户 ID、时间范围最通用但要注意字段顺序把选择性高的放前面GIN包含、重叠、全文检索、JSONB 键值商品标签数组、全文搜索、扩展字段查询写入慢、体积大别滥用fastupdate可调BRIN超大表的顺序相关字段按时间顺序写入的日志表时间列体积极小但要求物理顺序与值强相关否则几乎无效GiST几何、范围类型、近邻检索配送范围判断需要装对应扩展Hash纯等值查询极少使用不支持范围多数场景不如 B-tree举个具体的例子。日志表原来在created_at上建了 B-tree 索引一张表 400GB索引本身就 9GB。后来改成 BRIN索引缩到 200KB 出头查询性能反而没降因为这张表的数据是按时间顺序插入的物理顺序和值高度相关BRIN 的块级摘要足够精准。这就是典型的“选对类型直接把成本打下来一个数量级”。另一个实战经验是复合索引的字段顺序。我们有条查询是WHERE tenant_id ? AND status ? ORDER BY created_at DESC LIMIT 20。最优索引是(tenant_id, status, created_at DESC)三个字段一次命中排序都省了。如果顺序写错比如把status放前面那么查询虽然还能用上索引但要做一次过滤加排序大租户下的耗时差了将近二十倍。-- 查索引使用率找出从没被用过的“僵尸索引” SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS idx_size FROM pg_stat_user_indexes WHERE idx_scan 0 AND indexrelid NOT IN (SELECT conindid FROM pg_constraint WHERE conindid 0) ORDER BY pg_relation_size(indexrelid) DESC;提醒一句idx_scan 0不代表立刻就能删。统计是从上次重置开始算的可能刚好没赶上一个月度报表周期。我的习惯是观察满一个完整的业务周期比如包含月末结账确认确实是零使用再动手。2.4 分区表什么量级才值得上什么时候上了反而更慢分区这件事很容易走极端。有人几百兆的表就急着分区结果管理复杂度上去了性能没变有人几百 GB 还不分区导致 vacuum 跑不完、历史数据删不掉。我的判断标准是三条满足任意一条就该考虑分区单表超过 100GB 且还在增长存在明显的“按时间批量删除历史数据”的需求查询条件里天然带有时间或租户维度能靠分区裁剪砍掉大部分数据。分区键的选择比分区本身更重要。我们用得最多的是按时间做范围分区Range按月一个分区同时用DEFAULT分区兜底防止写入因为找不到匹配分区而报错。分区数量要严格控制经验值是一张表的分区总数别超过两三百个再多的话查询规划阶段本身就要花掉可观的时间。批量删除历史数据是分区带来的最大红利。没分区之前删三个月前的数据要跑一条DELETE跑几个小时、产生海量 WAL、还得配一次 vacuum分区之后一条DROP TABLE或者DETACH PARTITION秒级完成磁盘立刻释放。-- 按月分区 默认分区兜底 CREATE TABLE event_log ( id bigserial, tenant_id bigint NOT NULL, created_at timestamptz NOT NULL, payload jsonb ) PARTITION BY RANGE (created_at); CREATE TABLE event_log_2024_05 PARTITION OF event_log FOR VALUES FROM (2024-05-01) TO (2024-06-01); CREATE TABLE event_log_default PARTITION OF event_log DEFAULT; -- 归档时先摘下来再删两段式避免影响其他会话 ALTER TABLE event_log DETACH PARTITION event_log_2023_01; DROP TABLE event_log_2023_01;一个必须知道的坑PG 的分区表上不能建全局索引每个分区的索引是各自独立的。这意味着你在分区表上按非分区键查询优化器要把所有分区的索引都扫一遍。所以分区键一定要和最高频的查询条件对齐否则分区带来的收益会被索引扫描摊薄掉。3. 从裸机到生产一份可以照着抄的落地流程机制讲完说落地。这一节是我实际走了一遍的流程包含目录规划、参数计算过程、连接池、备份演练和上线压测。参数的具体数值是基于一台 64 核 128GB、NVMe SSD 的机器算的你换成别的规格按同样的逻辑重算就行。3.1 目录规划与安装把数据盘和 WAL 盘分开安装本身没什么难度难的是目录规划。我的原则是数据、WAL、备份三样东西物理上分开条件不允许至少要把 WAL 和数据分到不同盘上。原因很实在数据文件的读写是随机 IOWAL 是顺序写混在一块盘上它们会互相干扰尤其在做 checkpoint 的时候大量随机刷脏会把 WAL 的写入延迟带起来。目录结构我固定成这样/data/pgdata放数据/wal/pgwal放 WAL/backup/base放基础备份/backup/archive放归档日志。软件用系统的包管理器装或者自己编译都行我倾向于用官方仓库的稳定版本省得为了个小特性自己编译引入依赖问题。# 初始化数据目录WAL 单独放到另一块盘 /usr/pgsql-16/bin/initdb -D /data/pgdata -E UTF8 --localeC.UTF-8 -U pgadmin # 先把 WAL 目录建好并授权再改配置指向它 mkdir -p /wal/pgwal chown pgadmin:pgadmin /wal/pgwal psql -c ALTER SYSTEM SET wal_directory /wal/pgwal;--localeC.UTF-8这个选择我特意说一下。用 C 排序规则字符串比较是按字节走的索引构建和排序都会更快代价是中文按拼音排序这类需求得在应用层做。我们的业务里没有强依赖数据库排序规则的地方所以选了性能。如果你的业务里ORDER BY大量依赖中文语义排序那还是老老实实用zh_CN.UTF-8。另外从 PG 16 开始支持 ICU 排序规则可以做到“库级用 C某个字段用 ICU”这一点在混合场景下很值得用。3.2 内存参数把每一项的计算过程摊开内存参数是最容易被抄错的地方。网上到处是“shared_buffers 设成内存的 25%”但没人告诉你这个 25% 是相对什么而言的、剩下的 75% 都去哪儿了。我把我的推导过程完整写一遍。机器 128GB先扣掉操作系统和其他进程可用给 PG 的按 80GB 算shared_buffers取可用内存的 25%也就是 20GB。为什么不设更大因为 PG 依赖操作系统的页缓存做二级缓存两个缓存各管一摊shared_buffers太大反而会增加两者之间的同步开销。20GB 这个量级在我们这套读多写多的负载下实测最平衡。effective_cache_size这个参数不占内存它只是告诉优化器“整个系统大概有多少缓存可用”用来估算索引扫描的成本。设成可用内存的 60%48GB。设小了优化器会误以为随机 IO 很贵明明该走索引的查询它去走顺序扫描。work_mem这是最容易被误解的参数。它不是“每个连接的内存”而是“每个排序或哈希操作的内存”一条查询里有三个排序节点就会用三份。用常见公式算(可用内存 - shared_buffers) / (max_connections × 3) 60GB / (200 × 3) ≈ 100MB。这个数字看着很美但根本不能这么设因为一旦有几十个连接同时跑复杂查询内存瞬间打爆。我的做法是全局设 16MB 保底然后给报表类查询在会话级单独放开到 256MB。maintenance_work_mem给 vacuum 和建索引用设 2GB。这个值越大vacuum 跑得越快大表建索引也越快性价比很高。temp_buffers默认 8MB我们调到 64MB减少临时表反复申请。# postgresql.conf 关键内存项128GB 机器分配给 PG 约 80GB shared_buffers 20GB effective_cache_size 48GB work_mem 16MB maintenance_work_mem 2GB temp_buffers 64MB huge_pages tryhuge_pages try这一行值得单独提。开启大页之后PG 的页表项数量能降两个数量级TLB 命中率上去了高并发下的性能提升在 5% 到 10% 之间属于白捡的收益。设成try而不是on是为了避免系统没配好大页时数据库起不来。3.3 存储与并行相关的参数SSD 上必须改的两个默认值默认配置是十几年前为机械盘写的放到 NVMe SSD 上会严重误导优化器。最典型的是random_page_cost默认 4.0意思是随机读一次的成本是顺序读的四倍。这个比例在机械盘上成立在 SSD 上随机读和顺序读的成本差不多通常设成 1.1 左右。不改这个参数优化器会系统性地低估索引扫描的性价比大量本该走索引的查询被改成全表扫描。配套的还有effective_io_concurrency它控制同时能发起多少个异步 IO 请求机械盘设 2SSD 可以设到 200。另外并行查询的几个参数也按核数调一下64 核的机器上max_parallel_workers_per_gather设 4max_parallel_workers设 16别一次开太多并行 worker 抢 CPU 反而会让整体吞吐下降。random_page_cost 1.1 effective_io_concurrency 200 max_worker_processes 32 max_parallel_workers 16 max_parallel_workers_per_gather 4 max_parallel_maintenance_workers 4关于random_page_cost我给一个快速验证的方法改完之后拿几条典型的慢查询跑EXPLAIN (ANALYZE, BUFFERS)看执行计划有没有从 Seq Scan 翻成 Index Scan。如果翻了而且实际耗时下降说明改对了如果翻了但耗时反而变长说明统计信息不准先跑ANALYZE再看。3.4 连接池为什么 PG 必须加一层 PgBouncerPG 是每连接一个进程的模型每个连接背后是一个操作系统进程占内存、占 CPU 调度。连接数上到三四百光是进程切换的开销就能吃掉可观的比例而连接数上千基本就是灾难。这不是配置问题是架构决定的。所以生产环境上 PG连接池不是可选项。我选的是 PgBouncer模式用transaction。这个模式的含义是一个客户端连接只在事务期间占用一个真实数据库连接事务一提交就把连接还回池子。带来的好处是 2000 个客户端只需要 50 个真实连接代价是不支持会话级的状态比如SET的会话变量、预处理语句、会话级临时表在事务模式下都可能串到别的客户端上。[pgbouncer] listen_addr 0.0.0.0 listen_port 6432 pool_mode transaction max_client_conn 2000 default_pool_size 50 reserve_pool_size 10 reserve_pool_timeout 3 server_idle_timeout 60 query_wait_timeout 10query_wait_timeout是我特意加的。没有这个参数的话数据库一旦变慢请求会在池子里无限排队客户端超时重试重试又加重排队最后雪崩。设成 10 秒排队超过 10 秒直接快速失败应用层拿到明确错误比无限等待健康得多。配套地数据库侧也要设死超时防止一个慢查询霸占连接不放ALTER SYSTEM SET statement_timeout 30s; ALTER SYSTEM SET lock_timeout 5s; ALTER SYSTEM SET idle_in_transaction_session_timeout 300s; ALTER SYSTEM SET idle_session_timeout 1800s;这四个超时是我们线上最有效的“防雪崩四件套”。statement_timeout挡住跑飞的查询lock_timeout挡住等锁等到天荒地老idle_in_transaction_session_timeout挡住开了事务不干活的连接idle_session_timeout清理池子里挂着的僵尸连接。上线之后因为单个慢查询拖垮整个库的事故基本绝迹。3.5 备份与恢复演练没演练过的备份等于没有备份这句话我说过很多遍。备份的价值不在于“备份成功”而在于“能恢复”。我们的方案是基础备份加 WAL 归档能做到任意时间点恢复PITR。# 基础备份流式带上所需 WAL pg_basebackup -D /backup/base/$(date %F) -Ft -z -P -X stream -h 127.0.0.1 -U replicator # 归档命令写进 postgresql.conf先 cp 再校验失败要能重试 archive_command test ! -f /backup/archive/%f cp %p /backup/archive/%farchive_command这里有两个细节。第一一定要加test ! -f判断否则重试时会因为文件已存在而失败WAL 就堆积起来了。第二archive_command返回非零PG 会不停重试并保留 WAL 文件磁盘会被慢慢撑满所以必须给归档目录配监控。演练的频率我们是每月一次流程固定从备份恢复到一个临时实例指定一个三天前的时间点恢复完成后跑一组校验 SQL核对几张核心表的行数和金额汇总。有一次演练真的发现了问题——归档目录因为权限变更导致两周没写入新文件备份其实是断的。要不是演练等到真出事才发现后果不敢想。3.6 上线前压测关注三类指标不要只看 TPS压测最容易犯的错是拿一个漂亮的 TPS 数字交差。我关注的是三类东西。第一类是延迟分布P50 好看没用要看 P99 和 P999因为用户体验由长尾决定。第二类是资源饱和度CPU 使用率超过 70% 之后延迟会非线性上升IO await 超过 5ms 说明磁盘快满了。第三类是内部等待事件用pg_stat_activity采样看连接到底卡在LWLock、IO还是ClientRead上这决定了你该优化哪一层。压测期间我还会专门跑一次“故障注入”人为 kill 掉一个从库看主库写入是否受影响、应用是否自动切到另一个从库、报警是否在 30 秒内到达。这一步比跑出多高的 TPS 有价值得多。4. 踩坑实录那些凌晨两点教我的事4.1 慢查询EXPLAIN 之后还有三件事要看拿到一条慢查询多数人的第一反应是EXPLAIN ANALYZE。但执行计划经常是“看起来没问题”的因为计划里估算的行数和实际行数可能差了几个数量级。我现在的固定动作是三步。第一步看估算行数与实际行数的偏差。如果rows100而actual rows180000说明统计信息严重失真先去跑ANALYZE或者给这个字段调高default_statistics_target。这一步能解决大概一半的“莫名其妙走错计划”。第二步看Buffers。EXPLAIN (ANALYZE, BUFFERS)会告诉你读了多少个块、命中了多少缓存。如果read的数字很大说明热数据没进缓存要么是shared_buffers不够要么是查询扫了太多不相干的数据。第三步看这条查询是不是被某个函数拖慢的。我们曾经有条查询单看计划只要 20ms实际跑 3 秒最后发现是 WHERE 里调用了一个自定义函数而那个函数内部又查了一次库2000 万行数据调用了 2000 万次。这类问题执行计划上完全看不出来必须靠auto_explain或者pg_stat_statements的时间分布去定位。-- 按总耗时排序找最值得优化的查询 SELECT round(total_exec_time::numeric, 2) AS total_ms, calls, round(mean_exec_time::numeric, 2) AS mean_ms, round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct, left(query, 120) AS query_head FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;判断优先级我有个简单规则看总耗时占比不看单次耗时。一条 2 秒但一天只跑一次的查询不值得优化一条 80ms 但一秒跑一千次的查询才是金矿。4.2 锁等待为什么一个 ALTER 能让全站卡住有一次加索引我图省事直接在业务时段跑CREATE INDEX。结果它拿的是SHARE锁会阻塞所有写入队列越排越长五分钟内全站写入停滞。这件事之后我给自己立了三条规矩。第一建索引必须用CREATE INDEX CONCURRENTLY。它不阻塞写入代价是耗时更长、失败后会留下一个无效索引需要手动清理。清理的 SQL 是DROP INDEX CONCURRENTLY别用普通的 DROP。第二任何 DDL 之前先查锁。执行前先看一眼有没有长事务挂着否则ALTER TABLE会因为等锁而排队而它一旦排在队列里后面所有的查询都会被它挡住这叫锁队列级联。这种故障的杀伤力远超想象。第三给所有 DDL 加lock_timeout。设成 3 秒拿不到锁就快速失败重试总比把库拖死强。-- 谁在阻塞谁一次性看清楚 SELECT pid, pg_blocking_pids(pid) AS blocked_by, state, wait_event_type, now() - query_start AS duration, left(query, 100) AS query_head FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) 0 ORDER BY duration DESC;4.3 autovacuum 追不上事务 ID 回卷警告不是开玩笑PG 的事务 ID 是 32 位循环使用的如果老数据长期不被冻结最终会触发保护机制数据库直接进入只读状态。这东西听起来像远古传说但真遇到的时候非常吓人——日志里刷出一堆警告然后整个库拒绝写入只能紧急手工 vacuum。根因几乎都是 autovacuum 跑得太慢或者被阻塞。常见原因有三个一是大表上 autovacuum 的默认阈值太保守20% 的行变更才触发一次几百 GB 的表根本等不到二是autovacuum_max_workers默认只有 3表多的时候排不上队三是长事务挡住了回收。我们的应对是把大表逐个单独设置参数同时把 worker 数量提到 8autovacuum_naptime缩到 15 秒。另外加了一条针对age(datfrozenxid)的监控超过 10 亿就告警留出充足的处置时间。-- 查看各库的事务年龄越接近 20 亿越危险 SELECT datname, age(datfrozenxid) AS xid_age, round(age(datfrozenxid)::numeric / 2000000000 * 100, 2) AS pct_used FROM pg_database ORDER BY xid_age DESC;4.4 连接雪崩数据库变慢如何演变成全站不可用这是最有教育意义的一次事故。数据库因为一次意外的 IO 抖动慢了 3 秒应用层的连接池配置了 5 秒超时请求超时后不释放连接而是重试重试产生新连接连接数在 40 秒内从 300 冲到 1800直接把数据库顶死然后连正常的健康检查都连不上了。整个过程是一个标准的正反馈回路变慢导致重试重试导致更慢。治这个病要三管齐下。第一是池子要有限流max_client_conn和default_pool_size就是把闸门宁可拒绝一部分请求也别让所有请求一起死。第二是超时要分层客户端超时要大于服务端超时服务端超时要大于正常查询的 P99我们最后的配置是客户端 15 秒、服务端statement_timeout30 秒因为有些报表确实慢、PgBouncer 排队超时 10 秒。第三是重试要退避重试必须带随机抖动的指数退避而且最多两次很多雪崩就是无脑重试打出来的。4.5 常见问题速查表现象最可能的原因快速定位手段处置方向磁盘持续增长但数据量没变死元组堆积或 WAL 积压查pg_stat_user_tables与归档目录调 autovacuum、检查归档命令写入延迟周期性锯齿checkpoint 太频繁日志里搜 “checkpoints are occurring too frequently”调大max_wal_size、checkpoint_timeout查询突然变慢计划没变统计信息过期或缓存被挤掉EXPLAIN (ANALYZE, BUFFERS)看估算与实际偏差手动ANALYZE、调统计目标建了索引但没用上字段顺序不对或类型不匹配看执行计划是否出现 Seq Scan重建复合索引、检查隐式类型转换连接数突然暴涨慢查询堆积或应用重试风暴pg_stat_activity按 state 分组加超时、限流、退避重试分区表反而变慢分区键与查询条件不匹配看计划里是否出现全分区扫描换分区键或补局部索引备份恢复失败归档链路中断检查归档目录文件时间修复权限与归档命令、加监控最后分享一个我一直在用的排查习惯出问题时先看pg_stat_activity的状态分布再看等待事件最后才去看单条 SQL。因为绝大多数“数据库慢”不是某条 SQL 慢而是资源被抢光了。先判断是等锁、等 IO 还是等客户端方向对了后面的事情就快了。这套东西跑到现在一年半中间经历过两次大促最惊险的一次是主库所在机器的网卡抖动触发了自动切换从库接管用了 12 秒业务侧只出现了少量超时报错。我个人最大的体会是PG 的上手成本极低但它把复杂度藏在了后面——你得主动去理解 MVCC、WAL、锁和统计信息它才会在关键时刻回报你。反过来说如果只是把 SQL 塞进去能跑就行那它在数据量涨到某个点之后一定会用一种很痛的方式提醒你补课。那些凌晨两点被叫起来处理问题的经历本质上都是在补这些课。
平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测)
