知其然，知其所以然
PostgreSQL 实测 2026-10-30

PostgreSQL UPDATE 之后旧行还在吗:用 pageinspect 看 xmin、xmax 和 ctid,DELETE 与 ROLLBACK 各留下什么,只更新非索引列 1000 次表仍是 1 页

实测 PostgreSQL 16.15 的行版本:用 pageinspect 看堆页,UPDATE 会插入新版本并填旧版本的 xmax,DELETE 只填 xmax,ROLLBACK 的版本留在页里只是标志无效;repeatable read 事务在别人删除后仍能看到旧行;只更新非索引列 1000 次全是 HOT 更新,表仍是 1 页。

PostgreSQL MVCC xmin xmax ctid pageinspect HOT VACUUM 行版本 真实验证 动画演示
PostgreSQL 实测 2026-10-30

PostgreSQL 表膨胀与 VACUUM:20 万行全表 UPDATE 后 26.9 MB 变 53.9 MB,VACUUM 为什么不缩小文件,VACUUM FULL 会挡住 SELECT

实测 PostgreSQL 16.15 表膨胀与 VACUUM:20 万行全表 UPDATE 后表大小从 26.9 MB 翻倍到 53.9 MB;普通 VACUUM 清掉死行但文件不缩小,之后的更新重用空间;VACUUM FULL 缩回 26.9 MB 但会挡住 SELECT;只有尾部空页能被截断;老事务会让 VACUUM 回收不了。

PostgreSQL 表膨胀 VACUUM VACUUM FULL pgstattuple dead tuples ACCESS EXCLUSIVE 长事务 真实验证 动画演示
PostgreSQL 实测 2026-10-30

autovacuum 什么时候触发:门槛是 50 + 0.2 × 行数,更新 2000 行不触发、累计 2100 行触发,大表为什么一直不清理

实测 PostgreSQL 16.15 autovacuum 什么时候触发:门槛是 50 + 0.2 × 行数(1 万行的表 2050),更新 2000 行不触发、累计 2100 行触发;analyze 的门槛 1050 先到;10 万行的表默认要 20050,单表设 scale_factor = 0 后 1.0 s 内清理;老事务让 autovacuum 空转;日志里能看到 removed 行数。

PostgreSQL autovacuum autovacuum_vacuum_scale_factor autovacuum_naptime dead tuples ANALYZE pg_stat_user_tables 长事务 真实验证 动画演示
PostgreSQL 实测 2026-10-30

PostgreSQL HOT 更新与 fillfactor:fillfactor 100、90、70、50 的 HOT 占比是 0%、12.2%、44.7%、100.0%,代价是表变大

实测 PostgreSQL 16.15 HOT 更新与 fillfactor:改有索引的列不可能是 HOT;只改没索引的列也要页里有空位;fillfactor 100/90/70/50 的 HOT 占比是 0%/12.2%/44.7%/100.0%;连续更新 5 轮 fillfactor 100 涨到 85.2 MB 而 50 不变;长事务会挡住剪枝(1 页变 5 页);fillfactor 50 的全表扫描 6.8 ms 对 4.5 ms。

PostgreSQL HOT heap-only tuple fillfactor 页内剪枝 表膨胀 pg_stat_user_tables 长事务 真实验证 动画演示
PostgreSQL 实测 2026-10-30

ERROR: could not serialize access due to concurrent update:PostgreSQL 三个隔离级别、写偏斜、40001 重试和丢失更新实测

实测 PostgreSQL 16.15 事务隔离级别:READ COMMITTED 同事务两次读不同、UPDATE 等锁后重新评估 WHERE;REPEATABLE READ 无幻读但更新别人改过的行报 could not serialize access due to concurrent update;SERIALIZABLE 拦下写偏斜;热点行 8 个会话重试 1180 次;先读后写丢 311 次更新,原子 UPDATE 和 FOR UPDATE 不丢。

PostgreSQL 事务隔离级别 could not serialize access 40001 REPEATABLE READ SERIALIZABLE 写偏斜 丢失更新 真实验证 动画演示
PostgreSQL 实测 2026-10-30

ALTER TABLE 为什么会卡住所有查询:PostgreSQL 锁队列、lock_timeout 和哪些 DDL 会重写整张表

实测 PostgreSQL 16.15 ALTER TABLE 为什么会卡住所有查询:长查询拿着 ACCESS SHARE,ALTER 排队,后来的普通 SELECT 被挡 1.59 s;pg_locks 里能看到排队的锁;lock_timeout 把 C 的等待降到 0.10 s;重试循环;各种 DDL 是否重写整表(add column 可空 0.6 ms,volatile 默认值 594.2 ms);CREATE INDEX CONCURRENTLY 不挡写入。

PostgreSQL ALTER TABLE 锁队列 ACCESS EXCLUSIVE lock_timeout CREATE INDEX CONCURRENTLY pg_locks DDL 真实验证 动画演示
PostgreSQL 实测 2026-10-30

