25.3 SQL 可观测基线

数据库“忙”只是结果，SQL workload 才是来源之一。

要回答：

哪类语句消耗了时间？
调用量还是单次成本改变？
时间花在执行、计划、I/O、JIT、WAL 还是临时块？
慢是一直慢，还是 tail 中少量异常？
统计从什么时候开始？
query identity 是否稳定？
日志和计划采集付出了多少额外成本？
证据是否泄露业务值？

需要组合三个接口：

pg_stat_statements
  聚合 workload population

bounded logging
  离散慢语句、错误、锁等待、临时文件

auto_explain
  对满足政策的一部分执行自动记录计划

三者都有盲区，也都有成本。最危险的做法是为了“可观测”无界记录所有 SQL 和参数，结果既拖慢系统，又制造一份高密度敏感数据库。

25.3.1 `pg_stat_statements` 的统计口径与重置

它不是默认自动完整可用

pg_stat_statements 需要：

出现在 shared_preload_libraries；
PostgreSQL 重启后模块被加载；
每个需要查询视图的 database 安装 extension；
query identifier 可用；
查询角色有足够权限；
容量、track policy 和 reset 被声明。

检查：

SELECT name, setting, source
FROM pg_settings
WHERE name IN (
  'shared_preload_libraries',
  'compute_query_id',
  'pg_stat_statements.max',
  'pg_stat_statements.track',
  'pg_stat_statements.track_utility',
  'pg_stat_statements.track_planning',
  'pg_stat_statements.save'
)
ORDER BY name;

extension 可能不在 public：

SELECT
  e.extname,
  e.extversion,
  n.nspname AS extension_schema
FROM pg_extension AS e
JOIN pg_namespace AS n ON n.oid = e.extnamespace
WHERE e.extname = 'pg_stat_statements';

Pigsty 沙箱中的结果：

shared_preload_libraries        pg_stat_statements, auto_explain
compute_query_id                auto
pg_stat_statements.max          10000
pg_stat_statements.track        all
pg_stat_statements.track_utility off
pg_stat_statements.track_planning off
pg_stat_statements.save         on
extension version              1.12
extension schema               monitor

所以查询使用：

monitor.pg_stat_statements
monitor.pg_stat_statements_info

不要硬编码成 public.pg_stat_statements。

聚合键不是 query text

一行主要按以下身份聚合：

dbid
userid
queryid
toplevel

这意味着：

同一 queryid 在不同 database 分开；
不同执行角色分开；
top-level 与 nested statement 分开；
query text 是一个代表性 normalized text，不是主键；
同一可见文本仍可能因语义环境不同获得不同 queryid；
hash collision 在理论和实践上都可能发生。

因此关联时保留：

cluster / instance
dbid
userid or role class
queryid
toplevel
stats_since

只保存 queryid 而丢掉 database/user/toplevel，会把不同 workload 合并。

normalization 不是脱敏承诺

常量通常会被替换：

SELECT * FROM orders WHERE order_id = 123;

-- representative normalized form
SELECT * FROM orders WHERE order_id = $1;

但不能据此认为 query 列可以公开：

object 名可能包含客户或项目身份；
comment 可能含敏感上下文；
dynamic SQL 结构可能暴露值；
utility statement 的行为不同；
query text 与日志/错误组合可能重新识别业务； -权限边界本身说明它被视为敏感。

本章 evidence 明确：

query text exported   false
bind values exported  false
client address        false

queryid 是关联键，不是密码学身份

queryid 是内部 hash：

算法可能随 major version 改变；
collision 可能发生；
object identity 与 search_path 会影响语义； -相同显示文本可能代表不同解析对象；
failover/upgrade 前后要重新建立 baseline。

不要用 queryid：

做访问控制；
证明 SQL 完全相同；
做永久跨版本业务 ID；
取代 plan fingerprint；
取代 application operation class。

它适合：

workload 聚合；
before/after 比较；
dashboard 下钻；
与受限日志/plan 关联；
top-N 诊断。

