PostgreSQL 社区周报(第 4 期):PG 19 正式发布!

由 John Doe 十一月 20, 2026

2026 年 11 月,Postgres 社区周报 第 4 期,本周新闻速递。

目录

Postgres 社区周报

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。

深入阅读