PostgreSQL 建了索引为什么还是 Seq Scan:选择性、lower() 函数、LIKE 前缀、random_page_cost 四类原因实测

实测 PostgreSQL 16.15 索引为什么没被用:100 万行里取 1 行走 Index Scan、取 90% 走 Seq Scan 都是对的;30% 起索引扫描比全表扫描慢;对列做 lower()、加法、类型转换会让普通索引失效(89.28 ms 对 0.00 ms);LIKE 前缀在 en_US.UTF-8 下要 text_pattern_ops;random_page_cost = 1.1 让 50% 的查询换成更慢的 Index Scan(190.70 ms)。

PostgreSQL 索引不生效 Seq Scan Index Scan 表达式索引 text_pattern_ops random_page_cost EXPLAIN ANALYZE 真实验证 动画演示
PostgreSQL 实测 2026-10-30

PostgreSQL 索引类型怎么选:B-tree、Hash、GIN、BRIN 在 100 万行上的大小、建索引耗时和查询耗时

实测 PostgreSQL 16.15 的索引类型:100 万行上 B-tree 18.8 MB、Hash 32.0 MB、BRIN 24 KB;BRIN 只在与物理顺序相关的列上有效(随机列 43.66 ms 等于没用,顺序列 0.44 ms);Hash 不支持范围;jsonb 的 GIN 与 jsonb_path_ops(12.0 MB 对 8.9 MB);pg_trgm 让 LIKE '%qzx%' 从 59.80 ms 降到 0.07 ms。

PostgreSQL 索引类型 B-tree Hash GIN BRIN pg_trgm jsonb 真实验证 动画演示
PostgreSQL 实测 2026-10-30

PostgreSQL EXPLAIN ANALYZE 估计行数和实际行数差很多怎么办:过期统计信息、列相关、数据倾斜和 pg_stat_statements

实测 PostgreSQL 16.15 怎么读 EXPLAIN ANALYZE:估计行数与实际行数对比;统计信息过期时估计 1 行、实际 900000 行,Nested Loop 767.68 ms,ANALYZE 后 109.37 ms;两列相关用 CREATE STATISTICS 把估计从 1030 修正到 10200;statistics target 对倾斜数据的影响;pg_stat_statements 找出平均 42.230 ms 的无索引查询。

PostgreSQL EXPLAIN ANALYZE 统计信息 ANALYZE CREATE STATISTICS pg_stat_statements 行数估计 慢查询 真实验证 动画演示
PostgreSQL 实测 2026-10-30

PostgreSQL Nested Loop、Hash Join、Merge Join 什么时候用哪个:work_mem 分批、外键没索引和 NOT IN 为什么慢

实测 PostgreSQL 16.15 三种 JOIN 算法:小外表+索引用 Nested Loop(0.04 ms)、大批量用 Hash Join(144.82 ms 对 Nested Loop 550.43 ms);work_mem 4MB 时 Hash Join 分 8 批;Merge Join 在 ORDER BY 加 limit 时 47.30 ms 对 357.60 ms;外键列没索引从 0.23 ms 变 64.97 ms;NOT IN 放不进哈希时 10 秒超时,有 NULL 时一行都查不到。

PostgreSQL JOIN Nested Loop Hash Join Merge Join work_mem NOT IN NOT EXISTS 执行计划 真实验证 动画演示
PostgreSQL 实测 2026-10-30

Sort Method: external merge Disk 是什么意思:PostgreSQL work_mem 与排序、top-N heapsort、临时文件日志实测

实测 PostgreSQL 16.15 work_mem 与排序:100 万行 work_mem 4MB 时 Sort Method: external merge Disk: 17672kB,64MB 时 quicksort Memory: 55827kB;order by limit 10 用 top-N heapsort 只占 27kB;有索引时没有 Sort 节点 28.64 ms 对 258.66 ms;log_temp_files 的日志和 pg_stat_database;HashAggregate 4MB 时分 9 批写 23536kB。

PostgreSQL work_mem external merge Sort Method top-N heapsort 临时文件 log_temp_files HashAggregate 真实验证 动画演示
PostgreSQL 实测 2026-10-30

PostgreSQL shared_buffers 该设多大:pg_buffercache 看缓存、全表扫描的环形缓冲、命中率和 pg_prewarm 实测

实测 PostgreSQL 16.15 shared_buffers 与缓存命中:重启后第一次 read=3623、第二次全部 hit;全表扫描只占 33 个缓冲区不会灌满缓存;2 万次随机点查索引页命中率 95.4%、堆页 44.3%;shared_buffers 16MB/64MB/256MB 的 read 数 31857/13502/0;pg_prewarm 把 2745 + 15424 个页预热回缓冲区。

PostgreSQL shared_buffers pg_buffercache pg_prewarm 缓存命中率 pg_statio_user_tables 环形缓冲 真实验证 动画演示
京ICP备2026067502号-1京公网安备11010502063487号