由 John Doe 十一月 20, 2026
2026 年 11 月,Postgres 社区周报 第 4 期,本周新闻速递。
目录

PG 19 终于正式发布,本轮版本修复了大量由 AI 发现的漏洞,大家可以去官网下载试用了。
在 DBeaver 社区版中使用 Ollama 入门指南
- Ollama 让本地运行大语言模型更简单、私密且低成本,支持 Llama、Qwen、Gemma、DeepSeek 等模型,可离线运行,并提供本地 REST API。
- 它适合开发者快速构建 AI 原型、RAG、AI Agent,以及“自然语言转 SQL”等应用,无需管理复杂的推理基础设施。
- DBeaver Community 26.2.5 新增 Ollama 支持:可在
Preferences -> AI -> Model configuration中点击+添加 Ollama,选择模型并测试连接。 - Ollama 安装简单,Ubuntu 可通过安装脚本完成,并使用
ollama run下载和运行模型。 - 在 DBeaver 的 AI Assistant 中连接 PostgreSQL(示例为 DVDrental 数据库)后,可让模型查看表、视图等结构信息。
- 用户可用自然语言提出数据需求,例如“找出收入最高的前五部租赁影片”,Ollama 会生成 SQL,并提供运行、编辑或复制选项。
- 核心价值:DBeaver + Ollama 可在本地实现 AI 辅助数据库查询与开发,减少手写 SQL,同时保持对数据和基础设施的控制。
生产环境中的 Docker PostgreSQL:数据卷、共享内存、连接池与备份
- PostgreSQL 18+ 官方镜像的挂载路径已从
/var/lib/postgresql/data改为/var/lib/postgresql,PGDATA 位于版本化子目录中;不要将 PG17 旧卷直接挂给 PG18,应使用pg_dumpall导出后在新卷中恢复。 - Docker 默认
/dev/shm仅 64 MB,易导致并行查询、哈希连接报共享内存不足;生产环境应设置shm_size: 1gb或更高。 docker stop默认 10 秒后发送 SIGKILL,可能导致 PostgreSQL checkpoint 未完成并触发崩溃恢复;应设置stop_grace_period: 60s或更长。- 健康检查必须给
pg_isready传入-U和-d,否则可能误判;同时配置合理的interval、timeout、retries和start_period。 - PostgreSQL 每连接占用独立进程,突发连接容易导致 OOM;应在前面部署 PgBouncer,使用 transaction pooling,并控制 PostgreSQL 的
max_connections。 - 固定镜像版本,避免使用
:latest;启用--data-checksums;不要将 5432 暴露到0.0.0.0,PgBouncer 应绑定 localhost 或私有网络。 - 优先使用本地 NVMe 上的 Docker named volume;避免将 PGDATA 放在 SMB/CIFS 网络共享上,并注意 Debian 镜像的 UID/GID
999:999权限问题。 - 切勿对运行中的 PGDATA 直接 tar/rsync 备份;中小型数据库用
pg_dump -Fc,大型或低 RPO 场景用 pgBackRest/WAL-G 做持续 WAL 归档与 PITR,并定期演练恢复。 - 按主机内存成比例调整
shm_size、shared_buffers、effective_cache_size、work_mem、max_connections,并为 Linux page cache 预留足够内存。 - 常见故障:共享内存报错需增大
shm_size;权限报错改用 named volume 或在 Linux 上chown 999:999;PG18 初始化失败通常是挂载了错误版本的旧数据卷。
CREATE TEMP TABLE 的隐藏成本:系统目录膨胀与 LWLock:SInvalWrite
- Postgres 18 实测:每事务创建/删除临时表仅 3783 TPS;每会话复用一个临时表 17866 TPS;用 CTE 完成同样工作 86997 TPS,约快 23 倍。建删表带来的 catalog/文件开销约造成 4.7 倍差距。
- 成本 1:每次建删都有 catalog 与文件开销。 一个两列临时表每次 CREATE/DROP 会插入并删除 31 行系统目录记录、创建并删除 3 个文件。高频执行会导致
pg_class、pg_attribute、pg_type、pg_depend等目录表大量死元组和膨胀;实测 70 秒内pg_attribute增长 737–839 MiB。 - 成本 2:大量会话时共享失效队列成为瓶颈。 每次事务临时表 DDL 约发送 132 条 invalidation 消息,所有 backend(包括 idle)都要读取。队列是实例级 4096 槽,idle 会话会拖住队列尾部,writer 等待
LWLock:SInvalWrite,严重时发生 full cache reset。 - 目标 500 TPS、16 个 DDL 客户端时:0 idle 达 502 TPS;1000 idle 仍 502 但 54% active 时间等锁;3000 idle 降至 274 TPS;5000 idle 降至 127 TPS(另一次为 249)。5000 idle 时
LWLock:SInvalWrite占所有 backend 等待样本 84.7%。 - 影响是实例级、跨数据库的:即使其他客户端不做 DDL,在 1000–5000 idle 会话下也损失 27–29% TPS。idle 会话追赶消息本身也消耗 CPU。
- 其他限制:autovacuum 不处理临时表,只有所属会话能 VACUUM;长寿命临时表会阻碍 freezing;临时表只在主库可用。主库高频建删临时表还会让备库回放大量 WAL 和 ACCESS EXCLUSIVE 锁记录,造成明显延迟。
- 排查重点:监控
LWLock:SInvalWrite、idle backend 数量与max_connections、系统目录删除速率(pg_attribute是最强信号)。用连接池、缩小应用连接池、设置idle_session_timeout降低 idle 连接。 - 改进建议:不要把临时表放在高频代码中;优先用 CTE、PL/pgSQL 变量/数组/
unnest,批量 upsert 可用unnest($1::int[], $2::text[])。必须用时,每会话创建一次并ON COMMIT DELETE ROWS(实测 17866 TPS),但需直连或 session 模式连接池;每次 commit 仍有 3 个 ACCESS EXCLUSIVE 锁和 5 条 invalidation 消息。
公共安全提醒 — 大型 Postgres 实例需要大页(Huge Pages)
- 对大型 Postgres 实例(
shared_buffers >= 32GB),huge_pages保持默认try往往不合适;若 OS 未预留大页,Postgres 会静默使用 4KB 普通页,导致页表膨胀、TLB miss 增加、内存访问开销上升。 - 实际调优案例:64 CPU、256GB RAM、OLTP、约 500 活跃会话;启用并正确配置大页后,混合 OLTP 工作负载约提升 12% TPS 。
- 补充基准:EC2
m7gd.metal上只读 pgbench 从约 900K TPS 提升到 1.1M TPS(约 20%) ;未配置大页时页表可高达约 20GB,相当于浪费大量内存。 - 不少错误建议来自 AI 模型和宽松的在线配置生成器;部分生成器对大页提示不足,甚至完全不提。
- 配置大页要精确计算、避免过度分配;Postgres 启动后
HugePages_Free应接近 0。并行查询较多时可考虑为min_dynamic_shared_memory预留额外大页。 - 同时别忘了禁用 Transparent Huge Pages,并注意 NUMA 相关配置。总体结论:大型实例上配置大页成本低、收益明显,值得优先处理。
如何降低 PostgreSQL 内存占用并将云账单减半
- 客户通过调整连接池设置并优化租户清理任务,将云账单减半;其中最难处理的是 PostgreSQL 内存告警。
- 该数据库采用“每个租户一个 schema”的架构,共有 432 个 schema、82,058 张表,应用通过连接池连接 PostgreSQL。
- PostgreSQL 内存持续增长的原因:每个连接都会保留其访问过表的私有缓存(列、索引、约束等),且只有连接关闭时才释放。
- 连接池原本让每个连接保持 24 小时,导致连接服务大量租户、访问数千张表后缓存不断膨胀,占用大部分服务器内存。
- 解决方案:让连接池定期回收连接,例如在 PgBouncer 中设置
server_lifetime = 600,即连接 10 分钟后退役并重建,新连接缓存为空。 - 该方案成本很低,只是每 10 分钟重建一次连接;在 transaction pooling 模式下效果最好。
- 可通过
pg_backend_memory_contexts查看当前会话的 catalog cache;PostgreSQL 14+ 可用,需要超级用户或pg_read_all_stats权限。 - 测试显示,访问 1,000 张小表可使单会话内存从 512 kB 增至约 8 MB;表更宽、索引更多时占用更高,实际值还会略高。
- 效果:连接生命周期从 24 小时降到 10 分钟,内存使用率从 93% 降到 50%,实例内存减半且不再出现内存告警。
- 关键建议:如果采用多租户 schema 架构,应检查连接池保持连接的时间,这可能决定是否需要更大服务器。
索引扫描只是故事的一半:磁盘上的行序如何影响性能
- 同一查询、同一索引性能差异,可能不在索引或存储,而在表堆(heap)中相关行的物理聚集程度。
- 索引扫描先用索引定位行,再回表读取 heap 页;返回同样多行,若分散在更多页,冷缓存时 I/O 会显著增加。
- 案例中慢环境
correlation约 0.037,快环境约 0.571;更直接的诊断是用ctid统计匹配行占用的不同 heap 页数,慢环境某查询约占 20 倍页数。 - 根因是并行加载数据导致不同查找值的行交错写入,破坏了源库中的物理局部性;重建索引不会重排表堆。
VACUUM FULL主要回收膨胀/碎片,不按查询键排序,不能解决这种布局问题。- 可用
CLUSTER或pg_repack --order-by按索引重排堆,但需注意锁、空间和扩展支持;重排后应ANALYZE并在可比冷缓存条件下复测。 - 重排不是永久维护,后续写入会再次侵蚀局部性,应关注迁移加载策略和持续负载。
- 结论:在怀疑存储延迟前,先比较同一查询在各环境实际读取的 heap 页数。
Postgres 在等待什么(以及为什么仪表盘无法告诉你)
- 核心观点:等待事件仪表盘能显示数据库时间花在哪些等待类型上,但不能解释“某个请求为什么慢”,尤其是当原因很少见、很短或占总时间很小时。
- 等待事件机制:Postgres 的 wait event 只是后端共享内存中的实时标志,表示“此刻正在等待什么”;它不计数、不计时、不记录历史。
- 仪表盘依赖采样:pg_wait_sampling、云厂商和监控工具都通过定时读取该标志来估算时间占比。占比可以接近真实分布,但无法给出等待次数、每次持续时间,也无法还原单个请求经历了哪些等待。
- 采样份额的局限:同样的时间占比可能来自大量短等待或少量长等待;短于采样间隔的等待容易漏掉;“一个请求为什么慢”无法从聚合占比中回答。
- 慢结账案例:仪表盘显示主要时间在 WAL 写入、WAL fsync、CPU 和磁盘读,
Lock:transactionid仅约 2.3%。按此建议尝试异步提交和增加 shared_buffers,均值改善但 p99.9 和最慢请求几乎不变。 - 真正原因:后台任务一次更新 100 行并持有行锁约 17 ms。行锁等待只占总等待时间约 2.5%,却是 51% 的慢请求(>5 ms)的主要等待;在 >8 ms 的慢请求中占 65%。
- 有效修复:把后台任务拆成每次 5 行,使每行锁持有时间从约 17 ms 降到约 1 ms。p99.9 从 11.0 ms 降到 5.8 ms,最慢请求从 26.7 ms 降到 10.0 ms,>5 ms 请求数从 972 降到 388。
- 现有工具不足:
pg_locks、log_lock_waits、pg_stat_lock、pg_stat_io、pg_stat_statements、EXPLAIN ANALYZE各有用途,但要么只看当前状态,要么受deadlock_timeout限制,要么只有总量/均值,无法按请求记录短锁等待。 - 结论与方向:要回答“这个请求为什么慢”,需要按请求记录每一次等待及其持续时间。正式版 Postgres 目前没有此能力;文章提到两条路径:处于评审中的
pg_wait_event_tracing补丁,以及需要主机 root 权限的 eBPF 工具pg_wait_tracer。