跳转到主要内容

17.2 单机分析能力

PostgreSQL 的“单机”不是“单进程、单线程、每次从原表重算”。

在引入分布式之前，至少有五个正交杠杆：

减少访问的数据       -> 选择性索引、分区裁剪
并行处理必要的数据   -> parallel scan/join/aggregate
缩小每次处理的粒度   -> 物化汇总、批处理
利用物理相关性       -> BRIN、聚簇/装载顺序
隔离不同负载         -> 会话护栏、连接池、offline replica

每个杠杆解决不同问题。把它们都叫“性能优化”会丢失决策边界。

17.2.1 并行扫描、连接、聚合与限制

并行计划的基本结构

PostgreSQL 在计划树中使用 Gather 或 Gather Merge 汇集 worker 的结果：

leader
  Gather / Gather Merge
    worker 0 -> parallel-aware subtree
    worker 1 -> parallel-aware subtree
    ...

Gather 不保留 worker 输出顺序；Gather Merge 合并已经排序的并行流。 Gather 下面并非每个节点都自动并行。只有 parallel-aware 的 scan、join、 aggregate 等节点能让 workers 分担输入；普通节点可能在每个 worker 内分别 执行，也可能只在 leader 上执行。

PostgreSQL 官方 Parallel Query 把并行扫描、连接、聚合、append 与 parallel safety 分开说明。读计划时应 沿 plan tree 判断“谁分担数据、谁合并结果”，而不是只搜索一个 Gather。

本章的并行聚合

local-parallel-plan.sql 为冻结查询设置 一个可重复的实验上下文：

SET max_parallel_workers_per_gather = 2;
SET min_parallel_table_scan_size = 0;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;

EXPLAIN (
  ANALYZE,
  BUFFERS,
  COSTS OFF,
  SUMMARY OFF,
  TIMING OFF
)
SELECT
  tenant_id,
  date_trunc('month', occurred_on::timestamp)::date
    AS month_start,
  count(*) AS sale_count,
  sum(units)::bigint AS unit_count,
  sum(amount)::numeric(18,2) AS amount_total
FROM shop_ch17.sales_fact
GROUP BY tenant_id, month_start
ORDER BY tenant_id, month_start;

冻结计划：

Sort (actual rows=32 loops=1)
  -> Finalize HashAggregate (actual rows=32 loops=1)
       -> Gather (actual rows=96 loops=1)
            Workers Planned: 2
            Workers Launched: 2
            -> Partial HashAggregate (actual rows=32 loops=3)
                 -> Parallel Seq Scan on sales_fact
                      actual rows=80000 loops=3

读法：

  1. leader 与两个 worker 合计三个参与者；
  2. 每个参与者扫描约 80,000 行；
  3. 每个参与者产出 32 个 partial groups；
  4. Gather 收到约 96 行；
  5. finalize aggregate 合并成 32 行；
  6. 最后按租户和月份排序。

这比“三个人一起扫 24 万行”更精确：并行收益来自把大量输入压成少量 partial state，再让 leader 合并。若每个 worker 都输出海量行，leader 可能成为瓶颈。

partial/final aggregate 的条件

聚合要能并行拆分，必须有可合并的中间状态。概念上：

worker partial state
  count = 100
  sum   = 935.50

another worker partial state
  count = 120
  sum   = 1101.20

combine/final
  count = 220
  sum   = 2036.70

某些聚合、表达式、函数或语义无法安全拆分，就不会出现 partial/final aggregate。用户自定义函数默认不是 parallel safe；必须由作者基于真实行为 正确标记，不能为了得到并行计划而随意改 catalog。

计划能并行，不表示执行一定并行

计划显示：

Workers Planned: 2

执行证据还要看：

Workers Launched: 2

可用 worker 受多个上限和当前占用影响，例如：

max_worker_processes
max_parallel_workers
max_parallel_workers_per_gather
other sessions already using workers

如果执行时拿不到 worker，leader 可能独自执行 Gather 以下部分。因此容量 测试必须在代表性并发下观察 launched，而不是从单会话计划推断。

PostgreSQL 官方 When Can Parallel Query Be Used? 还列出写入、行锁、cursor、parallel-unsafe function、嵌套并行和 worker 资源不足等限制。

并行扫描

常见 parallel-aware 扫描包括：

Parallel Seq Scan
Parallel Index Scan
Parallel Index Only Scan
Parallel Bitmap Heap Scan

它们适合的访问形状不同：

  • 大范围低选择性读取常适合 parallel seq scan；
  • 有序 B-tree 与查询方向匹配时可并行 index scan；
  • visibility map 允许时 index-only 可减少 heap 访问；
  • bitmap 路径适合聚合多个索引命中后批量访问 heap page。