从总时间拆成频率与单次成本

$$ \text{total execution time}

\text{calls} \times \text{mean execution time} $$

top total time 可能来自：

very frequent cheap query
rare extremely slow query
both

基本查询不要一开始读 text：

SELECT
  dbid,
  userid,
  queryid,
  toplevel,
  calls,
  round(total_exec_time::numeric, 2) AS total_exec_ms,
  round(mean_exec_time::numeric, 3) AS mean_exec_ms,
  round(min_exec_time::numeric, 3) AS min_exec_ms,
  round(max_exec_time::numeric, 3) AS max_exec_ms,
  rows,
  shared_blks_hit,
  shared_blks_read,
  temp_blks_written,
  wal_bytes,
  stats_since,
  minmax_stats_since
FROM monitor.pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 50;

注意：

min/max 易受单次异常影响；
mean 掩盖分布；
row count 的语义随 statement 类型变化；
block 是 PostgreSQL block，不是任意存储 byte；
I/O timing 依赖开关；
WAL byte 不等于 commit durability；
top-N 会漏掉排名外 workload。

用 delta，而不是跨 reset 比裸值

两个采样点：

t0: calls_0, total_exec_0, stats_since_0
t1: calls_1, total_exec_1, stats_since_1

只有 identity 与统计窗口连续时才计算：

$$ \Delta calls = calls_1 - calls_0 $$

$$ \text{window mean}

\frac{\Delta total_exec_time} {\Delta calls} $$

如果：

row 消失；
stats_since 改变；
postmaster restart；
extension reset；
entry deallocated 后重新创建；
failover 到另一成员；

就不能直接减。

`pg_stat_statements_info` 是解释入口

SELECT *
FROM monitor.pg_stat_statements_info;

核心：

dealloc
stats_reset

dealloc 增长表示 statement entry 因容量压力被丢弃。此时 top workload 可能被 churn 影响；应评估：

pg_stat_statements.max；
query shape 数量；
dynamic SQL；
reset； -内存成本； -是否需要更稳定的 application query pattern。

沙箱正式快照：

dealloc              0
stats_reset          2026-07-29T18:57:02Z
statement rows       194
calls                超过 100,000

这是一次窗口基线，不是永久容量结论。

每行还有自己的时间边界

PostgreSQL 18 的 statement 行包含：

stats_since
minmax_stats_since

如果只 reset 某条或只 reset min/max，不同 entry 的窗口可能不同。一个 dashboard 不能只在标题写全局“过去 24 小时”，却忽略每行实际开始时间。

reset 是破坏性观察动作

函数可以全局或选择性 reset；当前版本还支持只 reset min/max。它很有用，但会销毁比较基线。

原则：

incident capture
  never reset to make the graph easier

planned benchmark
  may reset in an isolated target with explicit evidence

production baseline
  prefer snapshots/deltas and recording rules

本章 L0 采集明确禁止：

pg_stat_statements_reset
pg_stat_reset*

如果必须 reset：

记录 request/approval；
exact target；
old reset time；
snapshot/hash；
reason；
expected observation gap；
downstream dashboard impact；
new baseline start。

planning 与 execution 并非一一对应

pg_stat_statements.track_planning=on 可以收集 plan count/time，但：

默认通常为 off；
对高并发相同 query，plan 统计更新会有明显开销；
prepared/cached plan 改变 plan/execute 次数关系；
执行失败与计划失败的记录条件不同；
utility 和 nested track policy 影响 population。

沙箱 track_planning=off，所以：

total_plan_time = 0

表示未收集，而不是规划不耗时。不要为了填满 dashboard 直接在生产开启；先做负载测试和决策评审。

`track=all` 的语义

top 只跟踪 top-level；all 还跟踪 nested statement。沙箱设置 all，再用 toplevel 区分。否则 stored procedure 或 function 内 SQL 可能看不见，或被误与 top-level 混合。

top-N 查询要保护系统

