00:00:00
🗄️ 卷⑤ 数据库与存储运维
20 条。适用场景：查询变慢、要升大版本、要做迁移。
「要点」「我的补充」为本人撰写，非原文摘录；版本相关结论以链接内原文为准。
一、读懂执行计划：一切调优的起点
81. How to Read Postgres EXPLAIN: A Guide to Scan Types
来源：Crunchy Data · crunchydata.com 读它解决什么：EXPLAIN 的输出看得懂字面但不知道该改什么——先从扫描类型入手。
要点
- 主要扫描类型各有适用场景：Seq Scan（全表，小表或大比例命中时反而更快）、Index Scan（回表取行）、Index Only Scan（索引即可满足，不回表）、Bitmap Heap Scan（命中量中等时批量取页）
- 关键是优化器的选择往往是对的：看到 Seq Scan 别急着加索引，先看估算行数与实际行数的差距
- Index Only Scan 是最理想形态，靠覆盖索引（
INCLUDE）达成
我的补充：一定用 EXPLAIN (ANALYZE, BUFFERS) 而不是裸 EXPLAIN——前者给真实耗时和缓冲命中。最该盯的是 rows= 估算值与 actual rows 的偏差，差一个数量级说明统计信息过时或相关性被低估，先 ANALYZE 表名 再看。ANALYZE 会真实执行语句，对 UPDATE/DELETE 记得包在事务里回滚。
82. PostGIS Performance: pg_stat_statements and Postgres tuning
来源：Crunchy Data · crunchydata.com 读它解决什么：不知道该优化哪条 SQL——先让数据库自己告诉你。
要点
pg_stat_statements按规范化后的语句聚合执行次数与总耗时，是找"真正的大头"的标准手段- 排序依据应该是总耗时（次数 × 单次），不是单次最慢：一条 10ms 但每秒跑 1000 次的语句比 2s 跑一次的更值得优化
- 需要作为扩展启用并配置到
shared_preload_libraries
我的补充：起步查询：按 total_exec_time 降序取前 20。启用需要改 shared_preload_libraries = 'pg_stat_statements' 并重启（不是 reload）。优化前后用 pg_stat_statements_reset() 清零好对比。配套还应看 pg_stat_user_tables 的 seq_scan 与 idx_scan 比例，找出被全表扫的大表。
83. PostGIS Performance: Indexing and EXPLAIN
来源：Crunchy Data · crunchydata.com 读它解决什么：空间查询慢，普通 B-tree 索引不起作用。
要点
- 空间数据要用 GiST（或 SP-GiST）索引，B-tree 无法表达二维范围
- 空间查询的执行分两步：先用索引按外接框（bounding box）粗筛，再做精确几何判断
- 因此性能取决于粗筛的选择性，几何体越"胖"效果越差
我的补充：这个"粗筛 + 精算"两阶段模型是理解 PostGIS 性能的钥匙。&& 操作符只做外接框判断（走索引），ST_Intersects 才是精确判断（内部会先用 &&）。建完 GiST 索引记得 ANALYZE，否则优化器不知道新索引的选择性。检查索引是否被用上还是看 EXPLAIN，别猜。
84. PostGIS Performance: Simplification
来源：Crunchy Data · crunchydata.com 读它解决什么：几何体顶点太多导致计算和传输都慢。
要点
- 简化（
ST_Simplify类操作）用降低顶点数换取速度，适合展示用途 - 关键取舍是精度损失是否可接受：地图缩放到省级视图时，米级精度毫无意义
- 简化后可能产生无效几何（自相交），需要校验
我的补充：给前端出图的场景收益最大——按缩放级别提供不同精度的几何，而不是每次都传原始顶点。ST_SimplifyPreserveTopology 比 ST_Simplify 安全（不产生无效几何）但慢一些。简化后用 ST_IsValid 检查，有问题用 ST_MakeValid 修。更彻底的做法是预先物化多个精度层级的表。
85. PostGIS Performance: Data Sampling
来源：Crunchy Data · crunchydata.com 读它解决什么：全量数据上试参数太慢，需要用抽样先摸清规律。
要点
- 抽样的价值是把"试一次要一小时"变成"试一次一分钟"，加快迭代
TABLESAMPLE是标准语法（SYSTEM按页抽、BERNOULLI按行抽），比ORDER BY random()快得多- 抽样结论要回到全量验证，尤其涉及数据分布不均时
我的补充：ORDER BY random() LIMIT n 会全表排序，大表上是灾难；TABLESAMPLE SYSTEM (1) 按页抽样快几个数量级，代价是同页数据相关性高。这个思路不限于空间数据——任何"在大表上调优"的场景都该先建抽样表。注意：抽样表上的执行计划可能与全量不同（数据量变了优化器选择会变），所以最终验证必须在全量上做。
86. PostGIS Performance: Intersection Predicates and Overlays
来源：Crunchy Data · crunchydata.com 读它解决什么：区分"判断是否相交"与"计算相交结果"，这两件事代价差很多。
要点
- 谓词（predicate）只返回真假，可以尽早短路；叠加（overlay）要构造新几何，代价高得多
- 能用谓词表达的需求就不要用叠加，这是常见的性能浪费点
- 大量叠加运算通常需要先用谓词过滤缩小集合
我的补充：典型误用是"我只想知道有没有交集"却写了 ST_Area(ST_Intersection(a,b)) > 0——应该直接 ST_Intersects(a,b)。这条规律可以泛化到所有查询优化：先问"我真的需要计算出结果吗，还是只需要判断存在"。SQL 里对应的就是 EXISTS 优于 COUNT(*) > 0，前者能提前终止。
87. PostGIS Performance: Improve Bounding Boxes with Decompose and Subdivide
来源：Crunchy Data · crunchydata.com 读它解决什么：巨大的复杂几何体（比如整条海岸线）让索引粗筛几乎失效。
要点
- 外接框对细长或分散的几何体极不精确，粗筛后仍留下大量候选，索引形同没有
- 拆分（
ST_Subdivide）把大几何切成小块，每块外接框更贴合，索引选择性大幅提升 - 代价是行数增多，查询需要聚合
我的补充：判断是否需要拆分看 EXPLAIN ANALYZE 里索引粗筛后的 Rows Removed by Filter——这个数很大说明外接框没起到作用。ST_Subdivide(geom, 256) 是常见起点（每块最多 256 个顶点）。这是数据建模层面的优化，比调参数有效得多，同样的思路在非空间场景就是分区表。
二、大版本升级与数据类型陷阱
88. Postgres 19: How Our Advice Has Changed Since We Wrote It
来源：Crunchy Data · crunchydata.com 读它解决什么：手上那份多年前的 Postgres 调优清单，有多少条已经过时了。
要点
- 调优建议是版本相关的：默认值改进、优化器增强会让老建议失效甚至有害
- 典型例子是各类内存与并行度参数的推荐值随版本变化
- 升级后应重新评估配置，而不是照搬旧参数
我的补充：这是运维知识管理的通病——我们的经验里混着大量已过期的结论。实践建议：内部 wiki 上的调优文档必须标注适用版本与撰写日期。升级大版本后至少重看 shared_buffers、work_mem、effective_cache_size、random_page_cost（SSD 上默认值 4.0 通常偏高，1.1 更合适）这几项。
89. Postgres Serials Should be BIGINT (and How to Migrate)
来源：Crunchy Data · crunchydata.com 读它解决什么：自增主键用了 int 快到 21 亿上限——这是会让服务完全停写的事故。
要点
int（4 字节）上限约 21.47 亿，用作自增主键在大表上是定时炸弹- 溢出后新插入直接失败，且此时再改类型需要重写整表，在线迁移非常痛苦
- 新表应直接用
bigint（bigserial/GENERATED AS IDENTITY）
我的补充：现在就去查一遍：SELECT sequencename, last_value FROM pg_sequences; 对比列类型上限。超过 50% 就该安排迁移。在线迁移的常规套路是：加新 bigint 列 → 双写/回填 → 建唯一索引 → 切换主键 → 删旧列，每步都可回滚。别直接 ALTER TYPE，那会锁表重写，大表上等于停服。删除行不会回收序列值，这点常被误解。
90. Postgres 18 New Default for Data Checksums and How to Deal with Upgrades
来源：Crunchy Data · crunchydata.com 读它解决什么：数据校验和默认值变了，升级路径上有坑。
要点
- 数据校验和能在读取时发现存储层静默损坏，代价是少量 CPU 开销
- 默认值变更意味着新初始化的集群与老集群配置不一致，
pg_upgrade要求两侧一致 - 老集群可以用
pg_checksums离线开启
我的补充：pg_upgrade 会因为校验和设置不匹配直接拒绝执行，这是升级时的常见卡点。查当前状态：SHOW data_checksums;。老集群开启需要停库后跑 pg_checksums --enable，大库耗时可观，要算进维护窗口。这点开销换来的是能发现磁盘静默损坏——值得开。
91. Postgres 19 Compression: from pglz to LZ4
来源：Crunchy Data · crunchydata.com 读它解决什么：TOAST 压缩算法可选之后，该怎么选。
要点
- 大字段（超过页大小阈值）会被 TOAST 机制外存并压缩，压缩算法影响读写速度与体积
- LZ4 相比传统 pglz 压缩/解压快得多，压缩率略低
- 可按列设置，也可设默认
我的补充：判断依据是负载类型：读多写多、字段大（JSON、文本）的场景换 LZ4 收益明显；纯归档存储追求体积可以考虑其他选择。查当前设置：SHOW default_toast_compression;。注意改设置只影响之后写入的数据，已有数据不会自动重压，要靠 VACUUM FULL 或重写表。
92. British Columbia, Time Zones, and Postgres
来源：Crunchy Data · crunchydata.com 读它解决什么：时区处理出错——这类 bug 通常在跨时区或政策变更时才暴露。
要点
timestamp与timestamptz是不同的东西：前者不带时区信息，后者按 UTC 存储并按会话时区呈现- 时区规则是政治决定，会变；依赖 IANA tzdata，需要随系统更新
- 存本地时间而不存时区，历史数据在规则变更后无法正确还原
我的补充：铁律是一律用 timestamptz 存 UTC，展示时再转本地。不要存 timestamp 加一个单独的时区列。查会话时区：SHOW timezone;。容器里尤其注意：镜像里的 tzdata 可能很旧，时区规则变更（比如某国取消夏令时）后计算会错，基础镜像要定期更新。
93. Postgres 18: OLD and NEW Rows in the RETURNING Clause
来源：Crunchy Data · crunchydata.com 读它解决什么：想在一条 UPDATE 里同时拿到修改前后的值。
要点
RETURNING支持引用OLD与NEW，一次往返即可拿到变更前后对比- 过去要么先
SELECT再UPDATE（有竞态），要么写触发器 - 对审计日志、变更通知这类需求是直接的简化
我的补充：这消掉了一个真实的并发问题——"先查后改"之间数据可能被别人改掉，除非加锁。以前的替代方案是 SELECT ... FOR UPDATE 加锁再改，现在一条语句搞定，并发度更好。写审计日志的场景可以配合 WITH ... AS (UPDATE ... RETURNING ...) INSERT INTO audit ... 一次完成。
三、迁移、复制与容量判断
94. Postgres Migrations Using Logical Replication
来源：Crunchy Data · crunchydata.com 读它解决什么：要跨大版本或跨机器迁移，且停机窗口极短。
要点
- 逻辑复制按表级别复制变更，因此可以跨大版本，这是它相对物理复制的关键优势
- 迁移流程是：建订阅 → 初始同步 → 追平增量 → 短暂停写切换
- 限制要清楚：不复制 DDL、序列值需手工同步、需要主键或 replica identity
我的补充：三个必须处理的点，漏了会在切换后暴雷。一是序列值必须手工同步，否则新库插入立刻主键冲突；二是DDL 不复制，迁移期间禁止改表结构；三是无主键的表要设 REPLICA IDENTITY FULL（代价很大）。切换前用行数与关键聚合值对比校验，别只看复制延迟为 0。
95. Is Postgres Read Heavy or Write Heavy? (And Why You Should Care)
来源：Crunchy Data · crunchydata.com 读它解决什么：调优方向取决于负载性质，先量出来再动手。
要点
- 读多与写多的优化方向几乎相反：读多加索引、加缓存、加只读副本；写多则要减少索引、关注 WAL 与 checkpoint
- 判断依据来自统计视图，不是感觉
- 索引对写是净成本，读多写少才划算
我的补充：查负载比例：pg_stat_database 的 tup_returned/tup_fetched 对比 tup_inserted/tup_updated/tup_deleted。写多的集群要重点看 checkpoint 是否过于频繁（log_checkpoints = on 然后看日志里是否有 checkpoints are occurring too frequently），并调大 max_wal_size。另外去查一遍没被用过的索引：pg_stat_user_indexes 里 idx_scan = 0 的索引在白吃写入成本。
96. Postgres Internals Hiding in Plain Sight
来源：Crunchy Data · crunchydata.com 读它解决什么：想通过系统视图理解 Postgres 内部机制，而不是读源码。
要点
- 大量内部状态通过
pg_*视图暴露，可以直接查询：锁、事务、缓冲区、复制槽 - 隐藏列（
ctid、xmin、xmax）能揭示 MVCC 的实际行为 - 这些接口是排障的一手信息来源
我的补充：三个高频用法。查阻塞：pg_locks 关联 pg_stat_activity，找 granted = false 的等待者及其阻塞者。查膨胀：xmin/xmax 配合 pg_stat_user_tables.n_dead_tup 判断是否需要 VACUUM。查复制槽：pg_replication_slots 里失活的槽会一直保留 WAL 把磁盘写满——这是我见过最常见的"Postgres 磁盘突然满了"的原因，删掉不用的槽。
四、MySQL / Galera 与轻量存储
97. Stop guessing at gcache: inspect Galera/PXC write sets
来源：Percona · percona.com 读它解决什么：Galera 集群节点重新加入时总是走全量同步（SST），想知道 gcache 够不够。
要点
- gcache 缓存最近的写集，节点短暂离线后可以用增量同步（IST）追平，避免代价高昂的全量同步
- gcache 太小时窗口不够，只能 SST——全量传输会显著影响 donor 节点
- 关键是把 gcache 大小与"节点可能离线多久"匹配
我的补充：判断方式是估算写入速率与期望的离线容忍时间的乘积。运维上更重要的是别让 SST 打挂 donor：指定专门的 donor 节点（wsrep_sst_donor），并优先用 xtrabackup 方式（非阻塞）而不是 rsync。滚动重启 Galera 前先确认 gcache 够覆盖重启耗时，否则每台都走 SST，集群会很难受。
98. Stored Procedures memory consumption in Percona Server for MySQL
来源：Percona · percona.com 读它解决什么：MySQL 内存用量说不清，怀疑与存储过程有关。
要点
- 存储过程的解析与执行会占用连接级内存，连接数多时总量可观
- MySQL 的内存不只有 buffer pool，per-connection 的各类 buffer 累加起来常被低估
- 排查内存要区分全局与会话级
我的补充：MySQL 内存估算的常见错误是只算 innodb_buffer_pool_size。实际上还要加 max_connections × (sort_buffer_size + join_buffer_size + read_buffer_size + net_buffer_length + tmp_table_size)。把 sort_buffer_size 之类的会话参数调大是高危操作——乘上几百个连接足以 OOM。要精确排查用 Performance Schema 的 memory_summary_global_by_event_name。
99. Learning a few things about running SQLite
来源：Julia Evans · jvns.ca 读它解决什么：把 SQLite 用在真实服务里（不只是本地文件），需要知道并发行为。
要点
- 默认日志模式下写会阻塞读；WAL 模式允许读写并发，是服务场景的必要设置
- SQLite 的并发模型是单写多读，写入靠锁串行化
- "SQLite 不适合生产"这个结论过于粗糙：读多写少的中小规模服务它完全够用
我的补充：用在服务里必须做三件事：PRAGMA journal_mode=WAL（持久设置，只需一次）、PRAGMA busy_timeout=5000（否则并发写直接返回 SQLITE_BUSY 报错而不是等待）、PRAGMA synchronous=NORMAL（WAL 下的合理折中）。另外别把数据库文件放在网络文件系统上（NFS/SMB），锁语义不可靠，会损坏数据。
100. sqlite-utils: a nice way to import data into SQLite for analysis
来源：Julia Evans · jvns.ca 读它解决什么：手上一堆 CSV/JSON 日志要做分析，不想为此起一个数据库。
要点
sqlite-utils能直接把 CSV/JSON 导入 SQLite 并自动推断表结构- 之后就能用 SQL 做聚合分析，比写脚本或用 Excel 高效
- 适合一次性的数据探查
我的补充：这是运维日常分析的利器——把日志导进 SQLite 用 SQL 查，比 awk 管道拼半天可靠得多，尤其需要 group by 和 join 的时候。用法：sqlite-utils insert data.db logs access.csv --csv。类型推断有时会把数字列判成文本，加 --detect-types 或事后 ALTER。同类工具还有 duckdb，它能直接查 CSV/Parquet 文件连导入都不用，大文件上更快。
本卷小结
三条：
- 现在就查一遍
int主键的水位。pg_sequences的last_value对比列上限，溢出会直接停写，届时改类型要锁表重写。 - 查失活的复制槽。
pg_replication_slots里没人消费的槽会一直攒 WAL，这是"磁盘突然满了"的头号原因。 - 逻辑复制迁移别忘序列值和 DDL。切换后主键冲突基本都是序列没同步。