不能把 Parallel Seq Scan 当成“没用索引所以坏”。24 万行几乎全参与月聚合， 顺序读并行处理可能正是正确路径。判断标准是选择性、缓存、物理布局、并发与 总体资源，而不是节点名字的好恶。

并行连接

并行连接可能让：

outer side produced in parallel
inner side shared or rebuilt per worker
join work divided among workers

不同 join 算法的资源行为不同：

  • nested loop 的 inner scan 可能在每个 worker 重复；
  • merge join 的 inner side 可能被多次执行；
  • parallel hash 可以共享 hash table；
  • skew、错误基数和 worker 数会改变收益。

因此“两个大表 JOIN 能否并行”不能只看顶层 Gather。要看每个 input 的 actual rows/loops、hash memory/batches、排序与 buffer。

并行不是免费 CPU

一条查询从 8 秒降到 3 秒，可能消耗更多总 CPU。对单用户很有利，对 100 个 并发报表可能降低系统总吞吐。

容量要同时看：

single-query latency
system throughput
queue time
CPU saturation
workers requested/launched
OLTP tail latency

一个常见策略是：

interactive OLTP role:
  low statement timeout
  limited parallelism

batch analytics role:
  controlled concurrency
  selected higher parallelism
  explicit work_mem/temp limits

不要只提高全局 max_parallel_workers_per_gather。

实验设置不是生产建议

本章把：

SET min_parallel_table_scan_size = 0;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;

用于稳定地产生教学计划。它们刻意降低并行门槛，不是生产基线。生产应让 cost model 在真实数据、硬件和并发下选择，并通过回归计划验证。

17.2.2 分区、物化视图、增量汇总与批处理

四种手段解决四个问题

手段 主要减少什么 不自动解决什么
分区 无关分区扫描与维护范围 单节点总容量、所有查询
物化视图 重复计算 自动实时增量、定义演进
增量汇总表 每次重扫历史 迟到修正、幂等与对账
批处理 峰值并发与重复启动 单批本身的坏计划

它们可以组合，但不能互相替代。

分区首先是数据管理边界

原生分区适合：

按时间快速 detach/drop 历史
按边界独立装载或维护
让明确谓词裁剪无关分区
缩小部分索引和 vacuum 的工作单元

它不是把数据自动放到多台机器。PostgreSQL 原生 declarative partitioning 仍可完全位于一个实例、一个 tablespace 和一个故障域。

设计分区前回答：

主要删除/归档边界是什么？
查询是否稳定携带分区键？
分区数量与规划成本是否可控？
唯一约束能否包含分区键？
跨分区更新和 default partition 如何处理？
备份、vacuum、索引和 schema change 如何编排？

本章协调端把外表挂到 LIST 分区父表，是为了展示租户裁剪和路由，不是把 原生分区冒充成分布式引擎。

物化视图保存一个可重建结果

本章：

CREATE MATERIALIZED VIEW
  shop_ch17.daily_tenant_summary AS
SELECT
  tenant_id,
  occurred_on,
  channel,
  count(*) AS sale_count,
  sum(units)::bigint AS unit_count,
  sum(amount)::numeric(18,2) AS amount_total
FROM shop_ch17.sales_fact
GROUP BY tenant_id, occurred_on, channel
WITH DATA;

CREATE UNIQUE INDEX daily_tenant_summary_pkey
ON shop_ch17.daily_tenant_summary (
  tenant_id,
  occurred_on,
  channel
);

冻结数据得到：

240,000 raw facts
  -> 2,880 tenant/day/channel summaries
  -> 32 tenant/month rows

原表月报计划：

Parallel Seq Scan on sales_fact
actual rows=80000 loops=3

汇总月报计划：

Seq Scan on daily_tenant_summary
actual rows=2880 loops=1

两个输出逐字节相同。这个对比证明的是“缩小输入粒度”，不是物化视图对所有 查询都快。

PostgreSQL 原生 refresh 不是自动增量维护

普通物化视图需要：

REFRESH MATERIALIZED VIEW shop_ch17.daily_tenant_summary;

或在满足条件时：

REFRESH MATERIALIZED VIEW CONCURRENTLY
  shop_ch17.daily_tenant_summary;

核心 PostgreSQL 不会因为 base table 新增一行，就自动把对应增量加进这个 物化视图。CONCURRENTLY 解决读可用性的一部分，并不把刷新变成免费，也不 替你定义迟到事实、删除、修正和失败恢复。

发布合同应固定：

refresh owner
schedule and trigger
maximum freshness lag
unique index prerequisite
expected duration and WAL
lock behavior
failure alert
retry/idempotency
late-arrival window
full rebuild path
definition version
checksum/reconciliation

官方 Materialized Views 说明结果持久化、不可直接更新和 refresh 行为。

增量汇总表是一项应用协议

