文章

• 1: Victoria：吊打业界的可观测性全家桶来了
• 2: 让PG停摆一周的大会：PGCon.Dev 2024 参会记
• 3: PostgreSQL 宏观查询优化之 pg_stat_statements
• 4: 如何用Pigsty监控现有PostgreSQL (RDS/PolarDB/自建)？
• 5: PG慢查询诊断方法论
• 6: Pigsty正式发布
• 7: 黄金监控指标：错误延迟吞吐饱和
• 8: PostgreSQL监控系统Pigsty概述
• 9: PostgreSQL的KPI
• 10: PostgreSQL监控系统概览
• 11: 关系膨胀的监控与治理
• 12: 监控PG中的表大小
• 13: PG服务器日志常规配置

1 - Victoria：吊打业界的可观测性全家桶来了

最近几周老冯都在忙一件事，准备 Pigsty v4.0 —— 最主要的工作就是将 Prometheus 和 Loki 换为 Victoria 全家桶。Victoria 是朴实无华的强悍 ——效果非常炸裂。这一部分已经完工，发布一个 Beta 版本让有需要的朋友先耍一耍。

VictoriaMetrics 初体验

你可能没听说过 VictoriaMetrics，但肯定听说过 Prometheus —— 监控领域的事实标准。VictoriaMetrics 就是 Prometheus 的上位替代品。由白俄罗斯大神程序员 Aliaksandr Valialkin 单枪匹马搞出来，吊打业界的神器。

老冯还记得五年前在探探的时候，那时候我们的监控系统里有五千万左右的时间序列，用了十二台物理机（64C 256G）跑 Prometheus 集群。后来我把 Prometheus 换成了三节点的分布式 VictoriaMetrics，结果轻松扛下来了。后来我还试过，一台顶配物理机也能扛住，这实在是太惊人了！ 那时候我测试下来，VM 的内存/磁盘使用量是 Prometheus 的 1/4 ，查询性能则是 4x 左右，着实让我印象深刻。

业界有很多性能对比（Benchmark），VM 基本都吊打 InfluxDB 、Prometheus、TimescaleDB 的。不管是写入吞吐量还是高基数查询（High Cardinality），VM 都是碾压级的存在。

在 Pigsty 里面，我之前一直用 Prometheus，而 VM 作为专业版可选模块。不过最近有个契机，让我感觉有必要给 Pigsty 的监控基建也翻新一下了 —— 第一是原本使用的日志方案 Grafana Loki 和 Promtail 要淘汰了，想来想去还是得上 VictoriaLogs。第二是正好有个客户 —— 影视飓风 要部署生产级别的 VictoriaMetrics，我就干脆一起搞了。

VictoriaMetrics 其实是一个全家桶，不仅仅可以替代 Prometheus，而且还有 VictoriaLogs 用于存储日志，VictoriaTraces 存储链路追踪，我想着干脆都一起上了吧。于是就在 Pigsty v4 中对 Infra 模块整个进行重写。

为什么要 Vicotira 全家桶？

在聊性能之前，老冯想先聊聊 VictoriaMetrics 背后的男人 —— Aliaksandr Valialkin（@valyala）。这哥们是白俄罗斯人，在搞 VictoriaMetrics 之前，他是一家广告技术公司 VertaMedia 的 CTO。

在 Go 语言社区里，他早就是个传奇人物了。他写的 fasthttp 库有 2.3 万 Star，性能是标准库 net/http 的 10 倍， 150 万并发连接，每秒 20 万请求。他的 quicktemplate 模板引擎比 html/template 快 20 倍，fastjson 解析器比 encoding/json 快 15 倍。

这些库有个共同的特点：热路径零内存分配。这也是 VictoriaMetrics 为什么这么猛的核心秘密 —— 同样的设计哲学贯穿始终。valyala 的代码风格就是两个字：硬核。不依赖第三方库，极致的内存管理，不仅算法牛逼，工程实现更是变态。所以 VM 继承了 ClickHouse 的衣钵：快，省，稳。它就像是数据库界的 AK-47，结构简单，皮实耐造，但火力极其凶猛。

valyala 这哥们还贼有个性，一个人单枪匹马搞出来的东西吊打业界，放群嘲 AOE ，关键他还是太有实力，直接贴脸用 Benchmark 噎的别人说不出话来。用实力说话就是这么带劲。老冯感觉和他很对脾气，惺惺相惜，经常在 X 上互赞

Victoria 有多强

言归正传，简单来说，这次我弄了 10 个节点作为测试环境，收集所有的指标和日志。Pigsty v4.0 使用 VictoriaMetrics + VictoriaLogs，一天的数据量，12 万个时间序列用了 600 兆内存，11 亿个数据点占了 440MB 存储；50 万行日志占了 6MB 不到的存储。

也就是说，整个监控基础设施，在充分监控十台物理机和数据库应用的情况下，（还要加上 Grafana，Alertmanager 这些）大概使用了 0.2 个 vCPU / 1GB 的资源。可谓是非常经济实惠了！

作为对比，我又运行 Pigsty v3.7 10 节点环境，使用原本的 Prometheus + Loki ，跑了才 10 个小时。资源使用情况如下。基本上已经接近/超过 VictoriaMetrics 全家桶了，主要是数据量太小，弄几百个节点这个差距会更明显。

当然，要是说只是省点内存磁盘 CPU 啥的，我倒也没那么大兴趣去换。主要是查询响应时间也快了很多，这就不一样了，特别是 VictoriaLogs 相比 Loki，简直就是碾压式的降维打击。面板加载的速度肉眼可见的快了许多，那种上百个 Panel 的 Dashboard 也是瞬间全出，这个感觉实在是太爽了！

老冯自己的测试毕竟规模有限，业界三方数据更有说服力。下面是 Claude 汇总的一些测试用例。不是百分之几十几十的提升，都是几倍几倍的提升，朴实无华的强力。

Victoria 如何替代 Prometheus

有很多人问，VictoriaMetrics 运维复杂不复杂，从 Prometheus 迁移麻烦吗？老冯可以说，基本上是 “原位替代” —— 就是说，你把 VM 的二进制改个名字顶替掉 prometheus，它也能跑起来。

当然这么说其实是有点夸张了，毕竟还是有一点点小小的区别 —— 比如告警规则（Alert Rules）和预计算规则（Record Rules）其实是由一个单独的组件 VMAlert 来负责的，除此之外，它基本和 Prometheus 一模一样。你可以用一样的配置文件，用同样的 PromQL 查询 —— 当然有个别参数其实也有细微的区别，但都很简单。就一个二进制走天下，但也有分布式的集群版本。

有人说，啊这个分布式集群的架构看上去好复杂。相信我，第一，其实也没啥复杂的，第二，你的量绝对用不上分布式 —— 如果你真有那个量，你现在应该已经早就在用 VictoriaMetrics 了。我们 5000 万时间序列单机搞定，你也没必要去折腾分布式的版本，想要冗余，简单的跑两个独立副本去抓就够了。

当然，VictoriaMetrics 有自己的查询语言 MetricsQL，但也兼容 PromQL。这个老冯就真的懒得改了 —— 那么多个 Dashboard 里面的查询语句，我可没兴趣改写。但好处就是，VictoriaMetrics 可以完美扮演一个 Prometheus，你的 Grafana 只需要简单改一个端口，就可以切换到 VictoriaMetrics。

VictoriaLogs：从拖拉机到法拉利

如果说 VictoriaMetrics 替换 Prometheus 是 “很不错”，那么 VictoriaLogs 替换 Loki 就属于 —— 从拖拉机到法拉利。我唯一后悔的是为啥没早点把 Loki 给下掉。当然，和 Loki 一起下掉的还有 Loki 配套的日志 Agent Promtail，这个日志收集组件烂尾了，2026 年弃用，这也是老冯这次升级的主要原因 —— 然后用 vector 给替换掉了。

为什么我看这 Loki 不爽很久了？

Loki 的设计哲学是“不索引全文，只索引标签”。听起来很美好，但在大规模日志检索时，它本质上就是个 分布式的 Grep。你要查几个关键字，它得把原本的数据块拉出来暴力扫描。数据量一上来，查询慢得让人怀疑人生，动不动就超时或者 OOM（内存溢出）。有时候日志面板时间范围拉大一点，就直接报错了。

而 VictoriaLogs 采用了类似 ClickHouse 的列存和 Bloom Filter 技术。它虽然也不搞全文索引（那样太费空间），但在过滤和定位数据块上做得极极极其高效。不仅快的一批，而且稳如老狗。10x 的性能力大砖飞，大力出奇迹。

虽然 VLogs 不兼容 LogQL，使用的是自己的 LogsQL，但这一次，我把 Loki 的查询语句 LogQL 全部丢进了垃圾桶。LogsQL 明显要优雅，简洁的多：

最爽的是，LogsQL 里 Stream Selector 是 可选的。你可以直接写 "error" "timeout" 来全局搜索，不用像 LogQL 那样必须先指定标签。这在排查问题的时候太实用了 —— 很多时候你根本不知道错误会出现在哪个服务里。

如果你还在用 ELK 或者 Loki 这类古早日志方案，真的不如试一试力大砖飞的 VictoriaLogs。说不定连 ClickHouse 的活儿都能干掉一部分了。

VictoriaTraces

可观测性三剑客，除了指标与日志，还有一个链路追踪（Traces）。老实说，老冯在基础设施和数据库监控里面基本上用不到 Traces。但反正就是加双筷子的事情， 也就顺手弄进来了。你就把他当成一个 Jaeger 用就好了。但这个项目是刚刚从 VictoriaLogs 里分支出来的，成熟度还有待观察，老冯自己也没场景验证。

除此之外，还有一些周边的工具，比如专门用来计算告警的 vmalert，可以独立使用的抓取组件 vmagent，日志收集组件 vlagent，还有备份恢复，auth，之类的各种工具，做的非常的细。企业版里还有降采样，异常检测之类的功能。不过企业版老冯就没啥兴趣折腾了，想要用，自己去下载买 license 吧，反正我觉得开源版够够的了。

我应该如何上手？

为了帮助用户上手 Victoria 全家桶，老冯还是为用户准备了不少好东西，第一个好东西是 APT / DNF 仓库，里面提供了 Victoria 全家桶的 RPM/DEB 包。单机版，集群版，工具包，Agent，Grafana 数据源，全都打包好了。免去你自己去 GitHub 上扒拉 Tarball，可以直接 yum / dnf install 完成安装。

虽然 Pigsty v4 才正式切换到 Victoria 全家桶，但是 Pigsty Infra 仓库里面维护这些RPM/DEB 包已经很长时间了，久经生产考验。当然也顺便一提，这里面还有其他好东西，比如 Grafana / Prometheus / 对象存储全家桶。（包括 MinIO 不再发布二进制后，老冯还打了 2025-12 修完 CVE 的 RPM/DEB 包）

当然，即使是打好了包，从零开始部署 VM 全家桶还是需要不少工作的，设计目录，参考文档进行配置，接入 Grafana ，开发 Dashboard，Nginx 对接，证书申请，有很多很多脏活累活 —— 就算你用容器也一样省不了。所以老冯的 Pigsty 还有一个妙用，就是一键在 Linux 裸机上帮你拉起这套全家桶。

如你所见，所有服务都被 nginx 封装好了 （又省掉了一个 VMAuth 组件哈哈），统一通过 80/443 端口的 i.pigsty 服务对外暴露。 Nginx + Grafana + VMetrics + VLogs + Vtraces + VMALERT + Alertmanager —— 可观测性七件套，As your service! 整整齐齐一家人！

包括这些组件的自监控，也都配置好了。主机监控，Redis，PostgreSQL 这些也都带在里面了。你要把自己的 App 纳入监控，也完全可以很轻松的用添加配置文件的方式，将其加入进来。

从某种意义上来说，现在的 Pigsty 不仅仅是一个 PostgreSQL 数据库发行版了，还是一个 Observability 可观测性发行版！

快速上手

Pigsty v4 新增了一个配置文件，infra.yml ，这个模板里只会安装纯粹的 Victoria 全家桶，没有 PostgreSQL / ETCD 这些东西。如果你只是需要一个纯粹的 Vicotira 全家桶，只需要一键就可以在主流 Linux 上交付：

curl https://repo.pigsty.cc/beta | bash
./configure -c infra
./infra.yml

使用的配置文件如下，你可以加更多节点，部署更多副本。

然后所有的东西都会自动为你设置好：

比如三个节点就是这个样子，三个都是独立副本可以独立使用。

Pigsty v4 目前还在 Beta 阶段，但 Victoria 这一部分已经非常稳了，剩下的主要是 Dashboard 优化和文档编写。如果你想要尝鲜 Victoria 全家桶，这也许是最简单的方式。

Pigsty v4.0 正式版预计在 2026 年1月发布，届时会有更完整的文档和更多新特性介绍。有兴趣尝鲜的朋友可以先玩玩，有问题欢迎反馈。后续的版本中，也会添加 Victoria 原生分布式的支持。

写在最后

这次升级到 Victoria 全家桶，老冯自己也是受益者。每次打开 Grafana 看监控，那种丝滑的感觉，真的会让人心情愉悦。以前那种点一下要等好几秒的日志查询体验，现在回想起来简直是折磨。

VictoriaMetrics 这个项目，代表了开源软件一种很纯粹的形态 —— 一个技术大神凭借极致的工程能力，做出了吊打行业巨头的产品，然后用最宽松的许可证分享给全世界。没有风投压力，不玩 License 变脸，就是踏踏实实做产品，用实力吊打所有友商。这种项目，值得被更多人知道和使用。

2 - 让PG停摆一周的大会：PGCon.Dev 2024 参会记

PGCon.Dev 的前身是 PGCon —— 最知名的 PostgreSQL Hacker 年度聚会，也可以说是决定 PostgreSQL 未来的一场会。从 2007 年成立以来，一直都是在加拿大渥太华举办至今。 这次会议有些特殊，原来的主办者 Dan 交班给下一届大会组织者，举办地点也转移到了温哥华市的 SFU 港区活动中心，算是新班组开门红第一次大会，自然更为隆重。

全都来参会了，谁还在写代码？

有多隆重呢？PG 核心组的 Peter Eisentraut 在会后做了一个统计，在这次 PGCon.Dev 期间 PostgreSQL 一次代码提交都没有发生，出现了二十年来持续时间最长的停摆 —— 整整六天半！为啥，因为开发者全都来参会啦！

考虑到前几次中断都发生在二十年前的项目早期……

虽然我已经拥抱 PostgreSQL 十年了，但线下现场参加全球 PG Hacker 们的会议还是第一次，所以我非常感谢组织团队为组织这次活动所做的工作。

PGCon.Dev 2024 已经于5月31日晚正式结束，理论上本文章本应在大会闭幕时写就，不过在紧接着探索温哥华与班夫国家公园的旅途中，我确实在高密度的旅途中把这件事不厚道地搁置了 那么今天就补上参会的见闻与记录吧。

第零天：扩展生态峰会

大会的第零天是领导层会议，我注册了下午的 Extension Ecosystem Summit 扩展生态峰会。

说起来，这个扩展生态峰会也许跟我还有点关系。两个月前我写了一篇文章《PostgreSQL正在吞噬数据库世界》，主题是 PostgreSQL 的繁荣扩展生态是其独一无二的特点与成功的关键要素。 写完后将其翻译成了英文《Postgres is eating the database world》发到了 Medium 与 HackNews 上，总共有几十万的阅览量，基本应该覆盖了整个 PG 社区。

此前，扩展机制的重要性并没有达成共识，即使在 PG 社区与一些资深成员的眼中，关于扩展他们只是觉得 PostGIS 和 PGVector 好像很不错 —— 前者是地理空间数据库的事实标准，后者是AI领域当红炸子鸡 —— 向量数据库的砸盘掀桌者。 但 PG 生态中强大的扩展绝不仅仅只有这两个，在抛出了这个极为繁荣的 PG 扩展生态 Landscape 后，立即引起了社区成员的极大兴趣与关注，很快关于PG扩展的讨论发酵了起来。

在这次扩展峰会之前，PG 社区已经举办了六次迷你扩展峰会对此事进行了密集的讨论，六位主讲嘉宾兼主持人在最近两个月中，从不同的角度介绍了关于扩展生态的建设工作，并阐述了对 PG 扩展生态发展的愿景，会议的视频回放可以在 Youtube 上看到。

在这次大会中，有许多与扩展生态，可扩展性有关的议题，甚至还有一个专门的扩展峰会，也许确实是有点关系的。这场扩展峰会分了上下两场，每场都有几个 Topic，大家挑选感兴趣的主题参与。 我挑了 David Wheeler 的 Binary Packing 主题分会参与讨论，另外四个参与者是 PGDG Yum 仓库维护者 Devrim，Debian 仓库维护者大法师 Tomasz Rybak，以及 Neon 的 PG 主要贡献者 Andreas Scherbaum。都是些老前辈，好在我也算是 YUM/APT 仓库的建设者/维护者，能实质参与到讨论中。

在上半场，来自 Temob 的 David 一直想做一个 PGXN v2，作为 PG 生态扩展分发的标准，搞一些 OCI 构建扩展的花活。当然，现有事实标准的维护者 Devrim 和 Tomasz 肯定是不乐意的。我支持这两位老爷子，毕竟我做的是 PG 发行版，内核组的活儿跟我直接关系不大，但 YUM/APT 仓库的负责人跟我的关系最紧密，RPM / Deb 包分发扩展已经是一种相当成熟可靠的方式了，整 OCI 这些我个人觉得意义不大。

下半场，我参加了 Omnigres 创始人 Yurii Rashkovskii 主持的 Extension in Core 分会场，讨论了关于扩展目录结构，元数据，命名冲突，版本控制，二进制分发的一些想法。并且和负责 PG RPM 仓库的 Devrim 老爷子聊了很多关于扩展的问题。

在扩展峰会后，Devrim 打出 “Keith粉丝团” 的 Slogan

第一天：主题分享与酒吧社交

PGCon.Dev 最核心的部分当然是大会议题，在 PG大会2024开幕 中我已经选定了感兴趣的主题，绝大多数分享都没有让我失望 —— 比起国内各种 XX 大会无聊的产品宣介，无关痛痒的技术细节与架构分享，PGConf.Dev 的分享要有趣且扎实得太多了。

5月28号 / 周二举行了 PostgreSQL 开发者与领导层闭门会议，以及 PGCon.Dev 扩展生态峰会。大会正式的议程在周三，也就是 29 号开始。

开场由 Jonathan Katz 与 Melanie Plageman 主持，前者是 PG 核心组七人之一，AWS RDS 的首席产品经理；后者是新近成为 PG 提交者，来自微软的罕有的女性PG开发者。当然，开幕式上最精彩的一幕，就是发现了著名 xz 后门的 “英雄开发者” Andres Freund 被拉上了台，披上了超级英雄的披风。

开幕式之后就开始了常规 Session Track，目前还没有会议的视频放出，但我相信以加拿大的办事效率，“用不了多久” 就能在 Youtube 上看到了。大部分的 Session 都是三选一的。我选了几个场次，下面是摘要：

将扩展的边界推向新边疆

第一场来自 Yurii ， “将 PG 扩展的边界推向新边疆”。讨论的内容其实是 PostgreSQL 应该提供什么样的扩展 API ？ PostgreSQL 有着极佳的可扩展性，但这一套 API 已经是十几年前 9.x 留下的了。尤里的提议旨在解决现有扩展机制的一些问题。比如：如何同时安装两个不同版本的扩展插件？如何避免一部分扩展插件安装后需要重启数据库的问题？如何让PG像管理数据一样管理扩展？如何处理扩展的依赖关系？

Yurii 和 Viggy 创办了 Omnigres ，旨在让 PostgreSQL 直接成为一个应用开发平台（比如直接在数据库里跑 HTTP 服务器等任务）。为了做到这一点，他们为 PostgreSQL 设计的一套新的扩展 API 与管理机制。我认为一些改进很有创新性，是 PG 内核扩展机制的前沿探索实践。PDF地址

我和 Viggy 与 Yurii 聊的非常愉快，Yurii 手把手带我编译安装试用了一把 Omni。而我也准备在下一个 Pigsty 的版本中，就加入对 Omni 系列扩展的支持，让这个强大的应用开发框架开箱即用。

数据库中的无政府状态

第二场分享来自学术界（CMU），师从网红教授 Andy Pavlo 的阿比盖尔·金，主题为：数据库中的无政府状态 —— 数据库管理系统可扩展性的调查与评估。我对这个主题非常感兴趣，因为 Pigsty 将 PG可扩展性为首要价值主张，收录了 255 个扩展插件，而 Kim 的这个研究带来了一些有趣的发现。

例如，PostgreSQL 是可扩展性最强的 DBMS，在全部十项扩展点上支持其中九种（紧随其后的是 DuckDB，和PG同为我最看好的两款DBMS）。根据 Kim 的统计，PG 生态有 375+ 可用扩展，远远甩开其他数据库一个数量级。

更有价值的是，Kim 定量分析了这些扩展之间的兼容性水平，得到了一个兼容性矩阵，并有一些有趣的发现 —— 例如，最为强大的 TimescaleDB 和 Citus 最容易与其他扩展发生冲突。而这样的信息对于用户与发行版维护者来说是非常有价值的。

我跟 Kim 聊天时打趣说，你的这个研究大大的好 —— 可以有理有据地用数据说话，宣称 PostgreSQL 可扩展性天下无敌了。PDF在线地址