观察查询也消耗：

shared memory lock；
sort；
format/round；
dashboard 并发；
network；
结果存储。

生产查询建议：

SET LOCAL statement_timeout = '5s';
SET LOCAL lock_timeout = '500ms';

-- 只读、限制列、限制行、先 aggregate identity

不要每 5 秒在每个 database：

SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC;

尤其不要自动导出完整 query text。

建立 workload baseline

基线至少分：

calls rate
total/mean/max execution
rows per call
shared hit/read/write
temp blocks
WAL bytes
JIT time/count
planning if explicitly enabled
stats reset / entry age

按：

service operation
database
role class
queryid
release/change window

关联。不要仅按 instance；failover 后 workload 会移动。

25.3.2 慢语句、锁等待、临时文件与错误日志

慢日志是离散样本

核心设置：

SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN (
  'log_min_duration_statement',
  'log_min_duration_sample',
  'log_statement_sample_rate',
  'log_duration',
  'log_statement',
  'log_lock_waits',
  'deadlock_timeout',
  'log_temp_files',
  'log_min_error_statement',
  'log_parameter_max_length',
  'log_parameter_max_length_on_error'
)
ORDER BY name;

`log_min_duration_statement`

记录执行时间达到阈值的语句；0 表示记录全部，-1 关闭。

优点：

对超过阈值的 population 较完整；
可以看到离散 tail；
能与 queryid、错误和时间线关联。

代价：

I/O 与格式化；
log volume；
SQL/参数泄露；
高频略慢语句形成洪水； -日志系统成为新瓶颈。

`log_min_duration_sample`

达到 sample threshold 后，再按 log_statement_sample_rate 采样。它适合控制高流量环境的日志量，但“未出现”不能解释为“未发生”。

如果：

log_min_duration_sample = -1
log_statement_sample_rate = 1

采样路径仍是关闭的。不要只看 rate。

duration 的边界

语句 duration 与用户 end-to-end latency 不同。用户时间可能包括：

network
application queue
pool wait
server execution
result transfer
application processing
retry
commit reconciliation

PostgreSQL 慢日志只覆盖 server 侧语句范围。

lock wait 日志

log_lock_waits=on 在等待超过 deadlock_timeout 时记录。沙箱：

log_lock_waits   on
deadlock_timeout 50ms

这意味着：

超过约 50 ms 的 lock wait 有机会被记录；
更短等待可能很多但没有日志；
deadlock detection cadence 与日志量相关；
不能为了更多日志随意降低 timeout； -日志是离散事件，当前 lock view 是即时状态。

调查组合：

user latency window
pg_stat_activity current wait
pg_locks / pg_blocking_pids
lock wait logs
deadlock counter
application transaction boundary

temporary file

log_temp_files 在临时文件删除时记录超过阈值的文件。沙箱为：

1024 kB

这类日志说明：

某操作产生了 temp file；
大小达到记录政策；
日志时刻可能是删除时刻，不是创建/峰值时刻。

关联：

pg_stat_database.temp_files/temp_bytes；
pg_stat_statements.temp_blks_read/written；
queryid；
plan；
sort/hash/window；
workload concurrency；
work_mem policy。

不要看到 temp 就全局提高 work_mem。它按 operation/node/worker 使用，高并发会把一个小改动放大成内存风险。

error log 与用户 outcome

PostgreSQL error 包含 SQLSTATE、severity、detail、context 等；应用可能：

retry 后成功；
返回失败；
超时但 commit； -屏蔽错误； -将一个 DB error 映射成不同业务 outcome。

所以错误日志要与应用 outcome 关联，而不是用 log line 数直接做 availability 分子。

稳定聚合维度：

cluster / instance / database
severity / SQLSTATE class
application / operation class
release

不适合作 label：

full message
detail
statement
parameter
customer/order/tenant

structured CSV/JSON 不会自动安全

沙箱使用：

logging_collector on
log_destination csvlog
log_directory /pg/log/postgres
log_filename postgresql-%a.log