若完整 refresh 太贵，可以自己维护 summary table：

raw immutable events
  -> watermark / changed key set
  -> recompute affected tenant/day buckets
  -> upsert summary
  -> record batch identity and source watermark
  -> reconcile checksum

推荐按“重算受影响桶”而非“对旧值直接 +delta”开始，因为：

  • 迟到事件可能修改历史日期；
  • 事件可能撤销或更正；
  • 重试必须幂等；
  • 聚合逻辑会升级；
  • min/max/distinct 一类聚合不容易用简单减加回滚；
  • 需要从 raw truth 完整重建。

一张稳健的汇总控制表可以记录：

CREATE TABLE summary_batch (
  batch_id          uuid PRIMARY KEY,
  definition_version text NOT NULL,
  source_from       timestamptz NOT NULL,
  source_to         timestamptz NOT NULL,
  started_at        timestamptz NOT NULL,
  finished_at       timestamptz,
  status            text NOT NULL,
  source_checksum   text,
  result_checksum   text
);

这比“每五分钟跑一条 UPSERT”多了一层治理，但也使失败可恢复、结果可解释。

批处理是调度与资源控制

把 100 个 dashboard 请求合并为一个定时汇总，减少的是：

duplicate scans
query startup
concurrency spikes
cache churn
client retries

批处理仍需要：

  • 明确 batch 边界和 watermark；
  • 限制最大运行时间与并发；
  • 避免与 checkpoint、backup、vacuum 高峰重叠；
  • 在失败后从确定位置重跑；
  • 不用一个长事务覆盖整个历史；
  • 控制 WAL、temp 和副本 lag；
  • 给消费者暴露最后成功批次与数据新鲜度。

分区与汇总的组合

一个常见设计：

raw facts partitioned by event month
daily summaries keyed by tenant/day
monthly closed partitions become immutable
current/late window can be recomputed
old raw partitions retained or archived by policy

好处是：

  • 新鲜窗口小；
  • 历史汇总稳定；
  • 迟到修正有明确范围；
  • 全量重建可按分区推进；
  • 对账可以逐分区做。

风险是出现两套粒度与状态机。必须写清：

哪张表是最终事实？
汇总多久可旧？
历史是否允许更正？
定义升级如何双跑？
消费者如何选择版本？
raw 删除后是否仍能重建？

17.2.3 列式能力候选必须写入版本基线

“列式”不是一个单一功能

候选可能提供：

columnar storage
vectorized execution
compression
late materialization
parallel scan
external file scan
cache/format conversion
specialized aggregate

一项产品或扩展拥有其中一个，不表示拥有全部。也不能从“压缩率更高”推导 “点查、更新、复制和恢复都更好”。

先写 workload fit

列式路径通常更适合：

  • 只读或追加为主；
  • 扫描少数列、很多行；
  • 聚合和过滤占主导；
  • 批量装载；
  • 更新/删除少；
  • 可以接受特定事务和索引限制。

行存 PostgreSQL 通常在以下方面仍有优势：

  • 高选择性点查；
  • 频繁小事务更新；
  • 丰富 B-tree/GIN/GiST/SP-GiST 索引；
  • 完整约束、触发器与扩展组合；
  • 成熟复制、PITR 和工具链；
  • 单一数据副本与事务语义。

真实系统常混合两类负载，所以问题通常不是“行存还是列存”，而是：

哪些数据、哪些查询、在哪个新鲜度和事务边界下使用哪条路径？

版本是功能的一部分

一个可执行基线至少固定：

PostgreSQL major/minor
extension/product exact version
operating system and package source
storage format version
required shared_preload_libraries
GUC baseline
CPU architecture and instruction set
license
supported backup/restore path
supported upgrade path
replica behavior
known incompatibilities

不能写：

uses columnar extension

而应写：

candidate X exact version Y
on PostgreSQL 18.x
package repository Z
validated on every Pigsty L1 node
backup/restore drill identifier ...

本章正式实验没有安装列式扩展，因此 baseline-v1.5-proposal.json 明确只验证行存、BRIN、物化和 loopback FDW。没有运行的候选不会出现在 “已验证”清单里。

查询兼容之外的基线

列式候选还要验证：

类别 问题
DML insert/update/delete/upsert/truncate 支持到哪？
DDL alter type、default、constraint、partition 如何？
索引 哪些 access method、unique、FK 可用？
MVCC snapshot、vacuum、HOT、freeze 如何变化？
WAL/复制 physical/logical、PITR、standby 是否支持？
扩展 PostGIS、vector、FDW、UDF 能否组合？
备份 工具是否理解存储格式？
升级 大版本与扩展版本如何排序？
观测 size、I/O、bloat、query metrics 是否可见？
许可 部署、节点、商业使用与再分发条件？

“SQL 跑通”只覆盖第一行的一小部分。