PostgreSQL是如何被误用与滥用的

下午的第一场，我听了来自 CrunchyData 的 Karen Jex 的分享，这是少有的来自用户（一位DBA，还是女性DBA，确实非常罕见），而不是开发者的分享。Karen 分享了一堆 PG 初学者会犯的可笑错误。Karen 的分享并没有什么我不知道的新知识，但确实让我确信了 —— 世界哪儿的初学者都一样，都会犯这些可笑的错误。但这样的视角对于 PG Hacker 来说确实是很新鲜的，很多大佬都听得津津有味。

PostgreSQL与人工智能生态

下午的第二场，Bruce Momjian 分享了这个主题。Bruce 是 PGDG 发起人，从一开始到现在一直都是 PG 核心委员，也是中国各种 PG 会议的老熟人与常客了。

我本来以为分享的内容会是介绍一下PG生态的向量数据库扩展，或者类似 PGML，PG4ML 这样的机器学习扩展插件，结果竟然是如何利用 PostgreSQL 的多维数组与查询，实现神经网络的推理与训练。这样的把戏很好玩儿，但我很早也折腾过，没啥实用价值。

和 Bruce 吃饭聊天的时候也提到这个话题，Bruce 解释说，Jonathan Katz 为了介绍PG生态的向量数据库扩展 PGVector，需要一个话题来作为综述引子，于是就把 Bruce 拉壮丁过来灌水了，哈哈… PDF地址

让我看看，Bruce在偷偷写什么代码？竟然是 ArgParser

会后和 Bruce 聊了很多有趣的的话题，往年国内 PG 技术大会都喜欢请 Bruce 过来，或者远程做一个开幕分享。Bruce 说他很非常想再来中国，不过现在国际形势冲突加剧，美国大使馆发布的中国旅游风险等级太高，他也不敢过来了，让人遗憾。

构建PB级别的PostgreSQL部署

下午的第三场，我听了 Chris Travers 的分享：他们原来用 ElasticSearch 存数据，保留30天，数据量1PB，体验极差，基本上处于不可用状态，也难以维护。于是他们改用了一个水平分片的 PostgreSQL 集群完美地解决了问题 —— 总共存储了 10 PB 的数据。

通常来说出于各种因素，单机 PostgreSQL 的舒适区上界在几十TB ～ 几百TB 的数量级，PB 量级的部署我只听说过一例。即使是水平分片集群，10PB 量级也是极其罕见的了。尽管依然是中规中矩的分表/分布式实践，但数据量级确实让人印象深刻。PDF地址

临时加场：当数据库遇上新硬件

毫不夸张地说，这是本场最佳演讲，没有之一，也是我听过所有现场演讲中最富有激情与感染力的。演讲人 Margo Seltzer 是 UBC 的教授，以前是哈佛的教授，美国国家工程院院士，是数据库石破天祖师爷的亲传弟子，BerkeleyDB 的作者，她的老公也很有名，是 BSD / WiredTiger / nvi 的作者 Keith Bostic。

Margo 的演讲极具激情与感染力，并一针见血地指出了数据库领域面临的几个核心问题。例如，数据库的瓶颈已经不再是磁盘的IO性能，而是主内存的速度瓶颈。而硬件领域的趋势 —— HBM，CXL 也许是解决这些问题的答案，但具体怎么做，那就是在座的各位 PG Hacker 需要面对的挑战了。

在各种国内会议听多了院士，教授念经一样的分享报告，Margo 院士的演讲风格给我带来了耳目一新的感觉，并极大地感染鼓舞了我。大会视频放出后，我强烈建议各位可以听一听她的演讲。

酒吧社交活动

Margo 的分享结束后便是大会的官方 Social Event，在会场一街之隔的 Waterfront 车站里的 Rogue Kitchen & Wetbar， 位置极好 —— 窗外就是温哥华地标，太平洋海景。

大家可以随意交流，结识新老朋友。我和许多人都聊了很多有趣的话题，比如 Devrim，Tomasz，Yurii，Keith 等等等等。同样作为发行版/RPM维护者，我和 Devrim 老爷子尤其详谈甚欢，许多积存已久的问题也得到了想要的答案。

很多人都是老朋友了，难得相见一面，都聚在一起聊天。两杯啤酒下肚，许多人都打开心扉。再加上在场的都是 PG 同好，陌生人只要对个眼神也可以很轻松地聊起来。

于是在三四个小时的觥筹交错中，我和在场的 PG Hacker 基本都混了个熟脸。餐后 Melanie 喊我们去玩桌游，但我的英语做个专业演讲还行，但还没好到和 Native Speaker 玩猜词游戏和狼人杀的程度，甚是遗憾。

第二天的主题与活动

PostgreSQL 线程模型

第一天晚上的社交活动预热完，第二天大家就热情熟络得多了。今天的分享主题比较值得一提的是 “多线程 PostgreSQL”，成功做到了座无虚席。大家都很关注 Heikki 发起的这场讨论。Heikki 介绍了PG进程模型与线程模型的利与弊，详细实现路径与当前进展。

线程模型的收益有不少：更便宜的连接（等于内置连接池），共享的关系缓存，计划缓存，动态调整共享内存区域的能力，修改配置无需重启，Vacuum可以更加激进，运行中的 Explain Analyze，方便地限制每条连接的内存使用。但以 Tom Lane 为首的反对的声音也不小：这样可能会引入大量 Bug，丧失多进程模型隔离性的优势，以及 —— 引入大量的不兼容性，许多扩展都需要针对新的模型重写修改。

Heikki 提出了目标与相当详细周密的计划供在座的 Hacker 们评审 —— 在五到七年内，完成到线程模型的转换，最终目标是没有中间状态。有趣的是，Heikki现场在 PPT 里引用了最大反对者 Tom Lane 的一段评论：“从历史上看，我认为这会是一场大灾难，导致大量代码悄无声息的崩坏，让事情脱离我们的控制”。

虽然被当场揶揄，但这一次 Tom Lane 现场听着也是慈祥微笑，并没有直接表示反对。而最大的反对声音不是来自 Tom Lane，而是扩展维护者，一位维护了好几个扩展插件的老爷子问到扩展兼容性怎么办？（主要是分配/使用内存的方式）Heikki 表示只能要求这些扩展作者在五年左右的过渡阶段中国呢里重写修改适配新的模型。气得这位老爷子直接愤而离场出去了。

鉴于线程模型对现有扩展生态的巨大冲击，我对这件事并不看好。我也与 Heikki 和 Tom lane 以及其他 Hacker 聊了一下关于线程模型的观点，总的来说，社区持谨慎观望态度。目前的进展也仅仅是在 PG 17 中重构了与 fork exec 有关的调用代码，并标记出使用的全局变量以便后续修改。即使真得发生，那也至少是 PG 2x 的事了。

走廊社交与大厅闲聊

第二天议题场次比第一天稍微水了一点点，所以更多的人参加的是 “Hallway Track”，就是在走廊大厅里和别人聊天。作为一个 i 人，我其实蛮不擅长这种场合的，但现场热烈的氛围很快就感染了我。再加上昨天晚上的酒吧社交环节大家也混了个脸熟，所以也算轻车熟路了。

在这样的场合中，想要开启一场和陌生人的对话，其实非常简单。你也不需要主动搭讪或者咋样，就只要眼神接触一下，对话就自然而然地触发了。给我的感觉和打 RPG 游戏一样，按下空格触发 NPC 对话。然后自我介绍一下，说说自己干嘛的，这不是就顺便把 Pigsty 广告到 PG 社区的每一个角落啦？

作为第一次现场参加 PGCon.Dev 的人，我很惊讶地发现自己有着于与新人不匹配的知名度与关注度。有近半的参会者看到我的胸牌 Vonng / Pigsty 就认识我了 —— 主要还是归功于我之前写的那篇 PG 大爽文《PostgreSQL is eating the Database world》，Jonathan 跟我吐槽到说最近这篇文章天天出现在他的时间线上，整个 PG 社区的人基本上都看过了。

拍照基集：Tom, Bruce, Jonathan, Andres, Robert Hass, Devrim, Scherbaum, Heikki, Keith,…

当然说起社交，最简单粗暴的诀窍就是礼多人不怪 —— 我准备了一盒胸针，PostgreSQL 的吉祥物 Logo Slonik，镀金的，还带个小亚克力盒子。我给每一个和我聊天的 PG Hacker 都送了一个，这个胸针成为了本次大会备受欢迎的抢手货。好多人都在胸前或者会牌上别上了，而没拿到的人就在问：咦你们这个在哪里拿的，是贡献者奖励吗？

Bruce 对这个胸针爱不释手，说：“哎呀这个精巧的徽章真是太可爱了，一看就不是那种便宜货”（但其实镀金其实不贵的），然后我就又送了他一个。总之，礼多人不怪，靠着 Pigsty，PG爽文，和 Slonik 小胸针，也算是在 PG 大会上吃开了。

小聚：多国社区会餐

中午，瀚高做东，把美国PG社区，欧洲PG社区，还有日本PG社区的几位头面参会者拉到一起聚餐，一家温哥华的广东菜馆。图中从左前开始逆时针顺序分别是，瀚高北美研究院的 Grant Zhou，瀚高创始人苗健，欧洲PG用户组/ Neon 的 Andreas Scherbaum，有PG核心组/ 美国EDB 的 Bruce Momjiam，荣誉退休的前核心组成员 / pgEdge 的 Jan Wieck，制作各种PG贡献者硬币，社区周边的 Mark Wong，以及日本社区的 Tatsuro Yamada （山田達郎）与 Kyotaro Horiguchi （堀口恭太郎），最后是我。

在饭桌上我们聊了各种各样的话题，坐在我边上的两位日本 PG 社区友人很有趣，堀口桑是一位 PG 核心贡献者，在 WAL 复制 / 多字节字符串处理上有很多贡献，还是 pg_hint_plan 的作者。另一位山田桑也是 PG 贡献者，对 Pigsty 很感兴趣，在本次大会上进行了题为 《索引建议不受待见，但很管用》的分享。

Mark Wong 也是 PG 社区的主要贡献者，PGUS 的组织者，开发了一系列 PG 监控扩展，但更有趣的是他还负责 PG 社区的周边，贡献者硬币，衣服，贴纸，还有这个特别可爱的毛线团小象也是他自己手工缝制的，让人爱不释手。据说他上次做的公仔在 PG Conf US 被人顺走了哈哈，所以这次看得可牢了。

Bruce 是 PG 中文社区的老朋友了，上面介绍过了；来自德国的 Andreas Scherbaum 是欧洲 PG 大会的组织者，我们一起参加了扩展峰会的 Binary Distribution 讨论，也邀请我们到时候去参加；瀚高是唯一一个出现在 PGCon.Dev 的中国数据库厂商，苗总从山东飞过来参加，也跟我们分享了一些国产数据库的故事与密辛。

在回会场的路上，我和 Jan Wieck 聊了很多，他是老一代光荣退休的 PG 核心组成员，也是 PL/pgSQL，PL/TCL，外键，视图，规则系统，TOAST，BGWriter，统计进程这些耳熟能详的PG核心功能的作者，他也是 Slony 的作者。路上我说我也想当一个 Major Contributor，他便与我分享了他参与 PG 贡献的故事：银行职员，工作需要用到数据库，就慢慢开始一步一步变成核心贡献者了 —— 他勉励我更多地参与到 PostgreSQL 贡献来，PG社区的未来就看你们年轻人的了。

让PG社区参与更有包容性

第二天下午与昨天一样，有一个无需三选一的特殊场次，主题是 社区建设，由 Robert Hass 主持。3 位新晋的 PG 提交者轮流分享他们成为提交者的历程，与遇到的挑战和问题，分别是：泽田正彦，阿米特·兰格特，梅兰妮·普拉格曼。总体上社区参与面临的几个主要挑战是：非英语母语者的参与问题，时区差异，带有情绪的电子邮件沟通。

Robert Hass 在会后的博客中提到：他真的很想看到更多来自印度与日本的人参与到 PG 的高级职位中来，因为这两个国家有着庞大的开发者社区，却没有核心组成员，高级职位代表性不足。

说老实话，听着有些五味杂陈，因为在包容性的议题中没有提到 中国，而强调的是日本与印度。但这也是确实是没有办法的事情，中国在国际社区参与上，确实做的很拉垮。中国有三四百款国产数据库，其中很多都是基于 PG 魔改换皮套壳的，但这么多公司与用户，总共也只出了一个 PG 贡献者（ ——拓数派的 Richard Guo，原来在 Pivotal，今年刚晋为 Committer）。

这次 PGCon.Dev ，中国过来参会的人，除了瀚高的四位就是我了，加起来正好一只手数过来。说来遗憾，中国技术界对 PostgreSQL 的认知水平与采纳程度仍然远远落后于全球，在生态上可能有10到15年的差距。要说语言挑战与障碍的问题，印度日本的英语口音也有不少沟通障碍，要说种族歧视啥的更是无稽之谈 —— 在场的华人面孔可绝对不算少。

那么到底是为什么呢？如果你选择关门自嗨，土法炼钢造手搓数据库，白嫖社区，不参与到全球社区中来，那么别人自然也不会待见你。我希望我的参与能够 “Bootstrap” 并改善这一情况，让更多中国的 PG 用户、开发者，产品、开源项目，被全球社区所熟知，接纳，承认，让中文世界的用户也有更多的社区参与。

闪电演讲

第二天下午的最后一个议程叫做： 闪电演讲。顾名思义，就是一个人只给5分钟，超时就立刻轰下来。大家都很干练，11 个主题整个才花了 45 分钟。在酒吧相谈甚欢的 Keith 分享了一些关于 PG Monitor 的改进，Peter Eisentraut 分享了关于 SQL 标准的跟进。当然，要说我最喜欢的分享，当属 Devrim Gündüz 关于 PG RPMs 的闪电演讲。昨天酒吧喝酒的时候，他神秘兮兮地说明天要放个大招，震撼全场，果不其然，在5分钟里讲完了 75 页的 PPT，气氛非常欢快～。

说起 PostgreSQL，尽管这是个开源软件，但也许 99% 的用户都是直接使用 “官方” 编译好的成品二进制软件而不是从源码编译。Pigsty 作为一个数据库发行版，我自己维护了34个 RPM 扩展插件，但还有一百多个扩展，和各种生态工具都是直接来自 Devrim 老爷子维护的 PGDG 官方仓库的，我深深知道这项工作的不易。 Devrim 在用自己的信用，为这个世界上最先进，最流行的数据库软件质量把好最后一道关口。

Devrim 要是想干坏事，那破坏力说不定比 xz 要大多了

Devrim 老爷子是一个很有意思的人，土耳其人，现居伦敦，还兼职酒吧 DJ 打碟，身上有一个 PostgreSQL Logo 的纹身，是 PGDG RPM 仓库的维护者。我跟他聊了一个多小时，了解了 PGDG 仓库的方方面面，讨论了许多问题。比如，一个扩展想弄进 PGDG 仓库里，一般需要什么条件，是什么流程。Devrim 说他会去关注 PGXN ，以及社区讨论，像最近最近大火的 pgvector 向量数据库扩展就是有人推荐给他，然后就收录进去了。我就白了一眼说：你看看推给你的那个人莫不是我…，哈哈哈哈。

说起来很有趣，在最近发布 Pigsty v2.7 中，我发现我维护的34个扩展里有4个 pgsql-http, pgsql-gzip, pg_net, pg_bigm 被纳入了进入了 PGDG 官方仓库。我一和 Devrim 提起这个事，他就笑眯眯地跟说我：我跑到你的 Pigsty 网站扩展列表上扒拉了一圈，发现有几个不错的，就弄进官方仓库了。我就问，我打包的那些不错的 RUST 扩展有没有机会弄进官方仓库里？他马上义正严辞地表态 —— 这些 Go 和 Rust 异端插件想也别想！但反正你不是自己弄了个 YUM / APT 软件仓库专门放这些扩展吗？

我和 Devrim 聊得非常尽兴。最后我答应当一个 PG扩展猎手，发掘新的 PG 插件。如果觉得不错就交给他，收纳进 PG 的官方仓库里。

第三天：Unconference

如果说 PGCon.Dev 最精髓特色的节目是什么，那一定是 Unconference （自组织会议）。Unconference 没有预设的议程，而是由参与者在现场决定讨论的主题。

大会第三天全天的议程都是 Unconference，Joseph Conway 主持了 Unconference Organization 议程，愿意讲的人上去提交自己想介绍的主题，然后大家投票。当然我也上去提交了一个 Built-in Prometheus Metrics Exporter 的主题。

提交完后，每个议题主讲人上台简介自己的 Topic，并尽可能合并同类项，我的话题不出意料地被合入 Jeremy 发起的 Observability 主题里了。接下来就是大家投票选出感兴趣的演讲。排名前三的主题是： 多线程（42票），可观测性（35票），增强社区参与（35票）。 最后选出的主题如下：

看得出来，大家都非常重视可观测性上的特性。在 PostgreSQL 可观测性上，我确实是当仁不让的专家，pg_exporter 就是我写的。所以我抛出来的议题是：为 PostgreSQL 添加一个第一方的监控扩展，内置 Prometheus 监控端点，直接通过 HTTP 对外暴露监控指标。

提出这个问题的原因是，pg_exporter 虽好，但毕竟是外部组件，会引入额外的管理复杂度；而且如果 PostgreSQL 处于崩溃恢复无法接受新连接的状态，外部组件也难以知道内部的状态，只有将这个功能做成内核扩展，才能真正完整地提取这些信息。

实现的方案是采用类似 bgw_replstatus 扩展使用的后台工作进程，监听一个额外端口，通过 HTTP 对外暴露监控指标。内容上基本上也以我编写的 pg_exporter 作为蓝本，除了少量系统关键指标外，所有指标都通过一张 Collector 配置表进行定义。

这个想法得到了一些在座 PG Hacker 的关注。EDB ，CloudNativePG 的一些开发者也开始评估 pg_exporter 能否直接用在他们的发行版中，作为监控解决方案的一部分。现场所有对可观测性感兴趣的成员成立了一个 Observability SIG，并通过邮件列表进行后续的讨论。

议题：关于对龙芯提供支持

在大会最后两天中，我还与几位 PG Hacker 讨论了一些关于国产芯片，国产操作系统，中文字符集相关的中国特色数据库议题。

在之前发出的问题征集中，PG分会的类总提出了一个很好的建议，能不能让 PGDG 全球仓库支持龙芯 LoongArch 架构？国产芯片和国产操作系统厂商很乐意赞助这样的构建环境。带着这个问题，我询问了 RPM 仓库维护者 Devrim 老爷子；以及 Debian 侧的 Tomasz Rybak （备注：PGDG APT 仓库的维护者是 Christoph Berg，但 Tomasz 维护了 Debian 仓库中许多 PostgreSQL 相关的软件包），看看有没有可行性。

不过可惜的是，目前龙芯架构对于PG社区构建二进制使用的 OS 还没有提供支持 —— 例如 EL 系的 CentOS 7，Rocky 8，Rocky 9 ，以及 Debian 10/11/12 都无法在龙芯上运行。所以 Devrim 老爷子的回答是 No：而且构建的 Pipeline 必须在他们自己的机器和环境上可以跑起来，所以赞助云服务器的方式可能是走不通的。Tomasz 对于这个问题持开放态度，因为据说后面龙芯可能会支持 Debian13 ，那么就可以考虑把一些 PG 包的支持加进来。

总的来说，让 PG 官方 RPMs 支持龙芯架构估计没戏，但 APT 还是有可能的。但要龙芯支持主流开源社区 Linux OS Distro，这个事才有可能；如果是龙芯 + 一堆国产操作系统，那想都不要想，100% 没戏。

议题：关于服务端中文字符集支持

Jeremy Schneider 在本次大会带来一场关于字符排序规则（ Collation） 的分享，我非常关注。这个分享抛出了一系列 Collate 规则变化导致的问题。说实话，我以前也专门写过一篇文章研究过这个问题，最终结论与 Jeremy 高度一致，应该用 C.UTF8 ，我一直是这么做的，并制定开发规约，也在发行版中强制默认配置推行这一点，而 Jeremy 的分享，则详细阐述了不这么做会导致哪些坑爹的结果。

会后在大厅里，我和 Jeremy 进一步地讨论了这个问题，核心组的 Peter Eisentraut 也参与了进来。Jeremy 问我中国用户是怎样使用字符集与 Collation 的，我说新应用大体上都用的是 C.UTF8，通常只有一些政企单位和传统行业的老系统才会去折腾服务端中文字符集。

但这里确实有一个略尴尬的问题，例如 2023 年底中国发布的国标 GB18030 对信息系统提出了两条强制性要求：产品可以正确输入、输出、处理 GB18030 强制部分规定的全部汉字字符；产品可以正确识别 GB18030 强制性部分规定的全部汉字字符对应的编码。

PostgreSQL 可以在 客户端 支持 GB 18030 编码，也提供了 convert_to 将字符串编码为 GB18030 编码字节串的编码方案支持，但是不支持直接在服务端使用此编码（支持的是 EUC_CN），也没有通过 ICU 提供对 GB 18030 的支持。此外，我还向 Peter 提出了 convert_to + gb18030 大概有 20个新增汉字映射有误的问题。Jeremy 和 Peter 都表示会进一步跟进研究，看看怎么解决这些问题。

闭幕式

自组织会议聊完后，大会也进入到了最后的尾声。 Jonathan Katz 与 Melanie Plageman 主持了闭幕式。确实是一场非凡的大会，让人意犹未尽。