CSV 方便 Vector 解析，但：

statement 字段仍可能敏感；
detail/context 可能含值；
file permission/collector group 需要评审； -集中存储扩大读取面；
retention 必须独立设置。

从 metric 到 log，而不是无界 log query

正确流程：

metric identifies:
  service, environment, operation, time window

event identifies:
  release/change boundary

log query:
  bounded window + stable fields + maximum rows

result:
  aggregate error classes, do not export bodies

本章诊断包规定：

log query limit  1000
body export      forbidden

需要正文时，应在受限界面临时查看并按事件政策处理，而不是复制进公共工单。

日志缺失也有语义

没有日志可能是：

事件未发生；
threshold 未达到；
sampling 丢失；
collector/Vector 失败；
rotation/retention；
parse schema drift； -权限或查询错误； -日志写入阻塞；
service 在另一 instance。

必须同时观察 log pipeline。

25.3.3 `auto_explain` 的采样、嵌套语句与开销

`auto_explain` 解决什么

pg_stat_statements 告诉你：

which queryid is costly

EXPLAIN/EXPLAIN ANALYZE 告诉你：

how one plan is structured and executed

auto_explain 在满足政策时自动把 plan 写入日志，适合捕捉难以手工复现的慢执行。

它不是零成本“打开即可”。

最重要的开关

SELECT name, setting, unit, source
FROM pg_settings
WHERE name LIKE 'auto_explain.%'
ORDER BY name;

设置	问题
`log_min_duration`	多慢才记录；默认 `-1` 不启用
`sample_rate`	满足阈值的 statement 采样多少
`log_analyze`	是否实际执行统计进入 plan
`log_timing`	`ANALYZE` 时是否逐节点计时
`log_buffers`	是否记录 buffer 使用
`log_wal`	是否记录 WAL 使用
`log_nested_statements`	function 内语句是否记录
`log_parameter_max_length`	参数记录长度
`log_format`	text/xml/json/yaml
`log_verbose`	是否输出额外细节
`log_settings`	是否记录影响 planning 的设置
`log_triggers`	trigger 统计

`log_analyze` 的隐藏成本

官方文档特别警告：启用 log_analyze 后，即使最终语句没有达到 log_min_duration 而不写日志，也可能需要为所有语句做 per-plan-node timing。

如果同时：

log_analyze=on
log_timing=on
sample_rate=1

开销可能非常高，尤其是大量短 node 的 workload。

log_timing=off 可以保留 rows 等实际统计并减少逐节点时钟调用，但仍需实测。

沙箱的真实设置

本章只读检查发现：

auto_explain.log_min_duration       1000 ms
auto_explain.log_analyze            on
auto_explain.log_timing             on
auto_explain.log_nested_statements  on
auto_explain.sample_rate            1
auto_explain.log_parameter_max_length -1

这不是推荐模板。它是一个需要单独 overhead 与泄露评审的真实基线：

sample_rate=1 没有限制 eligible statement；
log_analyze + log_timing 有全局计时成本；
nested 可能显著放大日志量；
parameter length -1 允许完整参数； -阈值 1 秒只限制最终写 plan，不一定消除 instrumentation cost。

本章不 reload、不改参数，只把风险记录下来。

`BUFFERS` 与 `WAL` 依赖 `ANALYZE`

自动计划中的实际 buffer/WAL 信息要求 analyze path。不能关闭 analyze 后仍假装获得运行时资源。

设计取舍：

plan shape only
  lower runtime detail, lower cost

analyze without timing
  actual rows/buffer possibility, less per-node clock cost

analyze with timing
  richer node time, potentially high overhead

必须在代表性 workload 上量化。

nested statement

function、trigger、procedure 内可能包含真正慢的 SQL：

top-level CALL
  cheap wrapper
  nested SQL expensive

log_nested_statements=on 能看见，但会：

增加 volume； -重复上下文； -暴露更多 query/parameter； -让一个 top-level 请求产生多份 plan。