基准必须包含负面工作负载

不要只跑候选擅长的宽表聚合。还要包含：

single-row lookup
selective range query
high-concurrency small reads
batch insert
small update/delete
schema evolution
vacuum/compaction
backup while serving
restore and checksum
replica catch-up
node or process restart

选型不是找一个最高分，而是确认它在必要场景上没有不可接受的零分。

17.2.4 OLTP 与分析负载在同机共存的代价

共存争用表

资源 OLTP 典型需求 OLAP 典型行为 冲突
CPU 短请求低尾延迟 长扫描/聚合吞吐 worker 抢核心
shared buffers 热索引与热点页 大范围扫描 缓存污染
OS page cache 热数据 顺序历史读 热页被挤出
memory 小且稳定 sort/hash 波动 OOM/回收
storage 小随机 I/O、WAL 大顺序/临时 I/O 队列延迟
locks/snapshot 短事务 长快照/refresh vacuum/DDL
connections 短会话/池 少量长查询 slot 与队列
replicas 低 lag replay 与只读查询 recovery conflict

同一 SQL 在夜间快、白天慢，不一定是计划变化；可能是共存资源不同。

缓存命中率不能单独判断

分析大扫描可能有很高 shared hit，因为数据已经在缓存；它仍会消耗 CPU 并 驱逐其他热页。也可能有较低命中但利用高吞吐顺序读，对自己的完成时间尚可， 却让 OLTP 随机读尾延迟变差。

需要把：

database buffers
OS I/O
query latency
system throughput
OLTP tail latency

放在同一时间轴。

会话级护栏

对分析角色可以评审：

ALTER ROLE analyst SET statement_timeout = '10min';
ALTER ROLE analyst SET lock_timeout = '2s';
ALTER ROLE analyst SET idle_in_transaction_session_timeout = '1min';
ALTER ROLE analyst SET temp_file_limit = '20GB';
ALTER ROLE analyst SET work_mem = '64MB';
ALTER ROLE analyst SET max_parallel_workers_per_gather = 2;

数值只是示意，必须按容量计算。角色设置也不是资源管理器：它不能严格保证 CPU 百分比或 IOPS，仍需要连接池并发、作业调度、操作系统资源或实例隔离。

连接池与任务队列

分析任务应有独立入口和并发上限：

application request
  -> analytics queue
  -> bounded worker pool
  -> analyst database role
  -> statement/temp/parallel limits

这样过载首先表现为可观测排队，而不是所有查询同时进入数据库后互相拖垮。 队列本身要有：

deadline
priority
cancellation
deduplication
retry policy
idempotency
queue age alert

副本隔离不是免费复制

把报表放到只读副本可以隔离部分 CPU 和读 I/O，但仍共享：

  • primary 产生 WAL 的成本；
  • 网络带宽；
  • replay lag；
  • 长查询与 recovery conflict；
  • schema/extension 版本；
  • failover 时的角色变化；
  • 备份和维护体系。

还必须接受“副本可能比 primary 旧”。如果查询要求 read-your-writes 或刚提交 即见，不能无条件路由到异步副本。

Pigsty 4.5 把 offline 实例用于慢查询、ETL、OLAP 和交互查询隔离，也允许 在现有 replica 上设置 pg_offline_query。其当前行为与服务归属见 Cluster / Instance。 这是比直接分片更低一层的候选。

单独分析集群

若副本上的物理复制语义仍不合适，可以建立：

OLTP source
  -> logical replication / CDC / batch load
  -> independent analytical PostgreSQL cluster

它进一步隔离参数、存储、索引和维护，却引入：

data pipeline
schema propagation
freshness lag
replay/idempotency
DDL compatibility
backfill
dual-system reconciliation

是否比 Citus 或专用 OLAP 更合适，要由工作负载和运行模型决定。

何时单机能力已经被合理用尽

至少满足：

  • 大查询的扫描、连接、聚合路径合理；
  • worker planned/launched 与并发预算相符；
  • 选择性查询有正确索引；
  • 分区裁剪能消除无关数据；
  • spill 被量化并有会话级边界；
  • 重复历史计算已评估物化/汇总；
  • OLTP 与分析已有入口和资源隔离；
  • backup、vacuum、checkpoint、replica lag 一同压测；
  • 硬件纵向扩容与未来增长已建模；
  • 正确性和新鲜度仍满足。

只有到这一步，“单节点哪一种资源仍越界”才有明确答案。下一节据此定义何时 需要分布式，以及分片键会把哪些数据库语义变成应用必须承担的合同。

上一节：先证明单机边界 · 返回本章目录 · 下一节：何时需要分布式 · 查看全书目录 · 查看索引中心

最后修改 2026年8月28日 · chore: remove GitHub Pages workflow (83aa397)