明年的 PGCon.Dev 2025 也会在加拿大举办，可能在温哥华，多伦多，渥太华或蒙特利尔四者之一。今年摸清楚了大会的调性与流程，我想明年可以去上台分享一个关于 Pigsty 或者 PG 可观测性的话题了。

顺带一提，参会的效果确实很明显，参会后，Pigsty 的海外下载 CDN 流量（这还只是一家云上的一部分）出现显著增长，打掉了我接近大几百G 的流量。更多的国际友人了解到了国内的 PostgreSQL 数据库发行版 Pigsty，哈哈。

参会后来自海外的 CDN 流量有了一波暴增

大会的 PPT 一部分已经开放下载。当然，对 PostgreSQL 与 Pigsty 感兴趣的朋友也可以微信搜索 pigsty-cc 加群直接下载或参与讨论。以及，下面是一些 PGCon.Dev 相关的博客与文章：

• Andreas Scherbaum PostgreSQL Development Conference 2024 - Review

• PgCon 2024 Developer Meeting

• Robert Haas: 2024.pgconf.dev and Growing the Community

• How engaging was PGConf.dev really?

• Cary Huang: PGConf.dev 2024：在温哥华塑造 PostgreSQL 的未来

• PGCon.Dev 扩展生态峰会小记 @ 温哥华

• PG大会2024开幕，温哥华饭搭子驴友团呢？

3 - PostgreSQL 宏观查询优化之 pg_stat_statements

在线业务数据库中，慢查询不仅影响终端用户体验，还会浪费系统资源、拉高资源饱和度、导致死锁和事务冲突，增加数据库连接压力，导致主从复制延迟等问题。因此，查询优化 是 DBA 的核心工作内容之一。

在查询优化这条路上，有两种不同的方法：

宏观优化：整体分析工作负载，对其进行剖分下钻，自上而下地识别并改进其中表现最糟糕的部分。

微观优化：分析并改进一条特定的查询，这便需要记录慢查询日志，掌握 EXPLAIN 的玄机，领悟执行计划的奥妙。

今天我们先来说说前者，宏观优化 有三个主要目标与动机：

减少资源消耗：降低资源饱和的风险，优化CPU/内存/IO，通常以查询 总耗时/总IO 作为优化目标。

改善用户体验：最常见的优化目标，在OLTP系统中通常以降低 查询平均响应时间 作为优化目标。

平衡工作负载：确保不同查询组之间的资源使用/性能表现的比例关系得当。

实现这些目标的关键在于 数据支撑，但是数据从哪里来？

—— pg_stat_statements！

扩展插件：PGSS

pg_stat_statements，以下简称 PGSS ，是践行观宏之道的核心工具。

PGSS 出自 PostgreSQL 全球开发组官方之手，以第一方扩展插件的形式，随数据库内核本体一并发行，提供了跟踪 SQL 查询语句级别指标的方法。

PostgreSQL 生态中有许许多多的扩展，但如果说有哪一个是“必选”的，我必定会毫不犹豫的回答：PGSS。这也是在 Pigsty 中，我们宁愿“自作主张”，也要默认启用并主动加载的两个扩展之一。（另一个是用于微观优化的 auto_explain）

PGSS 需要在 shared_preload_library 中显式指定加载，并在数据库中通过 CREATE EXTENSION 显式创建。创建扩展后即可通过视图 pg_stat_statements 访问查询的统计信息。

在 PGSS 中，系统中的每一类查询（即抽取变量后，执行计划相同的查询）都会被分配一个查询ID，紧接着是调用次数，执行总耗时，以及各种其他指标，其完整模式定义如下（PG15+）：

CREATE TABLE pg_stat_statements
(
    userid                 OID,     -- （标签值）执行此语句的用户 OID（标签值）
    dbid                   OID,     -- （标签值）此语句所在的数据库 OID（标签值）
    toplevel               BOOL,    -- （标签值）此语句是否是顶层 SQL 语句（标签值）
    queryid                BIGINT,  -- （标签值）查询ID：标准化查询的哈希值（标签值）
    query                  TEXT,    -- （标签值）标准化查询语句的文本内容
    plans                  BIGINT,  -- （累积量）此语句被 PLAN 的次数
    total_plan_time        FLOAT,   -- （累积量）此语句花费在 PLAN 上的总时长
    min_plan_time          FLOAT,   -- （测量值）PLAN 的最小时长
    max_plan_time          FLOAT,   -- （测量值）PLAN 的最大时长
    mean_plan_time         FLOAT,   -- （测量值）PLAN 的平均时长
    stddev_plan_time       FLOAT,   -- （测量值）PLAN 时间的标准差
    calls                  BIGINT,  -- （累积量）此语句被调用执行的次数
    total_exec_time        FLOAT,   -- （累积量）此语句花费在执行上的总时长
    min_exec_time          FLOAT,   -- （测量值）执行的最小时长
    max_exec_time          FLOAT,   -- （测量值）执行的最大时长
    mean_exec_time         FLOAT,   -- （测量值）执行的平均时长
    stddev_exec_time       FLOAT,   -- （测量值）执行时间的标准差
    rows                   BIGINT,  -- （累积量）执行此语句返回的总行数
    shared_blks_hit        BIGINT,  -- （累积量）命中的共享缓冲区总块数
    shared_blks_read       BIGINT,  -- （累积量）读取的共享缓冲区总块数
    shared_blks_dirtied    BIGINT,  -- （累积量）写脏的共享缓冲区总块数
    shared_blks_written    BIGINT,  -- （累积量）写入磁盘的共享缓冲区总块数
    local_blks_hit         BIGINT,  -- （累积量）命中的本地缓冲区总块数
    local_blks_read        BIGINT,  -- （累积量）读取的本地缓冲区总块数
    local_blks_dirtied     BIGINT,  -- （累积量）写脏的本地缓冲区总块数
    local_blks_written     BIGINT,  -- （累积量）写入磁盘的本地缓冲区总块数
    temp_blks_read         BIGINT,  -- （累积量）读取的临时缓冲区总块数
    temp_blks_written      BIGINT,  -- （累积量）写入磁盘的临时缓冲区总块数
    blk_read_time          FLOAT,   -- （累积量）读取块花费的总时长
    blk_write_time         FLOAT,   -- （累积量）写入块花费的总时长
    wal_records            BIGINT,  -- （累积量）生成 WAL 的记录总数
    wal_fpi                BIGINT,  -- （累积量）生成的 WAL全页镜像总数
    wal_bytes              NUMERIC, -- （累积量）生成的 WAL 字节总数
    jit_functions          BIGINT,  -- （累积量）JIT 编译的函数数量
    jit_generation_time    FLOAT,   -- （累积量）生成 JIT 字节码的总时长
    jit_inlining_count     BIGINT,  -- （累积量）函数被内联的次数
    jit_inlining_time      FLOAT,   -- （累积量）花费在内联函数上的总时长
    jit_optimization_count BIGINT,  -- （累积量）查询被 JIT优化的次数
    jit_optimization_time  FLOAT,   -- （累积量）花费在JIT优化上的总时长
    jit_emission_count     BIGINT,  -- （累积量）代码被 JIT Emit的次数
    jit_emission_time      FLOAT,   -- （累积量）花费在 JIT Emit上的总时长
    PRIMARY KEY (userid, dbid, queryid, toplevel)
);

PGSS 视图的 SQL 定义（PG 15+版本）

PGSS 也有一些局限性：首先，正在执行中 的查询语句并不会纳入这里的统计，而需要从 pg_stat_activity 中查看获取。其次，执行失败的查询（例如，因为 statement_timeout 超时被取消的语句）也不会被计入这里的统计 —— 这是 错误分析 要解决的问题，而不是 查询优化 所关心的目标。

最后，查询标识符 queryid 的稳定性需要特别注意：当数据库二进制版本和系统数据目录完全相同时，同一类查询会具有相同的 queryid （即在物理复制的主从上，同类查询的 queryid 默认是相同的），然而对于逻辑复制则不然。但用户不应当对这一性质抱有过度的依赖与假设。

原始数据

PGSS 视图中的列可以分为三类：

描述性的标签列（Label）：查询ID（queryid）、数据库 ID（dbid）、用户（userid），一个顶层查询标记，和标准化的查询文本（query）。

测量性的指标（Gauge）：与最小、最大、均值标准差有关的八列统计量，以 min，max，mean，stddev 作为前缀，以 plan_time 与 exec_time 作为后缀。

累积性的指标（Counter）：除了上面八列与标签列的 其他指标，例如 calls、rows 等，最重要、最有用的指标都在这一类里。

首先解释一下 queryid：queryid 是查询语句被解析后，剥离常量后生成规范化查询的哈希值，因此可以用来标识 同一类查询。不同的查询语句可能有着同样的 queryid （规范化后结构一样），同样的查询语句也可能有着不同的 queryid （例如因为 search_path 不同，导致实际查询的表不懂）。

同样的查询可能会在不同的数据库中被不同的用户所执行。因此在 PGSS 视图中，queryid，dbid，userid，toplevel 四个标签列，共同组成了唯一标识一条记录的“主键”。

对于指标列而言，测量性质的指标（GAUGE） 主要是执行时间与计划时间相关的八个统计量，然而用户没有办法很好地控制这些统计量的统计范围，所以实用价值并不大。

真正重要的指标是 累积性的指标（Counter），例如：

calls ：此查询组发生了多少次调用。

total_exec_time + total_plan_time：查询组累计耗费时间。

rows：查询组累计返回了多少行。

shared_blks_hit + shared_blks_read：缓冲池累计命中和读取操作次数。

wal_bytes：此组中的查询累计生成的 WAL 字节数。

blk_read_time 和 blk_write_time：累计花费在块读写IO上的时间

这里，最有意义的指标是 calls 与 total_exec_time，可以用于计算查询组的核心指标 QPS （吞吐量）与 RT（延迟/响应时间），但其他的指标也很有参考价值。

可视化展现 PGSS 视图的某个查询组快照

要解读累积性指标数据，只有某一个时刻的数据是不够的。我们需要对比至少两个时刻的快照，才能得到有意义的结论。

作为特例，如果您感兴趣的范围正好是从统计周期伊始（通常是启用此扩展时）至今，那么确实不需要对比“两个快照”。但用户感兴趣的时间粒度通常并不会这么粗放，而往往是以分钟、小时、天为单位。

根据多个 PGSS 查询组快照计算历史时序指标

好在类似 Pigsty 监控系统这样的工具会定期（默认每隔10s）截取头部查询（耗时Top256）的快照。有了许多不同类型的累积指标 M（etrics）在不同时刻的快照之后，我们就能计算出某个累积性指标的三种重要派生指标：

dM/dt ：指标 M 基于时间的微分，即每秒的增量。

dM/dc：指标 M 基于调用次数的微分，即每次调用的平均增量。

%M：指标 M 在整个工作负载中所占的百分比。

这三类指标正好与宏观优化的三类目标相对应，对时间的微分 dM/dt 揭示了 每秒资源使用量，通常用于减少资源消耗的优化目标。对调用次数的微分 dM/dc 揭示了 每次调用的资源使用量，通常用于改善用户体验的优化目标。而百分比指标 %M 展示了查询组在整个工作负载中所占的百分比，通常用于平衡工作负载的优化目标。

对时间微分

让我们首先来看第一类指标：对时间的微分。在这里，我们可以使用的指标 M 包括：calls，total_exec_time，rows，wal_bytes，shared_blks_hit + shared_blks_read，以及 blk_read_time + blk_write_time。其他的指标也有参考意义，但让我们从最重要的开始。

可视化展现对时间的微分指标 dM/dt

计算这些指标的方式其实很简单，我们只需要：

• 首先计算两个快照之间的指标值 M 的差值：M2 - M1
• 然后计算两个快照之间的时间差值：t2 - t1
• 最终计算 (M2 - M1) / (t2 - t1) 即可

生产环境通常会使用 5s，10s，15s，30s，60s 这样的数据采样间隔。对于负载分析通常会使用 1m， 5m，15m 作为常用的分析窗口大小。

例如，当我们计算 QPS 时，就会分别计算最近 1分钟，5分钟，15分钟的 QPS。窗口越长曲线就越平稳，更能反映长期变化趋势；但是会隐藏短期波动细节，不利于发现瞬时异常波动，所以不同粒度的指标需要结合来看。

展示特定查询组 1/5/15 分钟窗口下的 QPS

如果您使用 Pigsty / Prometheus 来采集监控数据，那么可以使用 PromQL 简单地完成这些计算工作。例如，计算所有查询最近1分钟的 QPS 指标，使用以下语句就可以了： rate(pg_query_calls{}[1m])

QPS

当 M 是 calls 时，对时间求导的结果是 QPS，它的单位是每秒查询数（req/s），这是一个非常基础的指标。查询 QPS 属于吞吐量指标，直接反应了业务施加的负载状况，如果一个查询的吞吐量过高（例如，10000+）或者过低（例如，1-），有可能是值得关注的。

QPS：1/5/15 分钟 µ/CV， ±1/3σ分布

如果我们把所有查询组的 QPS 指标累加起来（且没超过PGSS的收集范围），就会得到所谓的 “全局QPS”。另一种获得全局 QPS 的方式是在客户端打点，在类似 Pgbouncer 的连接池中间件上采集，或者使用 ebpf 探测。但都不如 PGSS 方便。

请注意，QPS 指标并不具备负载意义上的 横向可比性。不同查询组可能有着同样的 QPS，而单个查询的耗时却天差地别。甚至同一个查询组在不同时间点上产生的负载水平，也可能因为执行计划不同而发生巨大变化。每秒执行时长是一个更好的衡量负载的指标。

每秒执行时长

当 M 是 total_exec_time （+ total_plan_time，可选 ）时，我们就会得到宏观优化中最重要的指标之一：在查询组上耗费的的执行时间，有意思的是，这个导数的单位是 秒/每秒，所以分子分母相互约掉了，使得它实际上是一个无量纲的指标。

这个指标的涵义是：服务器每秒钟花费多少秒来处理这个查询组中的查询，例如 2 s/s 意味着服务器每秒花费两秒执行时间在这组查询上；对于多核CPU，这当然是有可能的：把两个CPU核的全部时间都拿来就行了。

每秒执行时长：1/5/15 分钟均值

因此这里的值也可以理解为一个百分比：可以超过 100%，在这种视角下，它是一个类似于主机 load1, load5, load15 的指标，揭示了该查询组产生的 负载水平。如果除以 CPU 核数，甚至可以得到归一化的查询负载贡献度指标。

但是我们需要注意的是，执行时间中包括了等待锁，等待I/O的时间。所以确实可能出现这样的情况：查询执行时间很长，但却没有对 CPU 负载产生影响。所以如果要精细分析慢查询，我们还要参考 等待事件 来进一步分析才行。

每秒行数

当 M 是 rows 时，我们会得到每秒该查询组返回的行数，单位是行/每秒（rows/s）。例如 10000 rows/s 意味着该类查询每秒向客户端吐出1万行数据。返回的行需要耗费客户端的处理资源，当我们需要检视应用客户端的数据处理压力时，这是一个非常有参考意义的指标。

每秒返回的行数：1/5/15 分钟均值

共享缓冲区访问带宽

当 M 是 shared_blks_hit + shared_blks_read 时，我们会得到每秒命中/读取的共享缓冲区块数，如果将其乘以默认块大小 8KiB（极少情况下有可能会是其他的大小，例如32KiB），我们就会得到一类查询“访问”内存磁盘的带宽：单位是字节/秒。

举个例子，如果某一类查询每秒访问50万次共享缓冲区，折合 3.8 GiB/s 的内部访问数据流：那么这就是一个显著负载，也许会是一个很好的优化候选项。也许你应该检查一下这个查询，看看它是否配得上这些“资源消耗”。

共享缓冲区访问带宽与缓冲区命中率

另一个值得参考的衍生指标是缓冲区命中率：即 hit / (hit + read) ，它可以用于分析性能变化的可能原因 —— 缓存未命中。当然，重复访问同一个共享缓冲池里的块，并不会真的重新读取，即使真的去读取，也不一定是读取磁盘，有可能是读内存中的FS Cache。所以这里只是一个参考值，但它确实是一个非常重要的宏观查询优化参考指标。

WAL日志量

当 M 是 wal_bytes 时，我们得到了该查询生成 WAL 的速率，单位是字节/每秒（B/s）。这个指标是在 PostgreSQL 13 新引入的，可以用来定量揭示查询产生的 WAL 大小：写入的 WAL 越多越快，刷写磁盘、物理复制/逻辑复制、日志归档的压力就会越大。

一个典型的例子是：BEGIN; DELETE FROM xxx; ROLLBACK; 。这样的事务删了很多数据，产生了大量 WAL 却没有执行任何有用的工作，通过这个指标可以将其揪出来。

WAL字节率：1/5/15 分钟均值

这里有两个注意事项：上面我们说过，PGSS 无法跟踪执行失败的语句，但这里事务虽然 ROLLBACK 失败了，但是语句却是成功执行了的，所以会被 PGSS 跟踪记录。

第二件事是：在 PostgreSQL 中并非仅仅是 INSERT/UPDATE/DELETE 会产生 WAL 日志，SELECT 操作也有可能产生 WAL 日志，因为 SELECT 可能会修改元组上的标记（Hint Bit）让页面校验和出现变化，触发 WAL 日志写入。

甚至存在这种可能，如果读取负载非常大，它会有较大概率导致 FPI 镜像生成，产生可观的 WAL 日志量。你可以通过进一步检查 wal_fpi 指标。

共享缓冲区写脏/写回带宽

对于 13 以下的版本，共享缓冲区写脏/写回带宽指标可以作为一个近似下位替代，用于分析查询组的写入负载特征。

I/O耗时

当 M 是 blks_read_time + blks_write_time ，我们会得到查询组花费在块 I/O 上的耗时比例，单位是 “秒/每秒”，与每秒执行时长指标一样，它也反映出一样操作占用的时间比例。

I/O 耗时对于分析查询毛刺原因很有帮助

因为 PostgreSQL 会使用操作系统提供的 FS Cache，所以即使这里执行了块读取/写入，可能在文件系统层面上仍然是发生在内存中的缓冲操作。所以它只能作为一个参考指标，使用时需要谨慎，需要与主机节点上的磁盘 I/O 监控相互对照。

对时间微分的指标 dM/dt，可以展现出一个数据库实例/集群内部工作负载的全貌，对于优化资源使用的场景来说尤其有用。但是如果您的优化目标是改善用户体验，那么可能另一组指标 —— 对调用次数的微分 dM/dc，会更有参考意义。

对调用次数微分

上面我们已经计算了六类重要指标对于 时间 的微分，另一类衍生指标计算方式是对 “调用次数” 进行微分，也就是分母从时间差变成了 QPS。

这类指标重要性相比前者甚至更高，因为它提供了直接关乎用户体验的几个核心指标，比如最重要的 —— 查询响应时间 （RT，Response Time），或曰 延迟（Latency）。

计算这些指标的方式也很简单，我们只需要：

• 计算两个快照之间的指标值 M 的差值：M2 - M1
• 然后计算两个快照之间的 calls 差值：c2 - c1
• 然后计算 (M2 - M1) / (c2 - c1) 即可

对于 PromQL 实现来说，对于 调用次数的微分指标 dM/dc，可以用“对时间的微分指标 dM/dt” 计算得到。例如要计算 RT，就可以使用 每秒执行时长 / 每秒查询数 ，两指标相除即可：

rate(pg_query_exec_time{}[1m]) / rate(pg_query_calls{}[1m])

dM/dt 可以用于计算 dM/dc

调用次数

当 M 是 calls 时，对自己微分没有任何意义（结果会恒为 1）。

平均延迟/响应时间/RT

当 M 是 total_exec_time 时，对调用次数求导的结果是 RT，或响应时间/延迟。它的单位是秒（s）。RT 直接反映了用户体验，是宏观性能分析中最重要的指标。这个指标的含义是：此查询组在服务器上的平均查询响应时间。如果条件允许启用 pg_stat_statements.track_planning，还可以加上 total_plan_time 一起计算，结果会更精确更具有代表性。

RT：1/5/15 分钟 µ/CV， ±1/3σ分布

这里要特别强调两种特殊情况：第一：PGSS不跟踪失败/执行中的语句；第二：PGSS的统计数据受（pg_stat_statements.max）参数限制，可能出现部分采样偏差。尽管有这些局限性，但想要获取至关重要的查询语句组延迟数据，PGSS 毫无疑问是最为稳妥可靠的来源。正如上面所述，在其他观测点位也有办法采集查询 RT 数据，但会麻烦得多。

你可以在客户端侧打点，采集语句执行时间，通过指标或者日志上报；你也可以尝试使用 ebpf 来探测语句 RT，这对基础设施和工程师要求会比较高。Pgbouncer 和 PostgreSQL （14+） 倒是也提供了 RT 指标，只可惜粒度都是数据库级别，没有一个能做到 PGSS 查询语句组级别的指标收集。

RT：语句级/连接池级/数据库级

不同于 QPS 这样的吞吐量指标，RT 是具有横向可比性的：例如某个查询组平时的 RT 都在1毫秒内，那么超过 10ms 的事件应当被视作严重的偏差进行分析。

当出现故障时， RT 视图对于定位原因也很有帮助：如果所有查询整体 RT 变慢，那么最有可能与资源不足有关。如果只是特定查询组的 RT 发生变化，那就更有可能是某些慢查询导致了问题，应当进一步调查分析。如果 RT 变化的时间点与应用发布部署吻合，则应当考虑是否要回滚这些部署。