同时使用 pg_stat_statements.track=all 与 toplevel 关联。

采样测试应覆盖 tail

开启前在隔离 workload 回答：

baseline TPS / p50 / p99
CPU and system time
log bytes per second
collector lag
plan count
short statement overhead
long statement capture probability
nested amplification
parameter redaction
rollback path

不要只跑一条慢查询说“开销不大”。

不要在事故中临时全开

事故中：

log_min_duration=0
log_analyze=on
log_timing=on
sample_rate=1

可能：

-进一步降低吞吐； -加剧 disk/log pipeline； -泄露参数； -改变被观察 workload； -制造新的故障。

更安全顺序：

用现有 SLI、activity、wait、queryid 缩小范围；
使用已有日志与 plan；
评估只对 session/role/database 的受控方法；
明确 timeout、采样、持续时间和 rollback；
由变更政策批准；
观察 overhead；
到期自动撤销并验证。

`auto_explain` 不是 plan history 系统

计划只在满足政策时进入日志；sampling、rotation、retention、parse 都会造成缺口。若要做 plan regression：

在测试/发布流程保存 EXPLAIN (FORMAT JSON)；
记录 schema/statistics/version/settings；
与 queryid/plan fingerprint 关联； -不要把生产日志当完整 plan catalog； -不要在没有参数与数据分布语义时做机械 diff。

25.3.4 日志不得泄漏密码、令牌和敏感参数

SQL observability 是高敏感数据面

可能出现：

password in connection/DDL statement
API token in INSERT/UPDATE
tenant/customer/order identity
email/phone/address
medical/payment attributes
session variable
RLS predicate context
error detail containing row values
dynamic SQL comment
connection URI

PostgreSQL 文档明确警告，statement logging 可能暴露敏感数据，甚至明文密码。

参数记录的两套限制

log_parameter_max_length
  非错误 statement 的参数记录限制

log_parameter_max_length_on_error
  错误发生时的参数记录限制

一般语义：

0    disable
-1   no length limit
N    truncate to N bytes

auto_explain.log_parameter_max_length 又是独立设置。

沙箱：

log_parameter_max_length           -1
log_parameter_max_length_on_error   0
auto_explain.log_parameter_max_length -1

这表示错误路径参数被禁用，但普通慢 statement/auto_explain 仍可能完整记录参数。是否安全取决于 protocol、query 和应用，不能因为“一项为 0”宣布无泄露。

`log_statement` 的风险

log_statement=all 会记录每条 statement；DDL 中尤其可能出现：

CREATE ROLE app LOGIN PASSWORD '...';
ALTER ROLE app PASSWORD '...';

即使参数化 DML 避免 literal，DDL、utility、comment 和 dynamic SQL 仍可能带秘密。生产不得把“排障方便”当默认充分理由。

文件模式：0600 不是唯一安全答案

PostgreSQL log_file_mode 控制 collector 创建文件的 mode。

0600
  PostgreSQL OS owner only

0640
  owner read/write + restricted collector group read

Pigsty 使用 Vector 收集日志时，受控 group read 可能是合理实现。安全要求是：

not world-readable
collector group explicitly declared
membership minimal
no interactive user by default
directory traversal restricted
rotation preserves mode
central storage ACL reviewed

沙箱为 0640。本章没有证明 collector group membership 已完成生产审批，因此将其作为待评审事实，而不是自动判定安全或不安全。

传输到 VictoriaLogs 扩大了边界

原本只有 database host 上的日志，集中后可能被：

Grafana data source；
log query API；
incident automation；
backup/export； -多个 operator

访问。

必须重新定义：

ingestion TLS/auth
tenant isolation
query role
retention
deletion
export
redaction
audit
backup

“源文件权限正确”不能证明集中日志安全。

metric label、alert annotation、ticket 是二次泄露面

最常见事故不是直接开放 log file，而是自动化复制：

log body
  -> alert annotation
      -> chat webhook
          -> ticket
              -> email
                  -> postmortem

