# 单机分析能力

LLMS 索引： [llms.txt](/llms.txt)

---

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

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

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

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

## 17.2.1 并行扫描、连接、聚合与限制 {#item-17-2-1}

### 并行计划的基本结构

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

```text
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](https://www.postgresql.org/docs/18/parallel-query.html)
把并行扫描、连接、聚合、append 与 parallel safety 分开说明。读计划时应
沿 plan tree 判断“谁分担数据、谁合并结果”，而不是只搜索一个 `Gather`。

### 本章的并行聚合

[`local-parallel-plan.sql`](/labs/ch17/local-parallel-plan.sql) 为冻结查询设置
一个可重复的实验上下文：

```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;
```

冻结计划：

```text
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 的条件

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

```text
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。

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

计划显示：

```text
Workers Planned: 2
```

执行证据还要看：

```text
Workers Launched: 2
```

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

```text
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?](https://www.postgresql.org/docs/18/when-can-parallel-query-be-used.html)
还列出写入、行锁、cursor、parallel-unsafe function、嵌套并行和 worker
资源不足等限制。

### 并行扫描

常见 parallel-aware 扫描包括：

```text
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 万行几乎全参与月聚合，
顺序读并行处理可能正是正确路径。判断标准是选择性、缓存、物理布局、并发与
总体资源，而不是节点名字的好恶。

### 并行连接

并行连接可能让：

```text
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 个
并发报表可能降低系统总吞吐。

容量要同时看：

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

一个常见策略是：

```text
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`。

### 实验设置不是生产建议

本章把：

```sql
SET min_parallel_table_scan_size = 0;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
```

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

## 17.2.2 分区、物化视图、增量汇总与批处理 {#item-17-2-2}

### 四种手段解决四个问题

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

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

### 分区首先是数据管理边界

原生分区适合：

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

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

设计分区前回答：

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

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

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

本章：

```sql
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
);
```

冻结数据得到：

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

原表月报计划：

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

汇总月报计划：

```text
Seq Scan on daily_tenant_summary
actual rows=2880 loops=1
```

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

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

普通物化视图需要：

```sql
REFRESH MATERIALIZED VIEW shop_ch17.daily_tenant_summary;
```

或在满足条件时：

```sql
REFRESH MATERIALIZED VIEW CONCURRENTLY
  shop_ch17.daily_tenant_summary;
```

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

发布合同应固定：

```text
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](https://www.postgresql.org/docs/18/rules-materializedviews.html)
说明结果持久化、不可直接更新和 refresh 行为。

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

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

```text
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 完整重建。

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

```sql
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 请求合并为一个定时汇总，减少的是：

```text
duplicate scans
query startup
concurrency spikes
cache churn
client retries
```

批处理仍需要：

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

### 分区与汇总的组合

一个常见设计：

```text
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
```

好处是：

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

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

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

## 17.2.3 列式能力候选必须写入版本基线 {#item-17-2-3}

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

候选可能提供：

```text
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 和工具链；
- 单一数据副本与事务语义。

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

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

### 版本是功能的一部分

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

```text
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
```

不能写：

```text
uses columnar extension
```

而应写：

```text
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`](/labs/ch17/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 跑通”只覆盖第一行的一小部分。

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

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

```text
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 与分析负载在同机共存的代价 {#item-17-2-4}

### 共存争用表

| 资源 | 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 随机读尾延迟变差。

需要把：

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

放在同一时间轴。

### 会话级护栏

对分析角色可以评审：

```sql
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，仍需要连接池并发、作业调度、操作系统资源或实例隔离。

### 连接池与任务队列

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

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

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

```text
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](https://pigsty.io/docs/pgsql/config/cluster/)。
这是比直接分片更低一层的候选。

### 单独分析集群

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

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

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

```text
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 一同压测；
- 硬件纵向扩容与未来增长已建模；
- 正确性和新鲜度仍满足。

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

---

[上一节：先证明单机边界](../01/) · [返回本章目录](../) · [下一节：何时需要分布式](../03/) ·
[查看全书目录](/toc/) · [查看索引中心](/indexes/)