此外，在性能分析，压力测试，基准测试时，RT 也是最重要的指标。你可以通过对比典型查询在不同环境（例如不同PG大版本、不同硬件、不同配置参数）下的延迟表现来评估系统的性能，并以此为依据不断对系统性能进行调整与改进。

RT 是如此重要，以至于 RT 本身又会衍生出许多下游指标来：1分钟/5分钟/15分钟的均值µ与标准差σ自然必不可少；过去15分钟的 ±σ，±3σ 可以用来衡量 RT 的波动范围，过去1小时的 95，99 分位点也很有参考价值。

RT 是评估 OLTP工作负载的核心指标，怎么强调它的重要性都不为过。

平均返回行数

当 M 是 rows 时，我们会得到每次查询 平均返回的行数，单位是行/每查询。对于 OLTP 工作负载来说，典型查询模式为点查，即每次查询返回几条数据。

按照主键查询单条记录，平均返回行数稳定为1

如果一个查询组每次查询向客户端吐出几百甚至成千上万行记录，那么应当对其进行审视。如果这是有意而为之的设计，比如批量加载任务/数据转储，那么不需要做什么。如果这是由应用/客户端发起的请求，那么可能存在错误，比如语句缺少 LIMIT 限制，查询缺少分页设计，这样的查询应该进行调整修复。

平均共享缓冲区读取/命中

当 M 是 shared_blks_hit + shared_blks_read 时，我们会得到每条查询“命中”与“读取”共享缓冲区的平均次数，如果将其乘以默认块大小 8KiB，我们就会得到这类查询每次执行的“带宽”，单位是 B/s：每次查询平均会访问/读取多少 MB 数据 ？

按照主键查询单条记录，平均返回行数稳定为1

查询平均访问的数据量通常与平均返回的行数相匹配，如果你的查询平均只返回了几行，却访问了成M上G的数据块，那你就需要特别注意了：这样的查询对于数据冷热状态非常敏感，如果所有的块都在缓冲区中，它的性能可能还说的过去，但如果从磁盘冷启动，执行时间可能会出现戏剧性的变化。

当然，不要忘记 PostgreSQL 双缓存问题，所谓“读取”的数据可能已经在操作系统文件系统层面被缓存过一次了。所以你需要与操作系统监控指标，或者 pg_stat_kcache ，pg_stat_io 这些系统视图相互参照进行分析。

另一种值得关注的模式是此指标的突变，这通常意味着该查询组的 执行计划可能出现了翻转/劣化，非常值得关注与进一步研究。

平均WAL日志量

当 M 是 wal_bytes 时，我们得到了每条查询平均生成 WAL 的大小，这是 PostgreSQL 13 新引入的字段。这个指标可以衡量查询的变更足迹大小，并计算读写比例等重要评估参数。

稳定的QPS却有着周期性WAL波动，可推断是 FPI 的影响

另一个用途是优化检查点/Checkpoint：如果你观察到此指标周期性的起伏（周期约等于 checkpoint_timeout），那么可以通过调整检查点间距，来优化查询产生 WAL 的数量。

对调用次数进行微分的指标 dM/dc，可以展现出一类查询的工作负载特性，对于优化用户体验来说非常有用。特别是 RT 乃是性能优化的黄金指标，怎样强调其重要性都不为过。

dM/dc 这样的指标为我们提供类似重要的绝对值指标，但如果想要找出哪些查询的优化潜在收益最大，还需要用到 %M 百分比指标。

百分比指标

现在我们来研究第三类指标，百分比指标。即某个查询组相对于整体工作负载所占的比例。

百分比指标 M% 为我们提供了某个查询组相对于整体工作负载的比例，帮助我们在频次、时间、I/O时间/次数上时识别出“主要参与者”，找出潜在优化收益最大的候选查询组，作为优先级评定的重要依据。

常用百分比指标 %M 一览

举个例子，如果某个查询组有 1000 QPS 的绝对值，看上去不少；但如果它只占整个工作负载的 3%，那么优化此查询的收益与优先级就没那么高了；反之，如果它占据了整个工作负载的 50% 还要多 —— 如果你有办法把它优化掉就可以砍掉整个实例吞吐量的半壁江山，优化它的优先级就会非常之高。

常见的优化策略是这样的：首先把所有查询组分别按照上面提到的重要指标：calls，total_exec_time，rows，wal_bytes，shared_blks_hit + shared_blks_read，以及 blk_read_time + blk_write_time 在一段时间内的 dM/dt 值进行排序取 TopN （比如 N=10 或者更多），加入优化候选列表中。

按照特定标准，选取待优化的 TopSQL

然后，对于优化候选列表中的每个查询组，依次分析其 dM/dc 指标，结合具体的查询语句与慢查询日志/等待事件进行分析，决定这是不是一个值得优化的查询。对于决定（Plan）进行优化的查询，就可以使用后续篇 “微观优化” 将要介绍的技巧进行调优（Do），并使用监控系统评估优化的效果（Check），总结分析后进入下一个 PDCA 戴明循环，持续进行管理优化。

除了对指标取 TopN 之外，还可以使用可视化的方式。可视化非常有助于从工作负载中识别 “主要贡献者”，复杂的判断算法可能还远比不上人类DBA对监控图形模式的直觉。想要形成比例感，我们可以借助饼图，树图或者堆叠的时序图。

将所有查询组的 QPS 进行堆叠

例如，我们可以使用饼图来标识过去1小时内耗时/IO使用最大的查询，使用二维树图（大小代表总耗时，颜色代表平均RT）来展示一个额外的维度。并用堆叠时序图来展示比例随时间的变化关系。

我们也可以直接分析当下的 PGSS 快照，按照不同的关注点进行排序，按照您自己的标准选择有待优化的查询即可。

I/O 耗时对于分析查询毛刺原因很有帮助

总结

最后，让我们对上面的内容做一个总结。

PGSS提供了丰富的指标，其中最重要的累积指标可以使用三种方式进行加工处理：

dM/dt ：指标 M 基于时间的微分，揭示了 每秒资源使用量，通常用于减少资源消耗的优化目标。

dM/dc：指标 M 基于调用次数的微分，揭示了 每次调用的资源使用量，通常用于改善用户体验的优化目标。

%M ：百分比指标展示了查询组在整个工作负载中所占的百分比，通常用于平衡工作负载的优化目标。

通常，我们会根据 %M ：百分比指标 Top 查询选择高价值的备选优化查询，并使用 dM/dt、dM/dc 指标进行进一步的评估，确认是否有优化空间和可行性，并评估优化后的效果。如此往复，不断循环。

理解了宏观优化的方法论后，我们就可以用这样的方法去定位优化慢查询了。这里给出了一个具体的 《 利用监控系统诊断PG慢查询》的例子。在下一篇中，我们将介绍关于 PostgreSQL查询 微观优化 的经验技巧。

参考

[1] PostgreSQL HowTO: pg_stat_statements by Nikolay Samokhvalov

[2] pg_stat_statements

[3] 利用监控系统诊断PG慢查询

[4] 如何用Pigsty监控现有PostgreSQL (RDS/PolarDB/自建)？

[5] Pigsty v2.5 发布：Ubuntu/Debian支持与监控改版/新扩展

[6] PostgreSQL监控系统Pigsty概述

4 - 如何用Pigsty监控现有PostgreSQL (RDS/PolarDB/自建)？

Pigsty 是一个开箱即用的 PostgreSQL 发行版，与本地优先的 RDS 开源替代。但它也可以单独作为一个 PostgreSQL/主机监控系统来使用。本文以阿里云为例，介绍了用一台 ECS 安装部署 Pigsty，并用于监控云上的 PolarDB 与 RDS for PostgreSQL，为现有数据库带来极致的观测能力。

嵌入媒体

快速上手

使用 Pigsty 教程分为四个步骤：

1.申请用于部署 Pigsty 的 ECS 服务器2.在 ECS 服务器上完整单机安装 Pigsty3.配置 PolarDB / RDS 的监控用户、模式、视图、黑白名单4.将 RDS / PolarDB for PG 接入 Pigsty 监控系统中

只要您有阿里云账号，使用按量付费模式（需要账户余额¥100以上），费用大致为 ¥4 / 小时，一小时内收工。

Why Pigsty

在讲 How 之前先说 Why。为什么要用一个额外的监控系统来做这件事？难道各家云厂商不是已经提供 RDS 监控了吗？没有错，只不过各家云厂商的 RDS PostgreSQL 的监控实在太简单了：这种程度的监控，对于回答数据库活着还是死了这件事也许足够了，但对于稍微高级一丁点儿的管理工作：性能优化，故障诊断都无能为力。

Pigsty 的监控系统便是为了解决这个问题而生的。Pigsty 提供了基于开源的 Grafana / Prometheus 现代可观测性技术栈做监控的最佳实践。整套系统同样被设计为一键拉起，开箱即用的 INFRA 模块。Pigsty 所管理的任何组件都会被自动纳入监控之中，包括主机节点，负载均衡 HAProxy，数据库 Postgres，连接池 Pgbouncer，元数据库 ETCD，KV缓存 Redis，对象存储 MinIO，……，以及整套监控基础设施本身。大量的 Grafana 监控面板与预置告警规则会让你的系统观测能力有质的提升。

Pigsty提供的监控面板概览\

无论是故障分析还是慢查询优化、无论是水位评估还是资源规划，Pigsty 为您提供全面的数据支撑，真正做到数据驱动。在 Pigsty 中，超过三千类监控指标被用于描述整个系统的方方面面，并被进一步加工、聚合、处理、分析、提炼并以符合直觉的可视化模式呈现在您的面前。从全局大盘总览，到某个数据库实例中单个对象（表，索引，函数）的增删改查详情都能一览无余。您可以随意上卷下钻横向跳转，浏览系统现状与历史趋势，并预测未来的演变。

对于现有的 PostgreSQL 数据库特别是 RDS 云数据库来说，虽然 Pigsty 拿不到主机监控数据，也没有高可用/连接池等组件的监控数据，也缺少原生的数据库日志。但能利用好 PostgreSQL 本身的监控指标，已经有着足够强大的力量了。

您可以查阅公开Demo：https://demo.pigsty.cc 来了解 Pigsty 监控系统提供的能力。

使用 Terraform 申请 ECS 服务器

单机安装 Pigsty 需要一台 x86_64 ECS 云服务器，EL 7-9操作系统（建议使用 Rocky 8.6/9.1），规格最小 1C2G 。您可以直接在控制台上申请资源，或者使用 Pigsty 提供的 Terraform 模板一键完成资源申请与置备。默认配置会使用固定的 10.10.10.10 IP地址与密码 PigstyDemo4，并分配一个公网IP以供访问。

在ECS上完整安装单机版 Pigsty

登陆 ECS 后，可以通过以下命令完成 Pigsty 的单机安装

bash -c "$(curl -fsSL https://get.pigsty.cc/latest)"   # 下载 Pigsty
cd ~/pigsty; ./bootstrap;                              # 提示下载离线软件包
./configure                                            # 配置 Pigsty
vi pigsty.yml  # 定制一些配置，比如修改各种密码
./install.yml  # 完成完整的单机安装，使用离线包完整安装约10分钟

Pigsty的详细安装过程介绍：https://pigsty.cc/doc/#/zh/INSTALL，整个过程耗时约十几分钟。

安装完成后，您可以通过 ECS 公网 IP 地址上的 3000 端口访问监控系统 Grafana，监控界面中会显示出完整的自我监控。例如本例中：http://59.110.161.154:3000 ，默认用户名密码为：admin / pigsty。

我们建议您使用域名/https访问 Pigsty 的 Web 界面，注意在生产环境使用时，我们强烈建议您修改默认密码，并谨慎对公网暴露界面。

配置 PolarDB / RDS PG 监控

PolarDB 的配置细节在此不再赘述，关键是要有一个给监控用户使用的连接串，可以从 ECS 上访问 PolarDB 主库 / 从库，并确保相关监控用户具有足够的读取权限，以及相关监控扩展已经完成安装。

•创建集群，确保 PolarDB 与ECS在同一个可用区/子网内，可以从ECS访问PolarDB。•创建集群账号：管理用户 dbuser_dba，高权限账号，用来对集群进行进一步的配置。•添加集群访问白名单：将 ECS 内网IP地址 10.10.10.10 添加到集群白名单中。•创建一个业务数据库，这里以 test 为例。

配置好数据库集群的账号、白名单、数据库之后，使用高权限管理用户连接至新创建的数据库

$ psql postgres://dbuser_dba:[email protected]:1921/postgres

使用高权限用户创建专用监控用户，当然您也可以在控制台创建。

强烈建议安装 pg_stat_statements 扩展，它可以提供非常重要的关于查询的

将现有PG数据库接入监控

为了将 RDS for PostgreSQL 实例与 PolarDB 实例纳入监控，您需要将这些目标实例的身份信息与连接信息告诉 Pigsty。编辑 pigsty.yml 配置文件，在 all.children.infra.vars.pg_exporters 定义这些待监控的远程数据库实例，这是一个字典，Key 为唯一分配的本地监控组件端口号，Value为目标实例的配置信息。

这里，我们接入了一个一主一从的 PolarDB 集群，并将其命名为 pg-polar，一个基础版（单节点）的 RDS for PostgreSQL 实例并命名为 pg-rds，以及一个高可用版本并带有一个只读节点的 RDS 集群 pg-rdsha。其配置如下所示，并不是所有参数都是必须的，通常来说，只有 pg_cluster，pg_seq，与 pg_host （pg_port，如果不是5432） 是需要修改的，其他参数可以按需指定覆盖。

定义好这些配置选项后，您可以使用以下命令，将其纳入到 Pigsty 的监控系统中：

    bin/pgmon-add pg-polar
    bin/pgmon-add pg-rds
    bin/pgmon-add pg-rdsha

然后，您就可以在 Pigsty 监控系统中看到这三个新集群了。

点击浅灰蓝色的集群名/实例名，即可跳转到对应集群/实例 PGRDS 监控面板上：

点击浅灰蓝色的集群名/实例名，即可跳转到对应集群/实例 PGRDS 监控面板上：

首屏包含了最为关键的信息，集群实例成员，存活状态，数据库列表与导航，13个核心监控指标。点击都可以展开更详细的信息。\

在 PGRDS Cluster 与 PGRDS Instance 之间，可以方便地在图表元素上点击跳转，快速上卷下钻。

同时，您依然可以复用 Overview / Database 层次的所有监控面板，查阅数据库内部的详细细节，比如每一类查询的 QPS / RT，或者每个表上的增删改查：

Pigsty的监控系统还允许您直接访问数据库的 Catalog （可选），从系统视图中查阅统计数据。例如锁等待 / TopSQL 等。

更多监控系统的细节就不在此展开了，欢迎访问 Pigsty 文档： https://pigsty.cc ，或者访问 Demo 在线体验：https://demo.pigsty.cc 。点击“查看原文”，访问 Bilibili 视频版教程。

如果您对 Pigsty 与 PostgreSQL 感兴趣，也欢迎微信搜索 pigsty-cc 添加 Pigsty 小助手，加入 PGSQL x Pigsty 交流群中。

5 - PG慢查询诊断方法论

You can’t optimize what you can’t measure

慢查询是在线业务数据库的大敌，如何诊断定位慢查询是DBA的必修课题。

本文介绍了使用监控系统 —— Pigsty诊断慢查询的一般方法论。

慢查询：危害

对于实际服务于在线业务事务处理的PostgreSQL数据库而言，慢查询的危害包括：

• 慢查询挤占数据库连接，导致普通查询无连接可用，堆积并导致数据库雪崩。
• 慢查询长时间锁住了主库已经清理掉的旧版本元组，导致流复制重放进程锁死，导致主从复制延迟。
• 查询越慢，查询间相互踩踏的几率越高，越容易产生死锁、锁等待，事务冲突等问题。
• 慢查询浪费系统资源，拉高系统水位。

因此，一个合格的DBA必须知道如何及时定位并处理慢查询。

图：一个慢查询优化前后，系统的整体饱和度从40%降到了4%

慢查询诊断 —— 传统方法

传统上来说，在PostgreSQL有两种方式可以获得慢查询的相关信息，一个是通过官方的扩展插件 pg_stat_statements，另一种是慢查询日志。

慢查询日志顾名思义，所有执行时间长于 log_min_duration_statement 参数的查询都会被记录到PG的日志中，对于定位慢查询，特别是 对于分析特例、单次慢查询不可或缺。不过慢查询日志也有自己的局限性。在生产环境中出于性能考虑，通常只会记录时长超出某一阈值的查询，那么许多信息就无法从慢查询日志中获取了。当然值得一提的是，尽管开销很大，但 全量查询日志仍然是慢查询分析的终极杀手锏。

更常用的慢查询诊断工具可能还是 pg_stat_statements。这事是一个非常实用的扩展，它会收集数据库内运行查询的统计信息，在任何场景下都强烈建议启用该扩展。

pg_stat_statements 提供的原始指标数据以系统视图表的形式呈现。系统中的 每一类 查询（即抽取变量后执行计划相同的查询）都分配有一个查询ID，紧接着是调用次数，总耗时，最大、最小、平均单次耗时，响应时间都标准差，每次调用平均返回的行数，用于块IO的时间这些指标类数据。

一种简单的方式当然是观察 mean_time/max_time 这类指标，从系统的Catalog中，您的确可以知道某类查询有 史以来平均的响应时间。对于定位慢查询来说，也许这样也算得上基本够用了。但是像这样的指标，只是系统在当前时刻的一个 静态快照，所以能够回答的问题是有限的。譬如说，您想看一看某个查询在加上新索引之后的性能表现是不是有所改善，用这种方式可能就会非常繁琐。

pg_stat_statements 需要在 shared_preload_library 中指定，并在数据库中通过 CREATE EXTENSION pg_stat_statements 显式创建。创建扩展后即可通过视图 pg_stat_statements 访问查询统计信息

慢查询的定义

多慢的查询算慢查询？

应该说这个问题 取决于业务、以及实际的查询类型，并没有通用的标准。

作为一种经验阈值，频繁的CRUD点查，如果超过 1ms，可列为慢查询。

对于偶发的单次特例查询而言，通常超过100ms或1s可以列为慢查询。

慢查询诊断 —— Pigsty

监控系统就可以更全面地回答关于慢查询的问题。监控系统中的数据是由无数 历史快照 组成的（如5秒一次快照采样）。因此用户可以回溯至任意时间点，考察不同时间段内查询平均响应时间的变化。

上图是Pigsty中 PG Query Detail提供的界面，这里展现出了单个查询的详细信息。

这是一个典型的慢查询，平均响应时间几秒钟。为它添加了一个索引后。从右中Query RT仪表盘的上可以看到，查询的平均响应世界从几秒降到了几毫秒。

用户可以利用监控系统提供的 洞察 迅速定位数据库中的慢查询，定位问题，提出猜想。更重要的是，用户可以 即时地 在不同层次审视表与查询的详细指标，应用解决方案并获取 实时反馈，这对于紧急故障处理是非常有帮助的。

有时监控系统的用途不仅仅在于提供数据与反馈，它还可以作为一种安抚情绪的良药：设想一个慢查询把生产数据库打雪崩了，如果老板或客户没有一个地方可以透明地知道当前的处理状态，难免会焦急地催问，进一步影响问题解决的速度。监控系统也可以做作为精确管理的依据。您可以有理有据地用监控指标的变化和老板与客户吹牛逼。

一个模拟的慢查询案例

Talk is cheap, show me the code

假设用户已经拥有一个 Pigsty沙箱演示环境，下面将使用Pigsty沙箱，演示模拟的慢查询定位与处理流程。

慢查询：模拟

因为没有实际的业务系统，这里我们以一种简单快捷的方式模拟系统中的慢查询。即 pgbench 自带的类 tpc-b 场景。

通过 make ri / make ro / make rw，在 pg-test 集群上初始化 pgbench 用例，并对集群施加读写负载

# 50TPS 写入负载
while true; do pgbench -nv -P1 -c20 --rate=50 -T10 postgres://test:test@pg-test:5433/test; done

# 1000TPS 只读负载
while true; do pgbench -nv -P1 -c40 --select-only --rate=1000 -T10 postgres://test:test@pg-test:5434/test; done

现在我们已经有了一个模拟运行中的业务系统，让我们通过简单粗暴的方式来模拟一个慢查询场景。在 pg-test 集群的主库上执行以下命令，删除表 pgbench_accounts 的主键：

ALTER TABLE pgbench_accounts DROP CONSTRAINT pgbench_accounts_pkey ;

该命令会移除 pgbench_accounts 表上的主键，导致 相关查询 从索引扫描变为顺序全表扫描，全部变为慢查询，访问 PG Instance ➡️ Query ➡️ QPS，结果如下图所示：

图1：平均查询响应时间从1ms飙升为300ms，单个从库实例的QPS从500下降至7。

与此同时，实例因为慢查询堆积，系统会在瞬间 雪崩过载，访问PG Cluster首页，可以看到集群负载出现飙升。

图2：系统负载达到200%，触发机器负载过大，与查询响应时间过长的报警规则。

慢查询：定位

首先，使用PG Cluster面板定位慢查询所在的具体实例，这里以 pg-test-2 为例。

然后，使用PG Query面板定位具体的慢查询：编号为 -6041100154778468427

图3：从查询总览中发现异常慢查询

该查询表现出：