本章诊断包只导出：

error class/count；
queryid；
database/role class；
time window；
release/change id；
source link without credentials。

不导出：

statement；
bind value；
log body；
client address；
tenant/order/customer。

redaction 要在尽量靠近 source 的位置

优先级：

应用不把 secret 放进 SQL/comment；
参数化查询；
PostgreSQL logging policy 限制；
collector parse/redact；
storage ACL/retention；
alert/evidence allowlist。

最后一步 regex 不是万能防线：

数据格式会变化； -编码/截断会绕过； -新字段未被匹配； -secret 可能已进入上游缓存/备份。

query text 的受控交互查看

真正排障有时需要 SQL text。正确做法不是绝对禁止人看，而是区分：

machine evidence
  no query text

interactive diagnosis
  time-bounded pg_read_all_stats or narrower role
  approved operator
  restricted UI/session
  no casual copy
  audit and expiry

public/reference summary
  queryid and aggregates only

这既保留诊断能力，也控制扩散。

prepared statement 不自动消除泄露

参数化可以让 statement text 不含 literal，但：

bind parameter logging 仍可能记录值； -错误 detail/context 可能包含； -应用 comment/baggage 可能包含；
object/table/schema 名仍可能敏感；
auto_explain parameter logging 是独立开关。

所以要检查完整链路，不只看应用 ORM。

慢日志与审计日志不是一回事

慢日志回答 performance sample；审计回答谁在何时对什么对象做了什么。两者：

-目的不同； -保留不同； -访问不同； -完整性要求不同； -敏感性不同。

不要让 log_statement=all 同时冒充性能、审计、合规和安全检测。需要审计时，使用明确的审计政策、对象范围和审阅流程。

安全基线查询

SELECT name, setting, source
FROM pg_settings
WHERE name IN (
  'logging_collector',
  'log_destination',
  'log_directory',
  'log_filename',
  'log_file_mode',
  'log_statement',
  'log_min_duration_statement',
  'log_min_duration_sample',
  'log_statement_sample_rate',
  'log_parameter_max_length',
  'log_parameter_max_length_on_error'
)
OR name LIKE 'auto_explain.%'
ORDER BY name;

这只是配置事实，还要复核：

actual file mode/owner/group；
directory mode；
Vector identity/config；
VictoriaLogs access/retention；
sample log 是否被正确 parse/redact；
alert/template 是否复制 body；
backup 是否包含日志。

本章 L0 只记录配置，不读取日志正文。

SQL 可观测基线清单

extension
  preload / version / schema / query id

population
  track / utility / nested / max

time
  global reset / per-row stats_since / minmax_stats_since

cost
  planning / timing / auto_explain / logging volume

security
  query visibility / parameters / file group / central log ACL

diagnosis
  calls / exec / rows / blocks / temp / WAL

correlation
  service / operation / release / database / role / queryid

boundary
  no query text in automatic evidence

本节验收

你应当能够解释：

为什么 extension schema 不能假设是 public；
一行 pg_stat_statements 的四个核心聚合维度；
normalization 为什么不是脱敏保证；
queryid 为什么不能做永久或安全身份；
calls、total、mean、max 各说明什么；
dealloc、global reset、stats_since、minmax_stats_since 的差异；
track_planning=off 时 plan time 为零意味着什么；
慢语句、采样慢语句、lock wait 和 temp file 日志各何时产生；
auto_explain.log_analyze + log_timing 为什么可能给所有语句带来成本；
nested statement 为什么同时增加诊断价值与泄露/volume；
0640 如何在受控 collector group 下成立；
为什么自动 evidence 只保留 queryid 与聚合，而交互查看另行授权。

上一节：PostgreSQL 核心运行信号 · 返回本章目录 · 下一节：把观察契约变成告警 · 查看全书目录 · 查看索引中心

最后更新于 2026-07-30

25.2 PostgreSQL 核心运行信号 25.4 把观察契约变成告警