• 响应时间显著上升： 17us 升至 280ms
• QPS 显著下降： 从500下降到 7
• 花费在该查询上的时间占比显著增加

可以确定，就是这个查询变慢了！

接下来，利用PG Stat Statements面板或PG Query Detail，根据查询ID定位慢查询的 具体语句。

图4：定位查询语句为 SELECT abalance FROM pgbench_accounts WHERE aid = $1

慢查询：猜想

获知慢查询语句后，接下来需要推断慢查询 产生的原因。

SELECT abalance FROM pgbench_accounts WHERE aid = $1

该查询以 aid 作为过滤条件查询 pgbench_accounts 表，如此简单的查询变慢，大概率是这张表上的索引出了问题。 用屁股想都知道是索引少了，因为就是我们自己删掉的嘛！

分析查询后， 可以 提出猜想： 该查询变慢是 pgbench_accounts 表上 aid 列缺少索引。

下一步，我们就要 验证猜想。

第一步，使用PG Table Catalog，我们可以检视表的详情，例如表上建立的索引。

第二步，查阅 PG Table Detail 面板，检查 pgbench_accounts 表上的访问，来验证我们的猜想

图5： pgbench_accounts 表上的访问情况

通过观察，我们发现表上的 索引扫描 归零，与此同时 顺序扫描 却有相应增长。这印证了我们的猜想！

慢查询：方案

假设一旦成立，就可以着手提出方案，解决问题了。

解决慢查询通常有三种方式：修改表结构、修改查询、修改索引。

修改表结构与查询通常涉及到具体的业务知识和领域知识，需要具体问题具体分析。但修改索引通常来说不需要太多的具体业务知识。

这里的问题可以通过添加索引解决，pgbench_accounts 表上 aid 列缺少索引，那么我们尝试在 pgbench_accounts 表上为 aid 列添加索引，看看能否解决这个问题。

CREATE UNIQUE INDEX ON pgbench_accounts (aid);

加上索引后，神奇的事情发生了。

图6：可以看到，查询的响应时间与QPS已经恢复正常。

图7：系统的负载也恢复正常

慢查询：评估

作为慢查询处理的最后一步，我们通常需要对操作的过程进行记录，对效果进行评估。

有时候一个简单的优化可以产生戏剧性的效果。也许本来需要砸几十万加机器的问题，创建一个索引就解决了。

这种故事，就可以通过监控系统，用很生动直观的形式表达出来，赚取KPI与Credit。

图：一个慢查询优化前后，系统的整体饱和度从40%降到了4%

（相当于节省了X台机器，XX万元，老板看了心花怒放，下一任CTO就是你了！）

慢查询：小结

通过这篇教程，您已经掌握了慢查询优化的一般方法论。即：

• 定位问题
• 提出猜想
• 验证假设
• 制定方案
• 评估效果

监控系统在慢查询处理的整个生命周期中都能起到重要的效果。更能将运维与DBA的“经验”与“成果”，以可视化，可量化，可复制的方式表达出来。

6 - Pigsty正式发布

今天我很荣幸的宣布，Pigsty正式发布了！

官方网站：https://pigsty.cc

Pigsty是什么？

• Pigsty是针对大规模PostgreSQL集群的监控系统

• Pigsty是高可用PostgreSQL集群的供给方案

• Pigsty基于开源生态构建，是免费的开源软件

Pigsty 针对大规模数据库集群监控与管理而设计，提供业界顶尖的PostgreSQL监控系统与开箱即用的高可用数据库供给方案。Pigsty基于开源生态构建，旨在降低PostgreSQL使用管理的门槛，为用户带来极致的可观测性与丝滑的数据库使用体验。\

Pigsty是监控系统

PostgreSQL是世界上最好的开源关系型数据库，但在其生态中却缺少一个足够好的监控系统。Pigsty即旨在解决这一问题：提供世界上最好的PostgreSQL监控系统，

开发Pigsty的初衷是：作者需要对一个大规模PostgreSQL集群进行管理，但找遍所有市面上的开源与商业监控系统方案后，发现没有一个是“足够好用”的。本着“我行我上”的精神，开发设计了本系统。

Pigsty的界面基于Grafana深度定制，由30+监控面板，上千+仪表盘，18万行JSON定制而成，涵盖数据库与基础设施的方方面面。

Pigsty提供近1200个监控指标，一骑绝尘，远超市面上现有的相关产品。提供从全局大盘汇总到某一个数据对象增删改查的全域数据支持。\

Pigsty是供给方案

Pigsty同时还是一个高可用数据库集群供给方案。

监控系统要想发行与演示，必须要先有被监控的对象。可许多用户自建的数据库实在是千奇百怪。所以这里，Pigsty项目决定将数据库供给方案作为项目的一部分发布。

将主从复制，故障切换，流量代理，连接池，服务发现，基本权限系统等成熟的生产级部署方案打包至本项目中，真正让用户做到 立等可取，开箱即用。

数据库供给方案所做的事情一言以蔽之：您填写一张表单，然后系统会自动根据表单的内容创建出对应的数据库集群。真正做到傻瓜式数据库管理。

Pigsty通过130+配置项定义了数据库与基础设施的方方面面，采用声明式的语法与幂等的执行机制，使用代码定义基础设施，在物理机与虚拟机上达到了与Kubernetes类似的舒爽体验，简单易用。

Pigsty是开源软件

Pigsty依托开源，回馈社区，是免费的开源软件。Pigsty基于 Apache 2.0 协议开源，但也提供 专业版 与 可选的商业支持服务。欢迎各位贡献ISSUE与PR，也欢迎捐赠与赞助。

Pigsty的监控系统基于开源组件Prometheus，Grafana，Alertmanager, Exporter进行深度定制开发。同时还包括Nginx, Dnsmasq/CoreDNS, NTP/Chrony, Consul/Etcd等基础设施。遵循业界监控最佳实践，可以方便地与已有监控基础设施集成。

Pigsty的供给方案基于流行的DevOps工具Ansible进行开发，部署涉及的组件包括：Postgres, Pgbouncer, Patroni, HAProxy, Keepalived。所有部署逻辑都以Ansible Role的方式编写，可以方便地进行集成、定制与二次开发。

PostgreSQL是世界上最先进的开源关系型数据库，而Pigsty旨在成为世界上最先进的开源关系型数据库的监控系统与供给方案。希望Pigsty能在各位使用PostgreSQL的过程中起到帮助。

Pigsty可以开箱即用

Pigsty提供了详实的中英文档供您参考。

更重要的是，Pigsty既提供了可公开访问的演示Demo，也自带了基于Vagrant的本地沙箱。您可以使用以下命令简单的在自己的笔记本上一键拉起带有数据库集群与监控基础设施的沙箱环境。

make up          # 拉起vagrant虚拟机
make ssh         # 配置虚拟机ssh访问
make init        # 初始化Pigsty
sudo make dns    # 写入Pigsty静态DNS域名（需要sudo,可选）
make mon-view    # 打开Pigsty首页（默认用户密码：admin:admin）

也可以在修改极少量配置后，使用完全相同的工作流初始化生产环境。

Pigsty的相关站点\

Pigsty提供了详实的中英文档供您参考。

中文站点：https://pigsty.cc

英文站点：https://pigsty.cc/en/

官方演示：http://demo.pigsty.cc

Github仓库：https://github.com/Vonng/pigsty

7 - 黄金监控指标：错误延迟吞吐饱和

前言

玩数据库和玩车有一个共通之处，就是都需要经常看仪表盘。

盯着仪表盘干什么，看指标。为什么看指标，掌握当前运行状态才能有效施加控制。

车有很多指标：车速，胎压，扭矩，刹车片磨损，各种温度，等等等等，各式各样。

但人的注意力空间有限，仪表盘也就那么大，

所以，指标可以分两类：

• 你会去看的：黄金指标 / 关键指标 / 核心指标
• 你不会看的：黑匣子指标 / 冷指标。

黄金指标就是那几个关键性的核心数据，需要时刻保持关注（或者让自动驾驶系统/报警系统替你时刻保持关注），而冷指标通常只有故障排查时才会去看，故障排查与验尸要求尽可能还原现场，黑匣子指标多多益善。需要时没有就很让人抓狂

今天我们来说说PostgreSQL的核心指标，数据库的核心指标是什么？

数据库的指标

在讲数据库的核心指标之前，我们先来瞄一眼有哪些指标。

avg(count by (ins) ({__name__=~"pg.*"}))
avg(count by (ins) ({__name__=~"node.*"}))

1000多个pg的指标，2000多个机器的指标。

这些指标都是数据宝藏，挖掘与可视化可以提取出其中的价值。

但对于日常管理，只需要少数几个核心指标就可以了。

可用指标千千万，哪些才是核心指标？

核心指标

根据经验和使用频度，不断地做减法，可以筛选出一些核心指标：

紧急情况下：错误是始终是第一优先级的黄金指标。

常规情况下：应用视角的黄金指标：QPS与RT

常规情况下：DBA视角的黄金指标：DB饱和度（水位）

为什么是它们？

错误指标

第一优先级的指标永远是 错误，错误往往是直接面向终端用户的。

如果只能选一个指标进行监控，那么选 错误指标，比如应用，系统，DB层的每秒错误日志条数可能最合适。

一辆车，只能选一个仪表盘上的功能，你会选什么？

选 错误指标，小车不停只管推。

错误类指标非常重要，直接反映出系统的异常，譬如连接池排队。但错误类指标最大的问题就是，它只在告警时有意义，难以用于日常的水位评估与性能分析，此外，错误类指标也往往难以精确量化，往往只能给出定性的结果：有问题 vs 没问题。

此外，错误类指标难以精确量化。我们只能说：当连接池出现排队时，数据库负载比较大；队列越长，负载越大；没有排队时，数据库负载不怎么大，仅此而已。对于日常使用管理来说，这样的能力肯定也是不够的。

定指标，做监控报警系统的一个重要原因就是用于预防系统过载，如果系统已经过载大量报错，那么使用错误现象反过来定义饱和度是没有意义的。

指标的目的，是为了衡量系统的运行状态。，我们还会关注系统其他方面的能力：吞吐量/流量，响应时间/延迟，饱和度/利用率/水位线。这三者分别代表系统的能力，服务质量，负载水平。

关注点不同，后端（数据库用户）关注系统能力与服务质量，DBA（数据库管理者）更关注系统的负载水平。

流量指标

流量类的指标很有潜力，特别是QPS，TPS这样的指标相当具有代表性。

流量指标可以直接衡量系统的能力，譬如每秒处理多少笔订单，每秒处理的多少个请求。

与车速计有异曲同工之妙，高速限速，城市限速。环境，负载。

但像TPS QPS这样流量也存在问题。一个数据库实例上的查询往往是五花八门各式各样的，一个耗时10微秒的查询和一个10秒的查询在统计时都被算为一个Q，类似于QPS这样的指标无法进行横向比较，只有比较粗略的参考意义，甚至当查询类型发生变化时，都无法和自己的历史数据进行纵向比较。此外也很难针对QPS、TPS这样的指标设置利用率目标，同一个数据库执行 SELECT 1 可以打到几十万的QPS，但执行复杂SQL时可能就只能打到几千的QPS。不同负载类型和机器硬件会对数据库的QPS上限产生显著影响，只有当一个数据库上的查询都是高度单一同质且没有复杂变化的条件下，QPS才有参考意义，在这种苛刻条件下倒是可以通过压力测试设定一个QPS的水位目标。

延迟指标

与档位类似，查询慢，档位低，车速慢。查询档次低，TPS水位低。查询档次高，TPS水位高

延迟适合衡量系统的服务质量。

比起QPS/TPS，RT（响应时间 Response Time）这样的指标反而更具有参考价值。因为响应时间增加往往是系统饱和的前兆。根据经验法则，数据库的负载越大，查询与事务的平均响应时间也会越高。RT相比QPS的一个优势是 ，RT是可以设置一个利用率目标的，比如可以为RT设定一个绝对阈值：不允许生产OLTP库上出现RT超过1ms的慢查询。但QPS这样的指标就很难画出红线来。不过，RT也有自己的问题。第一个问题是它依然是定性而非定量的，延迟增加只是系统饱和的预警，但没法用来精确衡量系统的饱和度。第二个问题通常能从数据库与中间件获取到的RT统计指标都是平均值，但真正起到预警效果的有可能是诸如P99，P999这样的统计量。

饱和度指标

饱和度指标类似汽车的发动机转速表，油量表，水温表。

饱和度指标适合衡量系统的负载

即用户期待的负载指标是一个 饱和度（Saturation） 指标，所谓饱和度，即服务容量有多”满“，通常是系统中目前最为受限的某种资源的某个具体指标的度量。通常来说，0%的饱和度意味着系统完全空闲，100%的饱和度意味着满载，系统在达到100%利用率前就会出现性能的严重下降，因此设定指标时还需要包括一个 利用率目标，或者说 水位红线、黄线，当系统瞬时负载超过红线时应当触发告警，长期负载超过黄线时应当进行扩容。

8 - PostgreSQL监控系统Pigsty概述

近自己做了套数据库监控系统，搞的还可以，简单给大家介绍一下。

Pigsty is an advanced PostgreSQL monitoring systemd based on open source projects like prometheus & grafana. PIGSTY /pɪɡ staɪ/ is the abbreviation of “Postgres in Grafana Style”.

Pigsty是一个基于Grafana与Prometheus与Consul的Postgres数据库监控系统。

整体架构

TLDR: (Node/Pg/Pgbouncer) Exporter Discovered by Consul to Prometheus to Grafana

┏━━━━━━━━━━━┓     ┏━━━━━━━━━━━━━━━━━━━━┓
┃   Node    ┃ --> ┃   Node    Exporter-┃┐
┃ Pgbouncer ┃ --> ┃ Pgbouncer Exporter-┃┼--> Prometheus ---> Grafana
┃ Postgres  ┃ --> ┃ Postgres  Exporter-┃┘        ↑
┃           ┃     ┗━━━━━━━━━━━━━━━━━━━━┛  (Service Discovery)
┃  Consul   ┃ ----------------------------->   Consul
┗━━━━━━━━━━━┛

一言以蔽之：用Exporter取指标数据，通过Consul服务发现赋予身份标签与组织结构，存进Prometheus中进行预处理计算，最后使用Grafana展示

这里能看到的主要还是Grafana里的Dashboard，因此主要还是介绍脸面上的东西。\

层次组织

监控主要分为五个层次，集群（cluster），服务（service），实例（instance），数据库（database），与节点（node）。不过在本系统中，服务层次的监控指标被整合至集群级别，数据库层次的监控指标被整合至实例级别。因此实际上，只有三个核心层次的监控展示：集群，实例，节点。

• 集群使用cls唯一标识，名称类似于：pg-test-tt

• 实例使用ins唯一标识，名称类似于：pg-test-tt-0， 以集群为前缀，序号为后缀。后缀为0的实例通常是集群中的主库。

• 节点使用ip唯一标识。

除此之外，还有一些其他层次的Dashboard：例如全局大盘概览，分片库专用的Shard Dashboard，每一个数据库具体的Database Dashboard、连接池Pool层次的Dashboard，具体到某一个库上某一个查询的PG Query Dashboard，Pgbouncer中间件专用Dashboard，等等，这些衍生或周边的Dashboard就不介绍了。

功能简介

核心功能：日常巡检，故障排查，性能优化，全知即全能。

• PG全局监控

• PG Shard监控

• PG集群监控

• PG实例监控

• PG实例监控（故障排查专用视图）

• PG节点监控

• PG慢查询平台

• Redis全局概览

• Redis集群监控

• Redis实例监控

• PG集群健康度评估系统

首页导航概览

包含PG和Redis两部分，左侧为全局指标概览，右侧为集群导航。中间为全局报警与事件提醒。点击右上角的导航链接，或者页面中的可导航元素（Shard，集群名，实例名，IP等）可跳转至感兴趣的面板

DB监控：指标介绍

指标丰富程度

你可以不看，我不能没有。

每个实例包括了约3300个指标，其中：

数据库与连接池指标1000个，其中规则定义衍生指标250个。

节点指标约2000个，其中规则定义的衍生指标700个。

举个例子，单纯一个QPS，就可以衍生出下面近30个指标。\

这里随便挑一些重要的指标介绍一下\

指标内容

按Google SRE实践划分的四类黄金指标

错误

• 配置错误：关键功能是否配置正常：校验和，Numa，透明大页，同步提交等。

• 内存错误，TCP错误，时间漂移错误

• 服务宕机：机器，数据库，连接池，监控组件

• 数据库客户端排队，IdleInXact连接，超长事务，死锁，复制中断，大量回滚，监控报错

饱和度

• PG Load, Node Load

• CPU使用，内存使用，磁盘使用，网卡带宽利用率，缓存命中率，后端连接使用，连接池使用

流量

• 数据库直接指标：QPS，TPS，查询细分QPS

• 间接流量指标：连接池进出流量，WAL写入量，增删改查条数，块访问量，缓冲区访问量

• 节点流量：磁盘IO流量，网络IO流量，内存页面换入换出

延迟

• 事务平均响应时间 Xact RT

• 查询平均响应时间 Query RT

• 语句平均响应时间：Statement RT

• 磁盘平均响应时间：Disk R/W Latency

• 复制延迟（以秒或字节计算）

• 监控查询延迟

DB监控：PG实例

实例概览

• 实例身份信息：集群名，ID，所属节点，软件版本，所属集群其他成员等

• 实例配置信息：一些关键配置，目录，端口，配置路径等

• 实例健康信息，实例角色（Primary，Standby）等。

• 黄金指标：PG Load，复制延迟，活跃后端，排队连接，查询延迟，TPS，数据库年龄

• 数据库负载：实时（Load0），1分钟，5分钟，15分钟

• 数据库警报与提醒事件

关于PG Load，可以参考本号前一篇文章，如何给PostgreSQL定KPI

节点概览

• 四大基本资源：CPU，内存，磁盘，网卡的配置规格，关键功能，与核心指标

• 右侧是网卡详情与磁盘详情

单日统计

以最近1日为周期的统计信息（从当前时刻算起的前24小时），比如最近一天的查询总数，返回的记录总数等。上面两行是节点级别的统计，下面两行是主要是PG相关的统计指标。

对于计量计费，水位评估特别有用。

复制

• 当前节点的Replication配置

• 复制延迟：以秒计，以字节计的复制延迟，复制槽堆积量

• 下游节点对应的Walsender统计

• 各种LSN进度，综合展示集群的复制状况与持久化状态。

• 下游节点数量统计，可以看出复制中断的问题

事务

事务部分用于洞悉实例中的活动情况，包括TPS，响应时间，锁等。

• TPS概览信息：TPS，TPS与过去两天的DoD环比。DB事务数与回滚数

• 回滚事务数量与回滚率

• TPS详情：绿色条带为±1σ，黄色条带为±3σ，以过去30分钟作为计算标准，通常超出黄色条带可认为TPS波动过大

• Xact RT，事务平均响应时间，从连接池抓取。绿色条带为±1σ，黄色条带为±3σ。

• TPS与RT的偏离程度，是一个无量纲的可横向比较的值，越大表示指标抖动越厉害，计算方式为：(μ/σ)^2

• 按照DB细分的TPS与事务响应时间，通常一个实例只有一个DB，但少量实例有多个DB。

• 事务数，回滚数（TPS来自连接池，而这两个指标直接来自DB本身）

• 锁的数量，按模式聚合（8种表锁），按大类聚合（读锁，写锁，排他锁）

查询

大多数指标与事务中的指标类似，不过统计单位从事务变成了查询语句。查询部分可用于分析实例上的慢查询，定位性能瓶颈。

• QPS 每秒查询数，与Query RT查询平均响应时间，以及这两者的波动程度，QPS的周期环比等

• 生产环境对查询平均响应时间有要求：1ms为红线，100ms就该约谈了。

语句

语句展示了查询中按语句细分的指标。每条语句（查询语法树抽离常量变量后如果一致，则算同一条查询）都会有一个查询ID，可以在慢查询平台中获取到具体的语句与详细指标与统计。

• 左侧慢查询列表是按pg_stat_statments中的平均响应时间从大到小排序的，点击查询ID会自动跳转到慢查询平台

• 这里列出的查询，是累计查询耗时最长的32个查询，但排除只有零星调用的长耗时单次查询与监控查询。

• 右侧包括了每个查询的实时QPS，平均响应时间。按照RT与总耗时的排名。

后端进程

后端进程用于显示与PG本身的连接，后端进程相关的统计指标。特别是按照各种维度进行聚合的结果，特别适合定位雪崩，慢查询，其他疑难杂症。

• 后端进程数按种类聚合，后端进程按状态聚合，后端进程按DB聚合，后端进程按等待事件类型聚合。

• 活跃状态的进程/连接，在事务中空闲的连接，长事务。

连接池

连接池部分与后端进程部分类似，但全都是从Pgbouncer中间件上获取的监控指标

• 连接池后端连接的状态：活跃，刚用过，空闲，测试过，登录状态。

• 分别按照User，按照DB，按照Pool（User:DB）聚合的前端连接，用于排查异常连接问题。

• 等待客户端数（重要），以及队首客户端等待的时长，用于定位连接堆积问题。

• 连接池可用连接使用比例。

数据库概览

Database部分主要来自pg_stat_database与pg_database，包含数据库相关的指标：

• WAL Rate，标识数据库的写入负载，每秒产生的WAL字节数量。

• Buffer Hit Rate，数据库 ShareBuffer 命中率，未命中的页面将从操作系统PageCache和磁盘获取。

• 每秒增删改查的记录条数

• 临时文件数量与临时文件大小，可以定位大型查询问题。

持久化

持久化主要包含数据落盘，Checkpoint，块访问相关的指标

• 重要的持久化参数，比如是否出现数据校验和验证失败（如果启用可以检测到数据腐坏）

• 数据库文件（DB，WAL，Log）的大小与增速。

• 检查点的数量与检查点耗时。

• 每秒分配的块，与每秒刷盘的块。每秒访问的块，以及每秒从磁盘中读取的块。（以字节计，注意一个Buffer Page是8192，一个Disk Block是4096）

监控Exporter

Exporter展示了监控系统组件本身的监控指标，包括：

• Exporter是否存活，Uptime，Exporter每分钟被抓取的次数

• 每个监控查询的耗时，产生的指标数量与错误数量。

DB监控：PG集群

PG集群监控是最常用的Dashboard，因为PG以集群为单位提供服务，因此Cluster集合了最完整全面的信息。

大多数监控图都是实例级监控的泛化与上卷，即从展示单个实例内的细节，变为展现集群内每个实例的信息，以及集群和服务层次聚合后的指标。

集群概览

Cluster级别的集群概览相比实例级别多了一些东西：

• 时间线与领导权，当数据库发生Failover或Switchover时，时间线会步进，领导权会发生变化。

• 集群拓扑，集群拓扑展现了集群中的复制拓扑，以及采用的复制方式（同步/异步）。

• 集群负载，包括整个集群实时、1分钟、5分钟、15分钟的负载情况。以及集群中每个节点的Load1

• 集群报警与事件。

集群复制

Cluster级别的Dashboard与Instance级别Dashboard最重要的区别之一就是提供了整个集群的复制全景。包括：

• 集群中的主库与级联桥接库。集群是否启用同步提交，同步从库名称。桥接库与级联库数量，最大从库配置

• 成对出现的Walsender与Walreceiver列表，体现一对主从关系的复制状态

• 以秒和字节衡量的复制延迟（通常1秒的复制延迟对应10M~100M不等的字节延迟），复制槽堆积量。

• 从库视角的复制延迟

• 集群中从库的数量，备份或拉取从库时可以从这里看到异常。

• 集群的LSN进度，用于整体展示集群的复制状态与持久化状态。

节点指标

PG机器的相关指标，按照集群进行聚合。

事务与查询

与实例级别的类似，但添加了Service层次的聚合（一个集群通常提供primary与standby两种Service）。

其他指标与实例级别差别不大。\

DB监控：PG慢查询平台

显示慢查询相关的指标，上方是本实例的查询总览。鼠标悬停查询ID可以看到查询语句，点击查询ID会跳转到对应的查询细分指标页（Query Detail）。

• 左侧是格式化后的查询语句，右侧是查询的主要指标，包括

  • 每秒查询数量：QPS

  • 实时的平均响应时间（RT Realtime）

  • 每次查询平均返回的行数

  • 每次查询平均用于BlockIO的时长

  • 响应时间的均值，标准差，最小值，最大值（自从上一次统计周期以来）

  • 查询最近一天的调用次数，返回行数，总耗时。以及自重置以来的总调用次数。

• 下方是指定时间段的查询指标图表，是概览指标的细化。

9 - PostgreSQL的KPI

管数据库和管人差不多，都需要定KPI（关键性能指标）。那么数据库的KPI是什么？本文介绍了一种衡量PostgreSQL负载的方式：使用一种单一横向可比，与负载类型和机器类型基本无关的指标，名曰 PG Load（PG负载）。

0x01 Introduction

在现实生产中，经常会有衡量数据库性能与负载，评估数据库水位的需求。一种最朴素的形式就是，能不能有一个类似于KPI的单一指标，能直接了当地告诉用户他心爱的数据库负载有没有超过警戒线？工作量到底饱和不饱和？

当然这里其实隐含着一个重要信息，即用户期待的负载指标是一个 饱和度（Saturation） 指标，所谓饱和度，即服务容量有多”满“，通常是系统中目前最为受限的某种资源的某个具体指标的度量。通常来说，0%的饱和度意味着系统完全空闲，100%的饱和度意味着满载，系统在达到100%利用率前就会出现性能的严重下降，因此设定指标时还需要包括一个 利用率目标，或者说 水位红线、黄线，当系统瞬时负载超过红线时应当触发告警，长期负载超过黄线时应当进行扩容。

不幸的是，定义系统有多”饱和“并不是一件容易的事情，往往需要借助某些间接指标。评估一个数据库的负载程度，传统上通常会基于这样几类指标进行综合评估：

• 流量：每秒查询数量QPS，或每秒事务数量TPS。

• 延迟：查询平均响应时间 Query RT，或事务平均响应时间Xact RT

• 饱和度：机器负载（Load），CPU使用率，磁盘读写带宽饱和度，网卡IO带宽饱和度

• 错误：数据库客户端连接排队

这些指标对于数据库性能评估都很有参考意义，但它们也都存在各式各样的问题。

0x02 常用评估指标的问题

让我们来看一看，这些现有的常用指标都有哪些问题。

第一个Pass的当然是错误类指标，譬如连接池排队。错误类指标最大的问题就是，当错误出现时，饱和度可能已经没有意义了。评估饱和度的一个重要原因就是用于预防系统过载，如果系统已经过载大量报错，那么使用错误现象反过来定义饱和度是没有意义的。此外，错误类指标难以精确量化。我们只能说：当连接池出现排队时，数据库负载比较大；队列越长，负载越大；没有排队时，数据库负载不怎么大，仅此而已。这样的定义当然也无法让人满意。

第二个Pass的则是系统层（机器级别）指标，数据库运行在机器上，CPU使用率，IO使用率这样的指标与数据库负载程度密切相关，如果CPU和IO是瓶颈，理论上当然是可以直接使用瓶颈资源的饱和度指标 作为数据库的饱和指标，但这一点并非总是成立的，有可能系统瓶颈在于数据库本身。而且严格来说它们是机器的KPI而不是DB的KPI，评估数据库负载时当然可以参照系统层的指标，但DB层也应该有本层的评估指标。要先有数据库本身的饱和度指标，才可以去比较底层资源和数据库本身到底谁先饱和谁是瓶颈。这条原则同样适用于应用层观察到的指标。

流量类的指标很有潜力，特别是QPS，TPS这样的指标相当具有代表性。但这些指标也存在问题。一个数据库实例上的查询往往是五花八门各式各样的，一个耗时10微秒的查询和一个10秒的查询在统计时都被算为一个Q，类似于QPS这样的指标无法进行横向比较，只有比较粗略的参考意义，甚至当查询类型发生变化时，都无法和自己的历史数据进行纵向比较。此外也很难针对QPS、TPS这样的指标设置利用率目标，同一个数据库执行 SELECT 1 可以打到几十万的QPS，但执行复杂SQL时可能就只能打到几千的QPS。不同负载类型和机器硬件会对数据库的QPS上限产生显著影响，只有当一个数据库上的查询都是高度单一同质且没有复杂变化的条件下，QPS才有参考意义，在这种苛刻条件下倒是可以通过压力测试设定一个QPS的水位目标。

比起QPS/TPS，RT（响应时间 Response Time）这样的指标反而更具有参考价值。因为响应时间增加往往是系统饱和的前兆。根据经验法则，数据库的负载越大，查询与事务的平均响应时间也会越高。RT相比QPS的一个优势是 ，RT是可以设置一个利用率目标的，比如可以为RT设定一个绝对阈值：不允许生产OLTP库上出现RT超过1ms的慢查询。但QPS这样的指标就很难画出红线来。不过，RT也有自己的问题。第一个问题是它依然是定性而非定量的，延迟增加只是系统饱和的预警，但没法用来精确衡量系统的饱和度。第二个问题通常能从数据库与中间件获取到的RT统计指标都是平均值，但真正起到预警效果的有可能是诸如P99，P999这样的统计量。

这里把常用指标都批判了一番，到底什么样的指标适合作为数据库本身的饱和度呢？

0x03 衡量PG的负载

我们不妨参考一下 机器负载（Node Load） 和 CPU利用率（CPU Utilization） 的评估指标是如何设计的。

机器负载（Node Load）

想要看到机器的负载水平，可以在Linux系统中使用 top 命令。top 命令的第一行输出就醒目地打印出当前机器1分钟，5分钟，15分钟的 平均负载水平。

$ top -b1
top - 19:27:38 up 18:49,  1 user,  load average: 1.15, 0.72, 0.71

这里 load average 后面的三个数字分别表示最近1分钟，5分钟，15分钟系统的平均负载水平。

那么这个数字到底是什么意思呢？简单的解释是，这个数字越大机器越忙。

在单核CPU的场景下，Node Load（以下简称负载）是一个非常标准的饱和度指标。对于单核CPU，负载为0时CPU处于完全空闲的状态，负载为1（100%）时，CPU正好处于满载工作的状态。负载大于100%时，超出100%部分比例的任务正在排队。

Node Load也有自己的 利用率目标，通常的经验是在单核情况下：0.7（70%）是黄线，意味着系统有问题，需要尽快检查；1.0（100%）是红线，负载大于1意味着进程开始堆积，需要立即着手处理。5.0（500%）是死线，意味着系统基本上已经堵死了。

对于多核CPU，事情稍微有点不一样。假设有n个核，那么当系统负载为n时，所有CPU都处于满载工作的状态；而当系统负载为n/2时，姑且可以认为一半CPU核正在满载运行。因而48核CPU的机器满载时的负载为48。总的来说，如果我们把机器负载除以机器的CPU核数，得到的指标就与单核场景下保持一致了（0%空载，100%满载）。

CPU利用率（CPU Utilization）

另一个很有借鉴意义的指标是 CPU利用率（CPU Utilization）。CPU利用率其实是通过一个简单的公式计算出来的，对于单核CPU：

1 - irate(node_cpu_seconds_total{mode="idle"}[1m]

这里 node_cpu_seconds_total{mode="idle"} 是一个计数器指标，表示CPU处于空闲状态的总时长。irate 函数会用该指标对时间进行求导，得出的结果是，每秒CPU处于空闲状态的时长，换句话说也就是CPU空闲率。用1减去该值就得到了CPU的利用率。

对于多核CPU来说，只需要把每个CPU核的利用率加起来，除以CPU的核数，就可以得到CPU的整体利用率。

那么这两个指标对于PG的负载又有什么借鉴意义呢？

数据库负载（PG Load）

PG的负载是不是也可以采用类似于CPU利用率和机器负载的方式来定义？当然可以，而且这是一个极棒的主意。

让我们先来考虑单进程情况下的PG负载，假设我们需要这样一个指标，当该PG进程完全空闲时负载因子为0，当该进程处于满载状态时负载为1（100%）。类比CPU利用率的定义，我们可以使用“单个PG进程处于活跃状态的时长占比”来表示“单个PG后端进程的利用率”。

如图1所示，在一秒的统计周期内，PG处于活跃（执行查询或者执行事务）状态的时长为0.6秒，那么这一秒内的PG负载就是60%。如果这个唯一的PG进程在整个统计周期中都处于忙碌状态，而且还有0.4秒的任务在排队，如那么就可以认为PG的负载为140%。

对于并行场景，计算方法与多核CPU的利用率类似，首先把所有PG进程在统计周期（1s）内处于活跃状态的时长累加，然后除以“可用的PG进程/连接数”，或者说“可用并行数”，即可得到PG本身的利用率指标，如图3所示。两个PG后端进程分别有200ms+400ms与800ms的活跃时长，那么整体的负载水平为：(0.2s + 0.4s + 0.8s) / 1s / 2 = 70%

总结一下，某一段时间内PG的负载可以定义为：

pg_load = pg_active_seconds / time_peroid / parallel

• pg_active_seconds 是该时间段内所有PG进程处于活跃状态的时长之和。

• time_peroid 是负载计算的统计周期，通常为1分钟，5分钟，15分钟，以及实时（小于10秒）。

• parallel 是PostgreSQL的可用并行数，后面会详细解释。

因为前两项之商实际上就是一段时间内的每秒活跃时长总数，因此这个公式进一步可以简化为活跃时长对时间的导数除以可用并行数，即：

rate(pg_active_seconds[time_peroid]) / parallel

time_peroid 通常是固定的常量（1，5，15分钟），所以问题就是如何获取PG进程活跃总时长 pg_active_seconds 这个指标，以及如何评估计算数据库可用并行数 max_parallel 了。

0x04 计算PG的负载饱和度

事务还是查询？

当我们说数据库进程 活跃/空闲 时，究竟在说什么？ PG处于活跃状态，到底是什么意思？如果PG后端进程正在执行查询，那么当然可以认为PG正处于忙碌状态。但如果如上图4所示，PG进程正在执行一个交互式事务，但没有实际执行查询，即所谓的“Idle in Transaction”状态，又应该怎么计算“活跃时长”呢？图4中两个查询中空闲的那200ms时间。那么这段时间应该算作“活跃”，还是算作“空闲”呢？

这里的核心问题是怎么定义活跃状态：数据库进程位于事务中算活跃，还是只有当实际执行查询时才算活跃。对于没有交互式事务的场景，一个查询就是一个事务，用哪种方式都一样，但对于多语句，特别是交互式的多语句事务，这两者就有比较明显的区别了。从资源使用的角度看，没有执行查询也就意味着没有消耗数据库本身的资源。但空闲着的事务本身会占用连接导致连接无法复用，Idle In Transaction本身也应当是一种极力避免的情况。总的来说，这两种定义方式都可以，使用事务的方式会略微高估应用负载，但从负载评估的角度可能会更为合适。

如何获取活跃时长

决定了数据库后端进程的活跃定义后，第二个问题就是，如何获取一段时间的数据库活跃时长？不幸的是在PG中，用户很难通过数据库本身获取这一性能指标。PG提供了一个系统视图：pg_stat_activity，可以看到当前运行着的Postgres进程里列表，但这是一个时间点快照，只能大致告诉在当前时刻，数据库的后端进程中有多少个处于活跃状态，有多少个处于空闲状态。统计一段时间内数据库处于活跃状态的时长，就成了一个难题。一种解决方案是使用类似于Load的计算方式，通过周期性地采样PG中活跃进程的数量，计算出一个负载指标来。不过，这里有更好的办法，但是需要中间件的协助参与。

数据库中间件对于性能监控非常重要，因为很多指标数据库本身并没有提供，只有通过中间件才能暴露出来。以Pgbouncer为例，Pgbouncer在内部维护了一系列统计计数器，使用 SHOW STATS 可以打印出这些指标，诸如：

• total_xact_count：总共执行了多少个事务
• total_query_count：总共执行了多少个查询
• total_xact_time：总共花费在事务执行的时长
• total_query_time：总共花费在查询执行上的时长

这里 total_xact_time 就是我们需要的数据，它记录了Pgbouncer中间件中花费在某个数据库上的事务总耗时。我们只需要用这个指标对时间求导，就可以得到想要的数据：每秒活跃时长占比。

这里使用Prometheus的PromQL表达计算逻辑，首先对事务耗时计数器求导，分别算出其1分钟，5分钟，15分钟，以及实时粒度（最近两次采样点之间）上的 每秒活跃时长。再上卷求和，将数据库层次的指标上卷为实例级别的指标。（连接池 SHOW STATS 这里的统计指标是以数据库为单位的，因此在计算实例级别的总活跃时长时，应当上卷求和，消除数据库维度的标签：sum without(datname)）

- record: pg:ins:xact_time_realtime
expr: sum without (datname) (irate(pgbouncer_stat_total_xact_time{}[1m]))
- record: pg:ins:xact_time_rate1m
expr: sum without (datname) (rate(pgbouncer_stat_total_xact_time{}[1m]))
- record: pg:ins:xact_time_rate5m
expr: sum without (datname) (rate(pgbouncer_stat_total_xact_time{}[5m]))
- record: pg:ins:xact_time_rate15m
expr: sum without (datname) (rate(pgbouncer_stat_total_xact_time{}[15m]))

这样计算得到的结果指标已经可以相对本身进行纵向比较，并在同样规格的实例间进行横向比较了。而且无论数据库的负载类型怎样，都可以使用这个指标。

不过 不同规格的实例，仍然没法使用这个指标进行对比。比如对于单核单连接PG，满载时每秒活跃时长可能是1秒，也就是100%利用率。而对于64核64连接的PG，满载时每秒活跃时长是64秒，那么就是6400%的利用率。因此，还需要一个归一化的处理，那么问题又来了。

可用并行数如何定义？

不同于CPU利用率，PG的可用并行数并没有一个清晰的定义，而且跟负载类型有一些微妙的关系。但能够确定的是，在一定范围内，最大可用并行与CPU的核数呈粗略的线性关系。当然这个结论的前提是数据库最大连接数显著超过CPU核数，如果在64核的CPU上只允许数据库建立30条连接，那么可以肯定最大可用并行就是30而不是CPU核数64。软件的并行最终还是要由硬件的并行度来支撑，因此我们可以简单的使用实例的CPU核数作为可用并行数。

在64核的CPU上运行64个活跃PG进程，则其负载为（6400% / 64 = 100%）。同理运行128个活跃PG进程，负载就是（12800% / 64 = 200%）。

那么利用上面计算得到的每秒活跃时长指标，就可以计算出实例级别的PG负载指数了。

- record: pg:ins:load0
expr:  pg:ins:xact_time_realtime / on (ip) group_left()  node:ins:cpu_count
- record: pg:ins:load1
expr: pg:ins:xact_time_rate1m  / on (ip) group_left()  node:ins:cpu_count
- record: pg:ins:load5
expr: pg:ins:xact_time_rate5m  / on (ip) group_left()  node:ins:cpu_count
- record: pg:ins:load15
expr: pg:ins:xact_time_rate15m  / on (ip) group_left()  node:ins:cpu_count

PG LOAD的另一种解释

如果我们仔细审视PG Load的定义，其实可以发现每秒活跃时长这个指标，其实可以粗略等价于：TPS x XactRT，或者QPS x Query RT。这个也很好理解，假设我QPS为1000，每个查询RT为1ms，则每秒花费在查询上的时间为 1000 * 1ms = 1s。

因此，PG Load可以视为一个由三个核心指标复合而成的衍生指标：tps * xact_rt / cpu_count

TPS，RT用于负载评估都有各自的问题，但它们通过简单的乘法结合成一个新的复合指标，一下子就显示出了神奇的力量。（尽管实际上是通过其他更准确的方式计算出来的）

0x05 PG Load的实际效果

接下来，我们来看一下PG Load用于实际生产环境的表现。

PG Load最直接的作用有两个，告警以及容量评估。

Case 1: 用于报警：慢查询堆积导致的服务不可用

下图是一次生产事故的现场，由于某业务上线了一个慢查询，瞬间导致连接池被慢查询占据，发生堆积。可以看出PG Load和RT都很及时地反映出了故障的情况，而TPS看上去则是掉了一个坑，并不是特别显眼。

从效果上看，PG Load1与PG Load0（实时负载）是一个相当灵敏的指标，对于大多数与压力负载有关的故障都能及时准确作出反应。所以被我们采纳为核心报警指标。

PG Load的利用率目标有一些经验值：黄线通常为50%，即需要引起关注的阈值；红线通常为70%，即报警线，需要立刻采取行动的阈值；500%或更高通常意味着这个实例已经被打崩了。

Case 2：用于水位评估与容量规划

比起报警，水位评估与容量规划更像是PG Load的核心用途。毕竟报警之类的的需求还是可以通过延迟，排队连接等指标来满足的。

这里，PG集群的15分钟负载是一个很好的参考值。通过这个指标的历史均值，峰值，以及其他一些统计量，我们可以很轻松地看出哪些集群处于高负载状态需要扩容，哪些集群处于低资源利用率状态需要缩容。

CPU利用率是另一个很重要的容量评估指标。我们可以看出，PG Load与CPU Usage有着很密切的关系。不过相比CPU使用率，PG Load更为纯粹地反映了数据库本身的负载水平，滤除了机器上的无关负载，也可以滤除掉数据库维护工作（备份，清理，垃圾回收）产生的杂音，更为丝滑平顺。因此非常适合用于容量评估。

当系统负载长期位于30%~50%时，就应该考虑进行扩容了。

0x06 结论

本文介绍了一种定量衡量PG负载的方式，即PG Load指标

该指标可以简单直观地反映数据库实例的负载水平

该指标非常适合作容量评估之用，也可以作为核心报警指标。

该指标可以基本无视负载类型与机器类型，进行纵向历史比较与横向水位比较。

该指标可以通过简单的方式计算得出，即每秒后端进程活跃总时长除以可用并发数。

该指标所需数据需要从数据库中间件获取

PG Load的0代表空载，100%代表满载。黄线经验值为50%，红线经验值为70%，

PG Load是一个好指标👍

WeChat Column

10 - PostgreSQL监控系统概览

PostgreSQL驾驶技巧

PostgreSQL是一个很棒的数据库，但也相当复杂。上手虽简单，但想要用好不容易，想要管理好就更麻烦了。监控系统是几乎所有运维工作的基础，更亦是驾驭数据库的必备工具。用好一个监控系统，理解各种指标背后的意义并不是一件简单的事情，因此本司机决定写一系列文章，来介绍了PostgreSQL监控系统的设计，实施与使用。

0x01 PostgreSQL监控面板

为了帮助读者形成一种直觉，这里展示了在实际环境中，我所使用的监控面板。最为常用的监控面板为“单数据库实例”监控，并分为7个主要的子区域：概览，操作系统，数据库活动与会话，复制，检查点与WAL，数据库冲突，以及数据库对象统计，如下所示。

监控系统的架构\

程序员的一个基本原则就是：“不要造轮子”。所以如果有现成的开源组件能用，那是最好不过。一个比较简单的实用监控系统架构如下图所示。主要组件包括：Prometheus，Grafana，Consul，以及各个Exporter。各类Exporter从数据库，连接池，以及机器上获取指标数据库，Prometheus从Consul中发现这些Exporter，并向其拉取数据，Grafana负责展示Prometheus中的数据，而AlertManager负责报警相关的任务。

监控系统的指标

监控面板与系统架构，不过是监控系统的面子，真正的里子在于监控指标。哪些指标需要监控，每种指标如何解读，这才是真正重要的东西。毕竟知道了这些知识，即使没有监控系统，通过命令行手工检查也可以发现问题，但不了解指标背后的涵义，即使再傻瓜再漂亮的监控面板也于事无补。

下面会简单介绍单实例监控面板中涉及到的指标。

概览

概览是一些重要的核心指标，一眼扫过去就应当能大致定位数据库的主要问题。这里主要包括：

• CPU使用率、平均查询时间、TPS、QPS，连接池排队数，活跃连接数：判断数据库负载水平

• 内存空闲率：判断是否出现进程异常（例如突然被杀）

• 磁盘空闲率：判断磁盘是否被写满

• 数据库年龄：数据库是否快出现事务标识回卷故障。

• 数据库大小，缓存命中率：判断异常活动

操作系统指标\

操作系统指标主要包括：

• CPU与负载水平

• 内存使用，特别需要关注操作系统cache部分的变化。

• 网络与磁盘的IO吞吐量，磁盘的IOPS。

操作系统的指标能直观地反映大部分数据库活动异常，对于硬件类故障的排查尤有帮助。

数据库活动与会话

Activity与Session是数据库当前状态的直观反映，指标包括：

• TPS/QPS，平均响应时间都是最直接的负载指标。

• 缓存命中率对数据库性能有直接的影响，注意这里只是PostgreSQL本身BufferPool的命中率，并没有计算操作系统的文件缓存。

• 后端连接按状态的分布，active状态的连接是很重要的负载指标，而idle in transaction的连接数则需要特别关注。

• 临时文件通常是由一些复杂查询，长查询，过多的临时文件容易导致性能恶化。

复制指标

很多类型的故障与主从复制滞后有关，因此，ReplicationDelay是一个重要的监控指标。

在主库上可以获取从库的复制延迟，包括LSN落后的字节数，以及滞后的时长（v10以后）。在从库上也可以获得距离主库的滞后时长，但这种方法通常不如前一种准确。

检查点与WAL

检查点会吃IO，一方面它会引发大量页面落盘，另一方面在默认full_page_write打开的情况下，每一次检查点都会导致其后的WAL量升高，吃磁盘与网络的带宽。在一些情况下可能会成为故障的原因。因此有必要监控检查点与WAL，相关指标包括：

• WAL日志的生成速率

• LSN增长的速度

• 缓冲区刷回的数目，通常缓冲区刷回主要由Checkpoint负责，如果有大量缓冲区由后端进程负责刷盘，就需要检查并调整相关参数的配置了。

• 数据库与WAL日志的大小。

锁与数据库冲突

锁按类型分布的数据对于判断数据库活动的类型很有帮助。

对象级统计

对象级统计能够帮助DBA快速定位故障的根源。包括：

• 每个表的访问次数：索引扫描/顺序扫描。频繁出现的顺序扫描往往意味着索引出现了问题。

• 表中元组的拉取，修改，增删改查数目，表的大小，年龄，都有助于精确定位问题的来源。

• 死元组的比例：死元组的比例可用于估算表的膨胀程度，对于维持性能很有帮助。（还有另外一种通过统计数据估算表膨胀率的方法）

• 函数的调用次数与执行时间。

监控指标的来源\

统计收集器

对于PostgreSQL而言，其监控指标主要源于自带的统计收集器。PostgreSQL提供了很多系统视图，因此能很简单地以SQL语句的形式拉取统计数据。系统视图的具体定义可以参考文档，后续文章也会依此详细介绍。其中，比较重要的视图包括：

从统计视图中收集数据有一些注意事项：

• 统计数据并非实时更新的，每个服务进程只有在闲置前会更新统计计数。所以正在执行的查询和事务不影响计数。

• 收集器本身每隔（PGSTAT_STAT_INTERVAL=500ms）才发送一次新的报告。所以除了 当前进程活动track_activity之外的统计指标都不是最新的。

• 在事务中执行的统计查询，统计数据不会发生变化。使用pg_stat_clear_snapshot()来获取最新的快照。

• 要让数据库收集这些统计数据，需要在postgresql.conf中打开各种track_*选项。

系统视图

除了统计视图外，还有一些系统视图也能提供很多有价值的指标信息，例如pg_database与pg_locks，分别提供了数据库年龄尺寸，以及数据库内的锁等待信息。此外，还有很多系统指标是通过内建的函数提供的。

结语

监控是一个很大的话题，够写半本书了，因此这篇文章能做的也就是给读者留下一点直觉：监控系统应该是啥样子的。其实有了以上的信息，一个合格的架构师与PostgreSQL DBA应该能很轻松的复现该监控系统了。具体的实现细节，将在后续文章中继续介绍。

11 - 关系膨胀的监控与治理

PostgreSQL使用了MVCC作为主要并发控制技术，它有很多好处，但也会带来一些其他的影响，例如关系膨胀。关系（表与索引）膨胀会对数据库性能产生负面影响，并浪费磁盘空间。为了使PostgreSQL始终保持在最佳性能，有必要及时对膨胀的关系进行垃圾回收，并定期重建过度膨胀的关系。

在实际操作中，垃圾回收并没有那么简单，这里有一系列的问题：

• 关系膨胀的原因？
• 关系膨胀的度量？
• 关系膨胀的监控？
• 关系膨胀的处理？

本文将详细说明这些问题。

关系膨胀概述

假设某个关系实际占用存储100G，但其中有很多空间被死元组，碎片，空闲区域浪费，如果将其压实为一个新的关系，占用空间变为60G，那么就可以近似认为该关系的膨胀率是 (100 - 60) / 100 = 40%。

普通的 VACUUM 不能解决表膨胀的问题，死元组本身能够被并发 VACUUM 机制回收，但它产生的碎片，留下的空洞却不可以。比如，即使删除了许多死元组，也无法减小表的大小。久而久之，关系文件被大量空洞填满，浪费了大量的磁盘空间。

VACUUM FULL 命令可以回收这些空间，它将旧表文件中的活元组复制到新表中，通过重写整张表的方式将表压实。但在实际生产中，因为该操作会持有表上的 AccessExclusiveLock，阻塞业务正常访问，因此在不间断服务的情况下并不适用，pg_repack 是一个实用的第三方插件，能够在线上业务正常进行的同时进行无锁的 VACUUM FULL。

不幸的是，关于什么时候需要进行 VACUUM FULL 处理膨胀并没有一个最佳实践。DBA需要针对自己的业务场景制定清理策略。但无论采用何种策略，实施这些策略的机制都是类似的：

• 监控，检测，衡量关系的膨胀程度
• 依据关系的膨胀程度，时机等因素，处理关系膨胀。

这里有几个关键的问题，首先是，如何定义关系的膨胀率？

关系膨胀的度量

衡量关系膨胀的程度，首先需要定义一个指标：膨胀率（bloat rate）。

膨胀率的计算思想是：通过统计信息估算出目标表如果处于 紧实（Compact） 状态所占用的空间，而实际使用空间超出该紧实空间部分的占比，就是膨胀率。因此膨胀率可以被定义为 1 - (活元组占用字节总数 / 关系占用字节总数)。

例如，某个表实际占用存储100G，但其中有很多空间被死元组，碎片，空闲区域浪费，如果将其压实为一张新表，占用空间变为60G，那么膨胀率就是 1 - 60/100 = 40%。

关系的大小获取较为简单，可以直接从系统目录中获取。所以问题的关键在于，活元组的字节总数 这一数据如何获取。

膨胀率的精确计算

PostgreSQL自带了 pgstattuple 模块，可用于精确计算表的膨胀率。譬如这里的 tuple_percent 字段就是元组实际字节占关系总大小的百分比，用1减去该值即为膨胀率。

vonng@[local]:5432/bench# select *,
                          1.0 - tuple_len::numeric / table_len as bloat
                          from pgstattuple('pgbench_accounts');
┌─[ RECORD 1 ]───────┬────────────────────────┐
│ table_len          │ 136642560              │
│ tuple_count        │ 1000000                │
│ tuple_len          │ 121000000              │
│ tuple_percent      │ 88.55                  │
│ dead_tuple_count   │ 16418                  │
│ dead_tuple_len     │ 1986578                │
│ dead_tuple_percent │ 1.45                   │
│ free_space         │ 1674768                │
│ free_percent       │ 1.23                   │
│ bloat              │ 0.11447794889088729017 │
└────────────────────┴────────────────────────┘

pgstattuple 对于精确地判断表与索引的膨胀情况非常有用，具体细节可以参考官方文档：https://www.postgresql.org/docs/current/static/pgstattuple.html。

此外，PostgreSQL还提供了两个自带的扩展，pg_freespacemap 与 pageinspect，前者可以用于检视每个页面中的空闲空间大小，后者则可以精确地展示关系中每个数据页内物理存储的内容。如果希望检视关系的内部状态，这两个插件非常实用，详细使用方法可以参考官方文档：

https://www.postgresql.org/docs/current/static/pgfreespacemap.html

https://www.postgresql.org/docs/current/static/pageinspect.html

不过在绝大多数情况下，我们并不会太在意膨胀率的精确度。在实际生产中对膨胀率的要求并不高：第一位有效数字是准确的，就差不多够用了。另一方面，要想精确地知道活元组占用的字节总数，需要对整个关系执行一遍扫描，这会对线上系统的IO产生压力。如果希望对所有表的膨胀率进行监控，也不适合使用这种方式。

例如一个200G的关系，使用 pgstattuple 插件执行精确的膨胀率估算大致需要5分钟时间。在9.5及后续版本，pgstattuple 插件还提供了 pgstattuple_approx 函数，以精度换速度。但即使使用估算，也需要秒级的时间。

监控膨胀率，最重要的要求是速度快，影响小。因此当我们需要对很多数据库的很多表同时进行监控时，需要对膨胀率进行 快速估算，避免对业务产生影响。

膨胀率的估算

PostgreSQL为每个关系都维护了很多的统计信息，利用统计信息，可以快速高效地估算数据库中所有表的膨胀率。估算膨胀率需要使用表与列上的统计信息，直接使用的统计指标有三个：

• 元组的平均宽度 avgwidth：从列级统计数据计算而来，用于估计紧实状态占用的空间。
• 元组数：pg_class.reltuples：用于估计紧实状态占用的空间
• 页面数：pg_class.relpages：用于测算实际使用的空间

而计算公式也很简单：

1 - (reltuples * avgwidth) / (block_size - pageheader) / relpages

这里 block_size 是页面大小，默认为8182，pageheader 是首部占用的大小，默认为24字节。页面大小减去首部大小就是可以用于元组存储的实际空间，因此 (reltuples * avgwidth) 给出了元组的估计总大小，而除以前者后，就可以得到预计需要多少个页面才能紧实地存下所有的元组。最后，期待使用的页面数量，除以实际使用的页面数量，就是 利用率，而1减去利用率，就是膨胀率。

难点

这里的关键，在于如何使用统计信息估算元组的平均长度，而为了实现这一点，我们需要克服三个困难：

• 当元组中存在空值时，首部会带有空值位图。
• 首部与数据部分存在Padding，需要考虑边界对齐。
• 一些字段类型也存在对齐要求

但好在，膨胀率本身就是一种估算，只要大致正确即可。

计算元组的平均长度

为了理解估算的过程，首先需要理解PostgreSQL中数据页面与元组的的内部布局。

首先来看元组的 平均长度，PG中元组的布局如下图所示。

一条元组占用的空间可以分为三个部分：

• 定长的行指针（4字节，严格来说这不算元组的一部分，但它与元组一一对应）
• 变长的首部
  • 固定长度部分23字节
  • 当元组中存在空值时，会出现空值位图，每个字段占一位，故其长度为字段数除以8。
  • 在空值位图后需要填充至 MAXALIGN，通常为8。
  • 如果表启用了 WITH OIDS 选项，元组还会有一个4字节的OID，但这里我们不考虑该情况。
• 数据部分

因此，一条元组（包括相应的行指针）的平均长度可以这样计算：

avg_size_tuple = 4 + avg_size_hdr + avg_size_data

关键在于求出 首部的平均长度 与 数据部分的平均长度。

计算首部的平均长度

首部平均长度主要的变数在于 空值位图 与 填充对齐。为了估算元组首部的平均长度，我们需要知道几个参数：

• 不带空值位图的首部平均长度（带有填充）：normhdr
• 带有空值位图的首部平均长度（带有填充）：nullhdr
• 带有空值的元组比例：nullfrac

而估算首部平均长度的公式，也非常简单：

avg_size_hdr =  nullhdr * nullfrac + normhdr * (1 - nullfrac)

因为不带空值位图的首部，其长度是23字节，对齐至8字节的边界，长度为24字节，上式可以改为：

avg_size_hdr =  nullhdr * nullfrac +  24 * (1 - nullfrac)

计算某值被补齐至8字节边界的长度，可以使用以下公式进行高效计算：

padding = lambda x : x + 7 >> 3 << 3

计算数据部分的平均长度

数据部分的平均长度主要取决于每个字段的平均宽度与空值率，加上末尾的对齐。

以下SQL可以利用统计信息算出所有表的平均元组数据部分宽度。

SELECT schemaname, tablename, sum((1 - null_frac) * avg_width)
FROM pg_stats GROUP BY (schemaname, tablename);

例如，以下SQL能够从 pg_stats 系统统计视图中获取 app.apple 表上一条元组的平均长度。

SELECT
  count(*),                        -- 字段数目
  ceil(count(*) / 8.0),            -- 空值位图占用的字节数
  max(null_frac),                  -- 最大空值率
  sum((1 - null_frac) * avg_width) -- 数据部分的平均宽度
FROM pg_stats
where schemaname = 'app' and tablename = 'apple';

-[ RECORD 1 ]-----------
count | 47
ceil  | 6
max   | 1
sum   | 1733.76873471724

整合

将上面三节的逻辑整合，得到以下的存储过程，给定一个表，返回其膨胀率。

CREATE OR REPLACE FUNCTION public.pg_table_bloat(relation regclass)
 RETURNS double precision
 LANGUAGE plpgsql
AS $function$
DECLARE
  _schemaname text;
  tuples      BIGINT := 0;
  pages       INTEGER := 0;
  nullheader  INTEGER:= 0;
  nullfrac    FLOAT := 0;
  datawidth   INTEGER :=0;
  avgtuplelen FLOAT :=24;
BEGIN
  SELECT
    relnamespace :: RegNamespace,
    reltuples,
    relpages
  into _schemaname, tuples, pages
  FROM pg_class
  Where oid = relation;

  SELECT
    23 + ceil(count(*) >> 3),
    max(null_frac),
    ceil(sum((1 - null_frac) * avg_width))
  into nullheader, nullfrac, datawidth
  FROM pg_stats
  where schemaname = _schemaname and tablename = relation :: text;

  SELECT (datawidth + 8 - (CASE WHEN datawidth%8=0 THEN 8 ELSE datawidth%8 END)) -- avg data len
    + (1 - nullfrac) * 24 + nullfrac * (nullheader + 8 - (CASE WHEN nullheader%8=0 THEN 8 ELSE nullheader%8 END))
    INTO avgtuplelen;

  raise notice '% %', nullfrac, datawidth;

  RETURN 1 - (ceil(tuples * avgtuplelen / 8168)) / pages;
END;
$function$

批量计算

对于监控而言，我们关注的往往不仅仅是一张表，而是库中所有的表。因此，可以将上面的膨胀率计算逻辑重写为批量计算的查询，并定义为视图便于使用：

DROP VIEW IF EXISTS monitor.pg_bloat_indexes CASCADE;
CREATE OR REPLACE VIEW monitor.pg_bloat_indexes AS
  WITH btree_index_atts AS (
      SELECT
        pg_namespace.nspname,
        indexclass.relname                                                          AS index_name,
        indexclass.reltuples,
        indexclass.relpages,
        pg_index.indrelid,
        pg_index.indexrelid,
        indexclass.relam,
        tableclass.relname                                                          AS tablename,
        (regexp_split_to_table((pg_index.indkey) :: TEXT, ' ' :: TEXT)) :: SMALLINT AS attnum,
        pg_index.indexrelid                                                         AS index_oid
      FROM ((((pg_index
        JOIN pg_class indexclass ON ((pg_index.indexrelid = indexclass.oid)))
        JOIN pg_class tableclass ON ((pg_index.indrelid = tableclass.oid)))
        JOIN pg_namespace ON ((pg_namespace.oid = indexclass.relnamespace)))
        JOIN pg_am ON ((indexclass.relam = pg_am.oid)))
      WHERE ((pg_am.amname = 'btree' :: NAME) AND (indexclass.relpages > 0))
  ), index_item_sizes AS (
      SELECT
        ind_atts.nspname,
        ind_atts.index_name,
        ind_atts.reltuples,
        ind_atts.relpages,
        ind_atts.relam,
        ind_atts.indrelid                                    AS table_oid,
        ind_atts.index_oid,
        (current_setting('block_size' :: TEXT)) :: NUMERIC   AS bs,
        8                                                    AS maxalign,
        24                                                   AS pagehdr,
        CASE
        WHEN (max(COALESCE(pg_stats.null_frac, (0) :: REAL)) = (0) :: FLOAT)
          THEN 2
        ELSE 6
        END                                                  AS index_tuple_hdr,
        sum((((1) :: FLOAT - COALESCE(pg_stats.null_frac, (0) :: REAL)) *
             (COALESCE(pg_stats.avg_width, 1024)) :: FLOAT)) AS nulldatawidth
      FROM ((pg_attribute
        JOIN btree_index_atts ind_atts
          ON (((pg_attribute.attrelid = ind_atts.indexrelid) AND (pg_attribute.attnum = ind_atts.attnum))))
        JOIN pg_stats ON (((pg_stats.schemaname = ind_atts.nspname) AND (((pg_stats.tablename = ind_atts.tablename) AND
                                                                          ((pg_stats.attname) :: TEXT =
                                                                           pg_get_indexdef(pg_attribute.attrelid,
                                                                                           (pg_attribute.attnum) :: INTEGER,
                                                                                           TRUE))) OR
                                                                         ((pg_stats.tablename = ind_atts.index_name) AND
                                                                          (pg_stats.attname = pg_attribute.attname))))))
      WHERE (pg_attribute.attnum > 0)
      GROUP BY ind_atts.nspname, ind_atts.index_name, ind_atts.reltuples, ind_atts.relpages, ind_atts.relam,
        ind_atts.indrelid, ind_atts.index_oid, (current_setting('block_size' :: TEXT)) :: NUMERIC, 8 :: INTEGER
  ), index_aligned_est AS (
      SELECT
        index_item_sizes.maxalign,
        index_item_sizes.bs,
        index_item_sizes.nspname,
        index_item_sizes.index_name,
        index_item_sizes.reltuples,
        index_item_sizes.relpages,
        index_item_sizes.relam,
        index_item_sizes.table_oid,
        index_item_sizes.index_oid,
        COALESCE(ceil((((index_item_sizes.reltuples * ((((((((6 + index_item_sizes.maxalign) -
                                                             CASE
                                                             WHEN ((index_item_sizes.index_tuple_hdr %
                                                                    index_item_sizes.maxalign) = 0)
                                                               THEN index_item_sizes.maxalign
                                                             ELSE (index_item_sizes.index_tuple_hdr %
                                                                   index_item_sizes.maxalign)
                                                             END)) :: FLOAT + index_item_sizes.nulldatawidth)
                                                          + (index_item_sizes.maxalign) :: FLOAT) - (
                                                           CASE
                                                           WHEN (((index_item_sizes.nulldatawidth) :: INTEGER %
                                                                  index_item_sizes.maxalign) = 0)
                                                             THEN index_item_sizes.maxalign
                                                           ELSE ((index_item_sizes.nulldatawidth) :: INTEGER %
                                                                 index_item_sizes.maxalign)
                                                           END) :: FLOAT)) :: NUMERIC) :: FLOAT) /
                        ((index_item_sizes.bs - (index_item_sizes.pagehdr) :: NUMERIC)) :: FLOAT) +
                       (1) :: FLOAT)), (0) :: FLOAT) AS expected
      FROM index_item_sizes
  ), raw_bloat AS (
      SELECT
        current_database()                                                           AS dbname,
        index_aligned_est.nspname,
        pg_class.relname                                                             AS table_name,
        index_aligned_est.index_name,
        (index_aligned_est.bs * ((index_aligned_est.relpages) :: BIGINT) :: NUMERIC) AS totalbytes,
        index_aligned_est.expected,
        CASE
        WHEN ((index_aligned_est.relpages) :: FLOAT <= index_aligned_est.expected)
          THEN (0) :: NUMERIC
        ELSE (index_aligned_est.bs *
              ((((index_aligned_est.relpages) :: FLOAT - index_aligned_est.expected)) :: BIGINT) :: NUMERIC)
        END                                                                          AS wastedbytes,
        CASE
        WHEN ((index_aligned_est.relpages) :: FLOAT <= index_aligned_est.expected)
          THEN (0) :: NUMERIC
        ELSE (((index_aligned_est.bs * ((((index_aligned_est.relpages) :: FLOAT -
                                          index_aligned_est.expected)) :: BIGINT) :: NUMERIC) * (100) :: NUMERIC) /
              (index_aligned_est.bs * ((index_aligned_est.relpages) :: BIGINT) :: NUMERIC))
        END                                                                          AS realbloat,
        pg_relation_size((index_aligned_est.table_oid) :: REGCLASS)                  AS table_bytes,
        stat.idx_scan                                                                AS index_scans
      FROM ((index_aligned_est
        JOIN pg_class ON ((pg_class.oid = index_aligned_est.table_oid)))
        JOIN pg_stat_user_indexes stat ON ((index_aligned_est.index_oid = stat.indexrelid)))
  ), format_bloat AS (
      SELECT
        raw_bloat.dbname                                             AS database_name,
        raw_bloat.nspname                                            AS schema_name,
        raw_bloat.table_name,
        raw_bloat.index_name,
        round(
            raw_bloat.realbloat)                                     AS bloat_pct,
        round((raw_bloat.wastedbytes / (((1024) :: FLOAT ^
                                         (2) :: FLOAT)) :: NUMERIC)) AS bloat_mb,
        round((raw_bloat.totalbytes / (((1024) :: FLOAT ^ (2) :: FLOAT)) :: NUMERIC),
              3)                                                     AS index_mb,
        round(
            ((raw_bloat.table_bytes) :: NUMERIC / (((1024) :: FLOAT ^ (2) :: FLOAT)) :: NUMERIC),
            3)                                                       AS table_mb,
        raw_bloat.index_scans
      FROM raw_bloat
  )
  SELECT
    format_bloat.database_name                    as datname,
    format_bloat.schema_name                      as nspname,
    format_bloat.table_name                       as relname,
    format_bloat.index_name                       as idxname,
    format_bloat.index_scans                      as idx_scans,
    format_bloat.bloat_pct                        as bloat_pct,
    format_bloat.table_mb,
    format_bloat.index_mb - format_bloat.bloat_mb as actual_mb,
    format_bloat.bloat_mb,
    format_bloat.index_mb                         as total_mb
  FROM format_bloat
  ORDER BY format_bloat.bloat_mb DESC;

COMMENT ON VIEW monitor.pg_bloat_indexes IS 'index bloat monitor';

虽然看上去很长，但查询该视图获取全库（3TB）所有表的膨胀率，计算只需要50ms。而且只需要访问统计数据，不需要访问关系本体，占用实例的IO。

表膨胀的处理

如果只是玩具数据库，或者业务允许每天有很长的停机维护时间，那么简单地在数据库中执行 VACUUM FULL 就可以了。但 VACUUM FULL 需要表上的排它读写锁，但对于需要不间断运行的数据库，我们就需要用到 pg_repack 来处理表的膨胀。

• 主页：http://reorg.github.io/pg_repack/

pg_repack 已经包含在了PostgreSQL官方的yum源中，因此可以直接通过 yum install pg_repack 安装。

yum install pg_repack10

pg_repack 的使用

与大多数PostgreSQL客户端程序一样，pg_repack 也通过类似的参数连接至PostgreSQL服务器。

在使用 pg_repack 之前，需要在待重整的数据库中创建 pg_repack 扩展

CREATE EXTENSION pg_repack

然后就可以正常使用了，几种典型的用法：

# 完全清理整个数据库，开5个并发任务，超时等待10秒
pg_repack -d <database> -j 5 -T 10

# 清理mydb中一张特定的表mytable，超时等待10秒
pg_repack mydb -t public.mytable -T 10

# 清理某个特定的索引 myschema.myindex，注意必须使用带模式的全名
pg_repack mydb -i myschema.myindex

详细的用法可以参考官方文档。

pg_repack 的策略

通常，如果业务存在峰谷周期，则可以选在业务低谷器进行整理。pg_repack 执行比较快，但很吃资源。在高峰期执行可能会影响整个数据库的性能表现，也有可能会导致复制滞后。

例如，可以利用上面两节提供的膨胀率监控视图，每天挑选膨胀最为严重的若干张表和若干索引进行自动重整。

#--------------------------------------------------------------#
# Name: repack_tables
# Desc: repack table via fullname
# Arg1: database_name
# Argv: list of table full name
# Deps: psql
#--------------------------------------------------------------#
# repack single table
function repack_tables(){
    local db=$1
    shift

    log_info "repack ${db} tables begin"
    log_info "repack table list: $@"

    for relname in $@
    do
        old_size=$(psql ${db} -Atqc "SELECT pg_size_pretty(pg_relation_size('${relname}'));")
        # kill_queries ${db}
        log_info "repack table ${relname} begin, old size: ${old_size}"
        pg_repack ${db} -T 10 -t ${relname}
        new_size=$(psql ${db} -Atqc "SELECT pg_size_pretty(pg_relation_size('${relname}'));")
        log_info "repack table ${relname} done , new size: ${old_size} -> ${new_size}"
    done

    log_info "repack ${db} tables done"
}

#--------------------------------------------------------------#
# Name: get_bloat_tables
# Desc: find bloat tables in given database match some condition
# Arg1: database_name
# Echo: list of full table name
# Deps: psql, monitor.pg_bloat_tables
#--------------------------------------------------------------#
function get_bloat_tables(){
    echo $(psql ${1} -Atq <<-'EOF'
    WITH bloat_tables AS (
        SELECT
          nspname || '.' || relname as relname,
          actual_mb,
          bloat_pct
        FROM monitor.pg_bloat_tables
        WHERE nspname NOT IN ('dba', 'monitor', 'trash')
        ORDER BY 2 DESC,3 DESC
    )
    -- 64 small + 16 medium + 4 large
    (SELECT relname FROM bloat_tables WHERE actual_mb < 256 AND bloat_pct > 40 ORDER BY bloat_pct DESC LIMIT 64) UNION
    (SELECT relname FROM bloat_tables WHERE actual_mb BETWEEN 256 AND 1024  AND bloat_pct > 30 ORDER BY bloat_pct DESC LIMIT 16) UNION
    (SELECT relname FROM bloat_tables WHERE actual_mb BETWEEN 1024 AND 4096  AND bloat_pct > 20 ORDER BY bloat_pct DESC  LIMIT 4);
EOF
)
}

这里，设置了三条规则：

• 从小于256MB，且膨胀率超过40%的小表中，选出TOP64
• 从256MB到1GB之间，且膨胀率超过40%的中表中，选出TOP16
• 从1GB到4GB之间，且膨胀率超过20%的大表中，选出TOP4

选出这些表，每天凌晨低谷自动进行重整。超过4GB的表手工处理。

但何时进行重整，还是取决于具体的业务模式。

pg_repack 的原理

pg_repack 的原理相当简单，它会为待重建的表创建一份副本。首先取一份全量快照，将所有活元组写入新表，并通过触发器将所有针对原表的变更同步至新表，最后通过重命名，使用新的紧实副本替换老表。而对于索引，则是通过PostgreSQL的 CREATE(DROP) INDEX CONCURRENTLY 完成的。

重整表

1. 创建一张与原表模式相同，但不带索引的空表。
2. 创建一张与原始表对应的日志表，用于记录 pg_repack 工作期间该表上发生的变更。
3. 为原始表添加一个行触发器，在相应日志表中记录所有 INSERT,DELETE,UPDATE 操作。
4. 将老表中的数据复制到新的空表中。
5. 在新表上创建同样的索引
6. 将日志表中的增量变更应用到新表上
7. 通过重命名的方式切换新旧表
8. 将旧的，已经被重命名掉的表 DROP 掉。

重整索引

1. 使用 CREATE INDEX CONCURRENTLY 在原表上创建新索引，保持与旧索引相同的定义。
2. Analyze 新索引，并将旧索引设置为无效，在数据目录中将新旧索引交换。
3. 删除旧索引。

pg_repack 的注意事项

• 重整开始之前，最好取消掉所有正在进行的 Vacuum 任务。

• 对索引做重整之前，最好能手动清理掉可能正在使用该索引的查询

• 如果出现异常的情况（譬如中途强制退出），有可能会留下未清理的垃圾，需要手工清理。可能包括：

  • 临时表与临时索引建立在与原表/索引同一个schema内
  • 临时表的名称为：${schema_name}.table_${table_oid}
  • 临时索引的名称为：${schema_name}.index_${table_oid}}
  • 原始表上可能会残留相关的触发器，需要手动清理。

• 重整特别大的表时，需要预留至少与该表及其索引相同大小的磁盘空间，需要特别小心，手动检查。

• 当完成重整，进行重命名替换时，会产生巨量的WAL，有可能会导致复制延迟，而且无法取消。

12 - 监控PG中的表大小

表的空间布局

宽泛意义上的 表（Table），包含了 本体表 与 TOAST表 两个部分：

• 本体表，存储关系本身的数据，即狭义的关系，relkind='r'。
• TOAST表，与本体表一一对应，存储过大的字段，relinkd='t'。

而每个表，又由 主体 与 索引 两个 关系（Relation） 组成（对本体表而言，可以没有索引关系）

• 主体关系：存储元组。
• 索引关系：存储索引元组。

每个 关系 又可能会有 四种分支：

• main: 关系的主文件，编号为0

• fsm：保存关于main分支中空闲空间的信息，编号为1

• vm：保存关于main分支中可见性的信息，编号为2

• init：用于不被日志记录（unlogged）的的表和索引，很少见的特殊分支，编号为3

每个分支存储为磁盘上的一到多个文件：超过1GB的文件会被划分为最大1GB的多个段。

综上所述，一个表并不是看上去那么简单，它由几个关系组成：

• 本体表的主体关系（单个）
• 本体表的索引（多个）
• TOAST表的主体关系（单个）
• TOAST表的索引（单个）

而每个关系实际上可能又包含了1~3个分支：main（必定存在），fsm，vm。

获取表的附属关系

使用下列查询，列出所有的分支oid。

select
  nsp.nspname,
  rel.relname,
  rel.relnamespace    as nspid,
  rel.oid             as relid,
  rel.reltoastrelid   as toastid,
  toastind.indexrelid as toastindexid,
  ind.indexes
from
  pg_namespace nsp
  join pg_class rel on nsp.oid = rel.relnamespace
  , LATERAL ( select array_agg(indexrelid) as indexes from pg_index where indrelid = rel.oid) ind
  , LATERAL ( select indexrelid from pg_index where indrelid = rel.reltoastrelid) toastind
where nspname not in ('pg_catalog', 'information_schema') and rel.relkind = 'r';

 nspname |  relname   |  nspid  |  relid  | toastid | toastindexid |      indexes
---------+------------+---------+---------+---------+--------------+--------------------
 public  | aoi        | 4310872 | 4320271 | 4320274 |      4320276 | {4325606,4325605}
 public  | poi        | 4310872 | 4332324 | 4332327 |      4332329 | {4368886}

统计函数

PG提供了一系列函数用于确定各个部分占用的空间大小。

虽然在物理上一张表由这么多文件组成，但从逻辑上我们通常只关心两个东西的大小：表与索引。因此这里要用到的主要就是两个函数：pg_indexes_size 与 pg_table_size，对普通表其和为 pg_total_relation_size。

而通常表大小的部分可以这样计算：

 pg_table_size(relid)
     = pg_relation_size(relid, 'main')
     + pg_relation_size(relid, 'fsm')
     + pg_relation_size(relid, 'vm')
     + pg_total_relation_size(reltoastrelid)

 pg_indexes_size(relid)
     = (select sum(pg_total_relation_size(indexrelid)) where indrelid = relid)

注意，TOAST表也有自己的索引，但有且仅有一个，因此使用 pg_total_relation_size(reltoastrelid) 可计算TOAST表的整体大小。

例：统计某一张表及其相关关系UDTF

SELECT
  oid,
  relname,
  relnamespace::RegNamespace::Text               as nspname,
  relkind                                        as relkind,
  reltuples                                      as tuples,
  relpages                                       as pages,
  pg_total_relation_size(oid)                    as size
  FROM pg_class
WHERE oid = ANY(array(SELECT 16418 as id -- main
UNION ALL SELECT indexrelid FROM pg_index WHERE indrelid = 16418 -- index
UNION ALL SELECT reltoastrelid FROM pg_class WHERE oid = 16418)); -- toast

可以将其包装为UDTF：pg_table_size_detail，便于使用：

CREATE OR REPLACE FUNCTION pg_table_size_detail(relation RegClass)
  RETURNS TABLE(
    id      oid,
    pid     oid,
    relname name,
    nspname text,
    relkind "char",
    tuples  bigint,
    pages   integer,
    size    bigint
  )
AS $$
BEGIN
  RETURN QUERY
  SELECT
    rel.oid,
    relation::oid,
    rel.relname,
    rel.relnamespace :: RegNamespace :: Text as nspname,
    rel.relkind                              as relkind,
    rel.reltuples::bigint                    as tuples,
    rel.relpages                             as pages,
    pg_total_relation_size(oid)              as size
  FROM pg_class rel
  WHERE oid = ANY (array(
      SELECT relation as id -- main
      UNION ALL SELECT indexrelid FROM pg_index WHERE indrelid = relation -- index
      UNION ALL SELECT reltoastrelid FROM pg_class WHERE oid = relation)); -- toast
END;
$$
LANGUAGE PlPgSQL;

SELECT * FROM pg_table_size_detail(16418);

返回结果样例：

geo=# select * from  pg_table_size_detail(4325625);
   id    |   pid   |        relname        | nspname  | relkind |  tuples  |  pages  |    size
---------+---------+-----------------------+----------+---------+----------+---------+-------------
 4325628 | 4325625 | pg_toast_4325625      | pg_toast | t       |   154336 |   23012 |   192077824
 4419940 | 4325625 | idx_poi_adcode_btree  | gaode    | i       | 62685464 |  172058 |  1409499136
 4419941 | 4325625 | idx_poi_cate_id_btree | gaode    | i       | 62685464 |  172318 |  1411629056
 4419942 | 4325625 | idx_poi_lat_btree     | gaode    | i       | 62685464 |  172058 |  1409499136
 4419943 | 4325625 | idx_poi_lon_btree     | gaode    | i       | 62685464 |  172058 |  1409499136
 4419944 | 4325625 | idx_poi_name_btree    | gaode    | i       | 62685464 |  335624 |  2749431808
 4325625 | 4325625 | gaode_poi             | gaode    | r       | 62685464 | 2441923 | 33714962432
 4420005 | 4325625 | idx_poi_position_gist | gaode    | i       | 62685464 |  453374 |  3714039808
 4420044 | 4325625 | poi_position_geohash6 | gaode    | i       | 62685464 |  172058 |  1409499136

例：关系大小详情汇总

select
  nsp.nspname,
  rel.relname,
  rel.relnamespace    as nspid,
  rel.oid             as relid,
  rel.reltoastrelid   as toastid,
  toastind.indexrelid as toastindexid,
  pg_total_relation_size(rel.oid)  as size,
  pg_relation_size(rel.oid) + pg_relation_size(rel.oid,'fsm')
  + pg_relation_size(rel.oid,'vm') as relsize,
  pg_indexes_size(rel.oid)         as indexsize,
  pg_total_relation_size(reltoastrelid) as toastsize,
  ind.indexids,
  ind.indexnames,
  ind.indexsizes
from pg_namespace nsp
  join pg_class rel on nsp.oid = rel.relnamespace
  ,LATERAL ( select indexrelid from pg_index where indrelid = rel.reltoastrelid) toastind
  , LATERAL ( select  array_agg(indexrelid) as indexids,
                      array_agg(indexrelid::RegClass) as indexnames,
                      array_agg(pg_total_relation_size(indexrelid)) as indexsizes
              from pg_index where indrelid = rel.oid) ind
where nspname not in ('pg_catalog', 'information_schema') and rel.relkind = 'r';

13 - PG服务器日志常规配置

建议配置PostgreSQL的日志格式为CSV，方便分析，而且可以直接导入PostgreSQL数据表中。

日志相关配置项

log_destination ='csvlog'
logging_collector =on
log_directory ='log'
log_filename ='postgresql-%a.log'
log_min_duration_statement =1000
log_checkpoints =on
log_lock_waits =on
log_statement ='ddl'
log_replication_commands =on
log_timezone ='UTC'
log_autovacuum_min_duration =1000

track_io_timing =on
track_functions =all
track_activity_query_size =16384

日志收集

如果需要从外部收集日志，可以考虑使用filebeat。

filebeat.prospectors:

## input
- type: log
enabled: true
paths:
- /var/lib/postgresql/data/pg_log/postgresql-*.csv
document_type: db-trace
tail_files: true
multiline.pattern: '^20\d\d-\d\d-\d\d'
multiline.negate: true
multiline.match: after
multiline.max_lines: 20
max_cpus: 1

## modules
filebeat.config.modules:
path: ${path.config}/modules.d/*.yml
reload.enabled: false

## queue
queue.mem:
events: 1024
flush.min_events: 0
flush.timeout: 1s

## output
output.kafka:
hosts: ["10.10.10.10:9092","x.x.x.x:9092"]
topics:
- topic: 'log.db'

CSV日志格式

很有趣的想法，将CSV日志弄成PostgreSQL表，对于分析而言非常方便。

原始的csv日志格式定义如下：

日志表的结构定义
create table postgresql_log
(
  log_time               timestamp,
  user_name              text,
  database_name          text,
  process_id             integer,
  connection_from        text,
  session_id             text   not null,
  session_line_num       bigint not null,
  command_tag            text,
  session_start_time     timestamp with time zone,
  virtual_transaction_id text,
  transaction_id         bigint,
  error_severity         text,
  sql_state_code         text,
  message                text,
  detail                 text,
  hint                   text,
  internal_query         text,
  internal_query_pos     integer,
  context                text,
  query                  text,
  query_pos              integer,
  location               text,
  application_name       text,
  PRIMARY KEY (session_id, session_line_num)
);

导入日志

日志是结构良好的CSV，（CSV允许跨行记录），直接使用COPY命令导入即可。

COPY postgresql_log FROM '/var/lib/pgsql/data/pg_log/postgresql.log' CSV DELIMITER ',';

映射日志

当然，除了把日志直接拷贝到数据表里分析，还有一种办法，可以让PostgreSQL直接将自己的本地CSVLOG映射为一张外部表。以SQL的方式直接进行访问。

CREATE SCHEMA IF NOT EXISTS monitor;

-- search path for su
ALTER ROLE postgres SET search_path = public, monitor;
SET search_path = public, monitor;

-- extension
CREATE EXTENSION IF NOT EXISTS file_fdw WITH SCHEMA monitor;

-- log parent table: empty
CREATE TABLE monitor.pg_log
(
  log_time               timestamp(3) with time zone,
  user_name              text,
  database_name          text,
  process_id             integer,
  connection_from        text,
  session_id             text,
  session_line_num       bigint,
  command_tag            text,
  session_start_time     timestamp with time zone,
  virtual_transaction_id text,
  transaction_id         bigint,
  error_severity         text,
  sql_state_code         text,
  message                text,
  detail                 text,
  hint                   text,
  internal_query         text,
  internal_query_pos     integer,
  context                text,
  query                  text,
  query_pos              integer,
  location               text,
  application_name       text,
  PRIMARY KEY (session_id, session_line_num)
);
COMMENT ON TABLE monitor.pg_log IS 'PostgreSQL csv log schema';
-- local file server
CREATE SERVER IF NOT EXISTS pg_log FOREIGN DATA WRAPPER file_fdw;
-- Change filename to actual path
CREATE FOREIGN TABLE IF NOT EXISTS monitor.pg_log_mon() INHERITS (monitor.pg_log) SERVER pg_log OPTIONS (filename '/pg/data/log/postgresql-Mon.csv', format 'csv');
CREATE FOREIGN TABLE IF NOT EXISTS monitor.pg_log_tue() INHERITS (monitor.pg_log) SERVER pg_log OPTIONS (filename '/pg/data/log/postgresql-Tue.csv', format 'csv');
CREATE FOREIGN TABLE IF NOT EXISTS monitor.pg_log_wed() INHERITS (monitor.pg_log) SERVER pg_log OPTIONS (filename '/pg/data/log/postgresql-Wed.csv', format 'csv');
CREATE FOREIGN TABLE IF NOT EXISTS monitor.pg_log_thu() INHERITS (monitor.pg_log) SERVER pg_log OPTIONS (filename '/pg/data/log/postgresql-Thu.csv', format 'csv');
CREATE FOREIGN TABLE IF NOT EXISTS monitor.pg_log_fri() INHERITS (monitor.pg_log) SERVER pg_log OPTIONS (filename '/pg/data/log/postgresql-Fri.csv', format 'csv');
CREATE FOREIGN TABLE IF NOT EXISTS monitor.pg_log_sat() INHERITS (monitor.pg_log) SERVER pg_log OPTIONS (filename '/pg/data/log/postgresql-Sat.csv', format 'csv');
CREATE FOREIGN TABLE IF NOT EXISTS monitor.pg_log_sun() INHERITS (monitor.pg_log) SERVER pg_log OPTIONS (filename '/pg/data/log/postgresql-Sun.csv', format 'csv');