# PostgreSQL 输在哪里 > 如实梳理哪些地方其他系统胜过 PostgreSQL，能测量的就测量，并说明如何引入其他系统，同时保住权威数据源。 --- LLMS 索引： [llms.txt](/llms.txt) --- 此前的每一章都在展示，一个 PostgreSQL 能走多远。文档、队列、搜索、向量、时序、租户，还有把整表扫描变成十来页读取的汇总表。多数产品从来不需要别的数据库，我们依然坚持这个判断。 但“多数”不是“全部”，只讲一面的书没有告诉你真相。某些负载在另一类系统上能快一个数量级，原因就在 PostgreSQL 存储和搬运数据的方式里。你应当知道是哪些负载、原因何在、界限大致在哪，这样引入第二个数据库时，才有可以测量的理由。 我们用相同的两百万条事件，在 PostgreSQL 和两个列式存储系统上测量差距最大的一项：分析查询。然后梳理其余差距，并给出引入新系统的一条规则。 ## 行式存储为了用两列，必须读取所有列 {#a-row-store-reads-every-column-to-use-two} PostgreSQL 按行存储。每个堆页都保存完整的行，连同行首部一起（[每个页都有相同的布局](/04-storage/#every-page-has-the-same-layout)）。这非常适合“取出这个订单”和“更新这个用户”：一个页里，就有需要的所有列。 分析查询的需求恰好相反。“按类型和日期统计全年每天的事件数”会访问每一行，却只用两列，而行式存储仍要读取每一页。**列式存储**（column store）将每一列分别压缩后保存在各自的文件里，只读取查询指定的列。下面是实测出的差距。 ### 每个系统返回的答案逐行相同 {#every-system-returned-the-same-answers-to-the-row} 实验运行以下系统，每个都限制为 3 GB 内存：载入本书种子数据的 **PostgreSQL 18.6**；面向列的数据库服务器 **ClickHouse 26.8**（镜像标签 26.8.18.2）；嵌入式列式引擎 **DuckDB 1.5.6**；以及另一台装有 **pg_duckdb** 扩展的 **PostgreSQL 18.1**（pg_duckdb 镜像自带的版本）。它们都是开源软件，由同一个 `docker compose` 文件启动，各自代表一类系统。 为了公平对待 PostgreSQL，实验还创建了 `events_flat`，将 payload 中的字段展开为具有具体类型的列。接着运行三条仪表盘查询：每天各类型的事件数、每个地域的 `duration_ms` 的 p95，以及 20,000 个项目中最繁忙的十个。三个系统的答案逐行一致。若比较的是不同答案，基准测试就什么也没测出来。 > [!DETAILS]- 深入阅读：三条查询，以及三个系统答案相同的证明 > > ```sql > -- 1. events per kind per day, over the year > select kind, date_trunc('day', created_at), count(*) > from events group by 1, 2; > > -- 2. p95 of duration_ms per region > select payload->>'region', > percentile_cont(0.95) within group (order by (payload->>'duration_ms')::int) > from events group by 1; > > -- 3. the ten busiest projects (20,000 groups) > select project_id, count(*), avg((payload->>'duration_ms')::int) > from events group by 1 order by 2 desc, 1 limit 10; > ``` > > 每个系统都用自己的方言（ClickHouse 用 `toDate` 和 `quantileExactInclusive`，DuckDB 用 `quantile_cont`）：1,826 个类型与日期的组合，计数相同，p95 相同（各地域均为 4,745 至 4,749 ms），最繁忙的十个项目相同，平均值也相同。ClickHouse 中的表使用 `MergeTree`，按 `(project_id, created_at)` 排序，短文本列按文档建议采用 `LowCardinality(String)`；DuckDB 则与 PostgreSQL 一样，使用普通类型和主键。 > > > [!EXAMPLE]- 动手验证 · lab `40-limits.sh` > > > > ```bash > > prove "kind by day: 1,826 groups, identical in all three" \ > > test "$(wc -l <<<"$pg_q1" | tr -d ' ')" = 1826 -a "$pg_q1" = "$ch_q1" -a "$pg_q1" = "$duck_q1" > > prove "p95 of duration_ms by region is identical in all three" \ > > test "$pg_q2" = "$ch_q2" -a "$pg_q2" = "$duck_q2" > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/40-limits.sh) ### 列式存储只占十分之一的字节数 {#column-stores-keep-a-tenth-of-the-bytes} 同样的两百万行，磁盘占用如下： | 系统 | 大小 | | ----------------------------------------- | -------- | | PostgreSQL，`events`（jsonb），堆 + 索引 | 317.8 MB | | PostgreSQL，`events_flat`，堆 + 索引 | 220.9 MB | | ClickHouse，压缩后的数据片段 | 32.1 MB | | DuckDB，数据库文件 | 33.3 MB | **Table 41-1.** 列式存储只占十分之一的字节数 列式存储占用的字节数，不到字段拆列后的 PostgreSQL 表的五分之一（大约七分之一），约为本书原有存储方式的十分之一。PostgreSQL 的两个大小都包含 `id` 上的主键索引；DuckDB 的文件也包含自己的主键，而 ClickHouse 没有这样的索引需要存储。 差距来自两方面。字段拆列后的表，其堆有 22,784 页、178 MB，其中一半都是管理开销：按 ClickHouse 的统计，值本身约 92 MB，而且其中的短文本列已经做了字典编码；每行还要加上 24 字节首部、一个行指针和填充字节（[一行的成本高于它的列](/04-storage/#a-row-costs-more-than-its-columns)），这是 [MVCC](/43-glossary/#mvcc) 的代价。另一方面，相似的值放在一起，能压缩到几乎不占空间。 > [!DETAILS]- 深入阅读：逐列看 ClickHouse 的字节花在哪里 > > ```text > created_at 13.4 MB compressed, 15.3 MB raw > id 9.1 MB compressed, 15.3 MB raw > duration_ms 5.7 MB compressed, 7.6 MB raw > region 1.2 MB compressed, 1.9 MB raw > kind 1.1 MB compressed, 1.9 MB raw > status 0.8 MB compressed, 1.9 MB raw > user_id 0.2 MB compressed, 17.2 MB raw > account_id 0.2 MB compressed, 15.3 MB raw > project_id 0.2 MB compressed, 15.3 MB raw > ``` > > `project_id`：15.3 MB 的值（每行 8 字节）压缩成了 0.2 MB。表按项目排序，因此列文件中会有长段连续相同的值；`account_id` 和 `user_id` 随项目排列，也能很好地压缩。看起来随机的 `duration_ms` 几乎压不小。列式存储的压缩并非魔法：相似的值挨在一起，自然会这样，而排序键决定哪些值能挨在一起。把 payload 拆成类型列，让 PostgreSQL 的体积缩小了三分之一，因为 `jsonb` 会在每一行重复存储每个键名，但即便如此仍然相差很远。 > > > [!EXAMPLE]- 动手验证 · lab `40-limits.sh` > > > > ```bash > > prove "ClickHouse stores the events in under a fifth of the bytes of Postgres's typed-column table" \ > > holds "$ch_bytes * 5 < $pg_flat" > > prove "DuckDB does too" holds "$duck_bytes * 5 < $pg_flat" > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/40-limits.sh) ### 每条查询，PostgreSQL 都读取了所有页 {#postgres-read-every-page-for-every-query} 每条查询，PostgreSQL 都读取 `events` 的全部 35,173 页，共 275 MB，尽管每条查询只指定了两列。ClickHouse 同样读取了全部 2,000,000 行，但只读指定的列，而且是压缩后的数据： | 查询 | PostgreSQL 读取量 | ClickHouse 读取量（压缩后） | | -------------- | ----------------- | -------------------------- | | 每天各类型事件数 | 275 MB | 14.6 MB | | 各地域 p95 | 275 MB | 6.9 MB | | 最繁忙的项目 | 275 MB | 6.0 MB | **Table 41-2.** 每条查询，PostgreSQL 都读取了所有页 p95 查询指定了 `region` 和 `duration_ms`，ClickHouse 恰好读取了 10,000,000 字节的列数据，每行 5 字节（从磁盘取出时是压缩后的 6.9 MB）。为了取得同样的 5 字节，PostgreSQL 每行读取了 144 字节。 一个数字就说明了整个机制。行式存储每次扫描都要为整行的宽度付费；列式存储只为问题涉及的宽度付费。 > [!DETAILS]- 深入阅读：核对读取的页数 > > 并行工作进程会对少量页重复计数，因此 `explain` 报出的页数最高为 35,257。字段拆列后的 `events_flat` 只是因为体积更小而有所帮助：22,784 页，每次仍然全部读取。 > > ClickHouse 的数据来自 `system.query_log`：每条查询的 `read_rows` 和 `read_bytes`（未压缩的列数据），再加上表示磁盘读取量的 `ReadCompressedBytes` 性能分析事件。未压缩时，三条查询分别读取 17.2、9.5 和 22.9 MB；最繁忙项目查询读取的列数据最多（两个 8 字节列），却压缩得最好，因为 `project_id` 是排序键。压缩后，各查询读取量约为 PostgreSQL 的 275 MB 的 5%，甚至更少。 > > > [!EXAMPLE]- 动手验证 · lab `40-limits.sh` > > > > ```bash > > prove "Postgres reads every page of events, 35,173 pages (275 MB), for each of the three queries" \ > > holds "$events_relpages == 35173 && $pg_p1 >= 35173 && $pg_p2 >= 35173 && $pg_p3 >= 35173 && $pg_p1 < 35173 * 1.01 && $pg_p2 < 35173 * 1.01 && $pg_p3 < 35173 * 1.01" > > prove "ClickHouse also reads all 2,000,000 rows for each query, but only the columns it needs: 5 bytes a row for region and duration_ms" \ > > test "$ch_rows1" = 2000000 -a "$ch_rows2" = 2000000 -a "$ch_rows3" = 2000000 -a "$ch_raw2" = 10000000 > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/40-limits.sh) ### 列式存储快约十倍，单核上仍然更快 {#column-stores-were-about-ten-times-faster-and-still-faster-on-one-core} 字节数是证据，时间则是用户的感受。下面是实验一次运行中，缓存预热后测三次取最快值的结果，单位为毫秒：桌面电脑上的 Docker，容器可见第 13 代 Intel i9 的 8 个核心、16 GB 内存，与其他数据库共享。每个系统都在服务端自行计时，丢弃结果：PostgreSQL 在 PL/pgSQL 函数中用 `clock_timestamp()` 包住每条查询；ClickHouse 从查询日志取时间；DuckDB 用命令行的 `.timer`。 PostgreSQL 的默认配置有意保持保守，因此实验用了三种方式。**默认**：默认设置，`work_mem` 为 4 MB，导致 p95 排序溢写磁盘，最多使用两个并行工作进程。**字段拆列**：相同设置，表换为 `events_flat`。**调优**：使用 `events_flat`，设置 `work_mem = '256MB'`（不再溢写），并允许最多八个并行工作进程。这是我们能给它的最公平的行式存储基线。 | 毫秒，默认设置 | 每天各类型事件数 | 各地域 p95 | 最繁忙的项目 | | -------------------- | ---------------- | ---------- | ------------ | | PostgreSQL，默认 | 324 | 816 | 158 | | PostgreSQL，字段拆列 | 312 | 675 | 108 | | PostgreSQL，调优 | 354 | 474 | 82 | | ClickHouse | 43 | 16 | 15 | | DuckDB | 57 | 6 | 13 | **Table 41-3.** 列式存储快约十倍，单核上仍然更快 调优后的 PostgreSQL 执行三条查询合计耗时 910 ms；ClickHouse 用了 74 ms，DuckDB 用了 76 ms，快约十二倍。第一个查询中，更多工作进程没能帮上 PostgreSQL：规划器只用了其中几个，跨进程分配行的开销与省下的时间大致相当。 列式存储默认使用所有核心，因此实验又在各用一个核心的条件下重跑了一遍（PostgreSQL 不使用并行工作进程）： | 毫秒，各用单核 | 每天各类型事件数 | 各地域 p95 | 最繁忙的项目 | | -------------------- | ---------------- | ---------- | ------------ | | PostgreSQL，默认 | 313 | 1,087 | 363 | | PostgreSQL，字段拆列 | 315 | 675 | 199 | | PostgreSQL，调优 | 334 | 474 | 191 | | ClickHouse | 81 | 26 | 24 | | DuckDB | 278 | 22 | 15 | **Table 41-4.** 列式存储快约十倍，单核上仍然更快 单核上，调优后的 PostgreSQL 合计耗时 999 ms，ClickHouse 为 131 ms（快约 7.6 倍），DuckDB 为 315 ms（快约 3.2 倍）。第一个查询的差距几乎消失：按天把时间戳分桶时，单核 DuckDB 仅比 PostgreSQL 快一点。其余两个查询的差距仍然很大。精确 p95 是最极端的例子：即使排序完全在内存中，PostgreSQL 仍要逐个元组对两百万个值排序，而列式存储遍历的是紧凑、已经解码的整数列。 > [!NOTE] 一次基准测试不能定论 > > 一个小数据集、三条查询、一台机器、一次运行，只调了 PostgreSQL 最明显的几个设置，列式存储则保持默认配置。它展示的是一种机制和一个数量级，既不能判定哪个列式存储更快，也不能代表你的数据扩大一百倍后的情况。[如实测量](/29-benchmarking/#basics-chapter-29)一章给出了自己做测试的规则。 > [!DETAILS]- 深入阅读：列为何胜出——布局、压缩与向量化执行 > > 三种机制，而且效果会叠加。 > > **布局。**列式存储的开销随查询访问的列而增长，而不是随表的宽度增长。PostgreSQL 的开销随页数增长，每个页又包含完整的行。 > > **压缩。**一列只保存一种类型的值，常常有序或重复，因此低开销的编解码算法就能有很好的效果（根据 [CREATE TABLE 文档](https://clickhouse.com/docs/sql-reference/statements/create/table)，自建 ClickHouse 服务器默认使用 LZ4；根据[存储文档](https://duckdb.org/docs/current/internals/storage.html)，DuckDB 会在字典编码、游程编码和位打包编码之间选择）。PostgreSQL 只通过 TOAST 逐个压缩大值（[大值会先压缩，再移出行外](/04-storage/#large-values-are-compressed-then-moved-out-of-the-row)）。100 字节的行根本不会被压缩。 > > **向量化执行**（vectorized execution）。两个列式存储都一次处理一列中的一批值，用紧凑循环让 CPU 流水线发挥作用。PostgreSQL 的执行器则逐行从每个计划节点拉取数据，解构每个元组以找出其中的列。OLTP 查询只访问几行时，这样很好；两百万行都这么做，就全是开销。 > > > [!EXAMPLE]- 动手验证 · lab `40-limits.sh` > > > > ```bash > > tuned_sum=$((tuned_t1 + tuned_t2 + tuned_t3)); tuned_one=$((tuned_s1 + tuned_s2 + tuned_s3)) > > prove "with stock work_mem, the p95 sort spills to disk; with 256 MB it doesn't" \ > > test "$spill" -gt 0 -a "$spill_tuned" = 0 > > prove "against tuned, typed Postgres, both column stores answer the three queries at least 3 times faster" \ > > holds "$ch_sum * 3 < $tuned_sum && $duck_sum * 3 < $tuned_sum" > > prove "on one core each, against the same tuned, typed table, they are still at least twice as fast" \ > > holds "$ch_one * 2 < $tuned_one && $duck_one * 2 < $tuned_one" > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/40-limits.sh) ### 列式存储牺牲的，恰是应用数据库的职责 {#column-stores-give-up-what-an-application-database-is-for} 为了换取速度，列式存储牺牲的恰是应用数据库要做好的事。实验检查了四项： - **点查。**PostgreSQL 按 `id` 找一个事件，读取 4 页。ClickHouse 只为排序键 `(project_id, created_at)` 建索引，因此按 `id` 查找会读取全部 2,000,000 行；即便按排序键查找，也会读取整个包含 8,192 行的粒度单元（granule）。 - **约束。**ClickHouse 的主键只规定排序，不作唯一性保证：它存下了第二个 `id` 为 1 的事件。（DuckDB 会强制检查键约束，因此拒绝了它。） - **更新与事务。**在 ClickHouse 中更新一个事件，重写了一个含 2,000,000 行的数据片段（part）；PostgreSQL 只写了 264 字节 WAL。`begin transaction` 返回 “Transactions are not supported”。 - **并发写入者。**DuckDB 在你的进程内部运行，只有一个进程可以写入：第二个进程得到 “Could not set lock on file”。 列式存储最适合的是一张主要追加写入的宽表。你的应用数据模式恰好相反。 > [!DETAILS]- 深入阅读：各系统文档中的四项取舍 > > ClickHouse 在[文档](https://clickhouse.com/docs/guides/best-practices/sparse-primary-indexes)中描述了稀疏索引，并明确说明：主键允许多行具有相同的值。它在插入时检查 `CHECK` 约束；表的文档中没有外键之类的机制。其文档将变更操作（mutation）称为 “a heavy operation not designed for frequent use”，默认异步执行。也有更轻量的选择：轻量 `DELETE` 先隐藏行，直到合并时再移除；轻量 `UPDATE` 写入小型补丁片段，目前标为 beta，需要开启两项表设置。它们有帮助，但不会把 ClickHouse 变成 OLTP 存储。事务需要 ClickHouse Keeper（[事务指南](https://clickhouse.com/docs/guides/developer/transactional)）。连接也能做，有多种算法可选，但[连接指南](https://clickhouse.com/docs/guides/joining-tables)建议使用反规范化、字典和物化视图，并提醒：哈希连接需要把右表放进内存。 > > DuckDB 的[并发文档](https://duckdb.org/docs/current/connect/concurrency.html)描述了单写入者模型。它对多写入者的回答是 Quack：一种客户端—服务器协议，2026 年以 beta 扩展发布，计划随 DuckDB 2.0 走向成熟（[公告](https://duckdb.org/2026/05/12/quack-remote-protocol.html)）。就目前而言，嵌入式分析引擎非常适合报表、交互式笔记本或流水线中的一个步骤，却不适合让一百台应用服务器同时向它写入。 > > > [!EXAMPLE]- 动手验证 · lab `40-limits.sh` > > > > ```bash > > prove "Postgres finds one event by id in 4 pages through its primary key index" test "$pg_point" = 4 > > prove "ClickHouse has no index on id (its sort key is project_id, created_at), so it reads all 2,000,000 rows" \ > > test "$ch_point" = 2000000 > > prove "even on its sort key, ClickHouse reads a whole granule: 8,192 rows to find a handful" \ > > test "$ch_key" = 8192 > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/40-limits.sh) > > > [!EXAMPLE]- 动手验证 · lab `40-limits.sh` > > > > ```bash > > ch -q "insert into events values (1, 1, 1, 1, 'deploy', 'ok', 1, 'iad', '2026-10-01 12:00:00')" > > prove "ClickHouse accepts it: a MergeTree primary key is a sort order, not a constraint" \ > > test "$(ch -q "select count() from events where id = 1")" = 2 > > prove "Postgres updates one row with under 1 KB of WAL" holds "$pg_wal < 1024" > > prove "a ClickHouse ALTER TABLE ... UPDATE of one row rewrites a part of 2,000,000 rows" test "$ch_mut" = 2000000 > > out=$(ch -q "begin transaction" 2>&1 || true) > > prove "ClickHouse has no multi-statement transactions at its default settings" has "Transactions are not supported" "$out" > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/40-limits.sh) > > > [!EXAMPLE]- 动手验证 · lab `40-limits.sh` > > > > ```bash > > "${COMPOSE[@]}" run -d -i --name "$HOLDER" duckdb /duckdb /xfer/events.duckdb >/dev/null 2>&1 > > sleep 3 > > out=$("${COMPOSE[@]}" run --rm -T duckdb /duckdb /xfer/events.duckdb -c "select 1" 2>&1 || true) > > prove "DuckDB is one process: while one has the file open for writing, a second can't open it" \ > > has "Could not set lock on file" "$out" > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/40-limits.sh) ### 汇总表和列式扩展能大幅缩小差距 {#rollups-and-a-column-extension-close-much-of-the-gap} 添一台服务器之前，先在 PostgreSQL 内试两件事。 **汇总表。**如果问题事先已知，就提前算好。按类型和日期做每日汇总，只有 1,826 行、12 页，却能给出与扫描 35,173 页相同的答案（[预先算好要展示的内容](/37-scale/#precompute-what-you-show)）。没有哪个列式存储能胜过只读 12 页。问题变成临时分析时，汇总表就帮不上忙了。 **以扩展形式提供的列式引擎**（[目前的选择](/16-extensions/#run-an-analytics-engine-on-a-replica-never-the-primary)）。pg_duckdb 在普通 PostgreSQL 表上运行 DuckDB 的向量化执行器。p95 查询快了约五倍，答案相同；另外两个查询几乎没变（最繁忙项目查询还稍慢了一点）。它仍然读取了字段拆列后的表副本的全部 22,752 个堆页（在独立服务器上，因此与前面的 22,784 页略有不同），所以使用自己列式文件的 DuckDB 还能再快约六倍（三条查询合计 76 ms，对比 474 ms）。向量化执行解决 CPU 这一半，只有列式存储才能解决 I/O 那一半。 > [!DETAILS]- 深入阅读：PostgreSQL 内部的列式存储及其限制 > > 一台 pg_duckdb 服务器上三条查询的耗时，单位为毫秒： > > | 毫秒，同一服务器 | 每天各类型事件数 | 各地域 p95 | 最繁忙的项目 | > | ---------------- | ---------------- | ---------- | ------------ | > | PostgreSQL 执行器 | 292 | 623 | 92 | > | DuckDB 执行器 | 252 | 113 | 109 | > | DuckDB，自有文件 | 57 | 6 | 13 | > > > > **Table 41-5.** 汇总表和列式扩展能大幅缩小差距 > > 因此，这种执行器最适合搭配列式文件：对象存储中的 Parquet 或 Iceberg，或者 pg_mooncake 那样的镜像。另一种方式是列式表访问方法（Citus 和 Hydra 都有），直接按列存储表。Citus 的实现只允许追加写入，[README](https://github.com/citusdata/citus/tree/main/src/backend/columnar)也如此说明：在 PostgreSQL 18.4 上运行 Citus 14.2 时，对列式表执行 `update` 失败，报错 “UPDATE and CTID scans not supported for ColumnarScan”。TimescaleDB 的列式存储会把超表的旧数据块转换成压缩列；[前文实测](/16-extensions/#the-columnstore-324-mb-to-30-mb)时，它把本书的事件从 324 MB 缩到了 30 MB，与这里的列式存储处于同一数量级。 > > > [!EXAMPLE]- 动手验证 · lab `40-limits.sh` > > > > ```bash > > roll_pages=$(pg -c "select lab.buffers('select kind, day, n from events_daily')") > > prove "a daily rollup answers kind by day from under 20 pages instead of 35,173" holds "$roll_pages < 20" > > prove "with duckdb.force_execution on, pg_duckdb returns the same p95s" test "$pgd_q2" = "$pg_q2" > > prove "on the same server and table, pg_duckdb computes the p95s at least twice as fast as Postgres's executor" \ > > holds "$pgd_on2 * 2 < $pgd_off2" > > prove "but it still reads every 8 KB page of the row-store heap" holds "$pgd_read >= $pgd_relpages && $pgd_relpages > 22000" > > prove "so DuckDB on its own columnar file answers the three queries at least 3 times faster than pg_duckdb over a heap" \ > > holds "$duck_sum * 3 < $pgd_on1 + $pgd_on2 + $pgd_on3" > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/40-limits.sh) ### 四个条件同时成立时，再把分析移出去 {#move-analytics-out-when-four-things-are-true-together} 如果是两亿多条事件，相同的扫描就要读取约一百倍的页。这是算术推算，不是实测，但正是这种算术，让一次仪表盘查询变成了喝杯咖啡的等待。 四个条件同时成立时，再把分析移出去：查询是**临时提出的**，会**扫描大表的大部分数据**，数据**以追加写入为主**，而在主库或副本上运行它们会造成**可以测量的损害**。在此之前，带汇总表的副本能长期支撑报表。迁出之后，列式存储也只是从 PostgreSQL 接收数据的副本，绝不能成为行诞生的地方。 ## 其他差距，只有规模上来才显现 {#the-other-gaps-show-up-only-at-scale} 分析查询是我们能在笔记本电脑上测出的差距。其他差距要到规模上来才显现，因此对每一项，我们都会说明机制、表现更好的系统类别和分界线；依据是资料，而非基准测试。提到的名字只是某类系统的例子，不是推荐。 ### 搜索：PostgreSQL 排名不使用语料库统计信息 {#search-postgres-ranks-without-corpus-statistics} PostgreSQL 的排名函数 “do not use any global information”（[Ranking Search Results](https://www.postgresql.org/docs/18/textsearch-controls.html#TEXTSEARCH-RANKING)），并且会读取每个匹配项。[Elasticsearch](https://www.elastic.co/docs/reference/elasticsearch/index-settings/similarity)、OpenSearch 这类引擎用语料库统计信息评分（[BM25](/43-glossary/#bm25)），增加同义词和分面功能，并对索引分片。分界线是：相关性已经成为有人专门负责的产品功能（参见[何时 PostgreSQL 已经够用](/23-search/#for-most-applications-postgres-search-is-enough)；先试 [BM25 扩展](/23-search/#search-extensions-put-bm25-in-the-index)）。 ### 缓存：数据库不是缓存 {#caching-a-database-is-not-a-cache} 让 PostgreSQL 在自己前面充当缓存，会被自身机制拖累：每个连接都是一个进程，每次写入都是一个新行版本（[更新就是插入](/06-mvcc/#basics-chapter-6)），数据不会自行过期，而且还与它本该保护的数据库共用一台机器。[Valkey](https://valkey.io/topics/introduction/)（采用 BSD 许可证的 Redis 分支）和 Redis 这类存储把数据放在内存中，支持生存时间，并允许[调节](https://valkey.io/topics/persistence/)持久性。分界线是：请求速率高到连接和单次写入开销已在测量中显现；在此之前，用[不记日志表](/15-models/#an-unlogged-table-is-emptied-after-a-crash)就好。 ### 流：表不是日志 {#streams-a-table-is-not-a-log} PostgreSQL 中的[作业队列](/15-models/#skip-locked-gives-each-job-to-exactly-one-worker)很好用。但供许多消费者使用、保留多日的事件日志就不合适了：消费会产生死元组，每个消费者的位置都要靠你自己实现的游标记录。[Apache Kafka](https://kafka.apache.org/intro) 这类日志系统在消费之后仍保留事件（根据[主题配置](https://kafka.apache.org/41/configuration/topic-configs/)，默认七天），各消费者可以按自己的节奏读取。通过 [Debezium](https://debezium.io/documentation/reference/stable/connectors/postgresql.html) 这类 CDC 工具，从逻辑解码向它传送数据，不要再额外写一次。分界线是：需要扇出，以及以天计的重放。 ### 时序：写入路径就是瓶颈 {#time-series-the-write-path-is-the-limit} BRIN 和分区很适合处理按时间排序的数据（[时序适合 BRIN 和按月分区](/15-models/#time-series-fits-brin-and-monthly-partitions)）。摄入速率极高时，写入路径成了瓶颈：每行 24 字节的首部，每次单行插入约 600 字节 WAL（[所有写入都汇入同一条 WAL 流](/37-scale/#every-write-funnels-into-one-wal-stream)），每个副本只有一个重放进程（[重放只有一个进程](/38-replication/#replay-is-one-process)）。时序数据库把数据批量写入压缩的列式块；TimescaleDB 在 PostgreSQL 内部完成这件事（[有主张的分区](/16-extensions/#timescaledb-is-partitioning-with-opinions)）。分界线是：即使做了[批处理](/27-loading/#basics-chapter-27)，WAL 或重放仍跟不上摄入速度。 ### 全球写入：每次提交都要付出往返时间 {#global-writes-every-commit-pays-the-round-trip} 悉尼的用户向弗吉尼亚写入，每次提交都要承受一次往返延迟；地域级故障切换还可能丢失尚未传送的数据。分布式 SQL 数据库（[Spanner](https://docs.cloud.google.com/spanner/docs/true-time-external-consistency)、[CockroachDB](https://docs.cockroachlabs.com/docs/stable/architecture/replication-layer)、[YugabyteDB](https://docs.yugabyte.com/stable/architecture/query-layer/)；后两者[支持 PostgreSQL 协议，但有差异](/40-sharding/#distributed-sql-speaks-postgres-but-isnt-postgres)）通过共识提交，能承受一个地域的失效。它们无法打破物理规律，只是选择把往返开销放在哪里。分界线是：确实需要多大陆低延迟写入，或地域故障时零数据丢失。 ### 向量：可建索引的列最多 2,000 维 {#vectors-an-indexed-column-tops-out-at-2000-dimensions} pgvector 让相似度搜索就在数据旁边完成（[向量搜索会找到临近的点，不在乎文字是什么](/23-search/#a-vector-search-finds-near-points-whatever-the-words)），但也有限制：可建索引的列最多 2,000 维（使用 `halfvec` 时为 4,000 维，见 [pgvector README](https://github.com/pgvector/pgvector)），而[过滤条件与近似索引会相互掣肘](/23-search/#filters-and-approximate-indexes-dont-mix)。专用向量数据库会分布式存储并量化索引。分界线（[何时该用它](/23-search/#a-vector-database-needs-a-reason-you-can-name)）是：数亿个向量，或者索引比最大机器的内存还大。 ### 图：每一跳仍是一次连接 {#graphs-every-hop-is-still-a-join} 递归 CTE 和闭包表可以处理树和少量跳转（[Postgres 用三种方式存储图](/15-models/#postgres-stores-a-graph-three-ways)），但每一跳都是一次连接，指数增长的搜索前沿会让每次连接越来越大。图数据库直接沿存储好的关系走；[Apache AGE](/16-extensions/#apache-age-is-a-nicer-language-for-the-same-joins)把 openCypher 带进 PostgreSQL，但每一跳仍然是连接。分界线是：在大规模稠密图上运行图算法，或在请求路径上做多跳匹配。组织架构图远没到这个程度。 ### 二进制大对象：字节应放进对象存储 {#blobs-bytes-belong-in-object-storage} 存进去的每个字节都要经过 WAL，进入每个副本、备份和恢复过程。一张经过 [TOAST 存储](/43-glossary/#toast)的 20 MB 图片，即使只为了生成缩略图，也得完整读取。对象存储放在 CDN 后面，可以容纳数十 TB 的对象（[Amazon S3 为 50 TB](https://docs.aws.amazon.com/AmazonS3/latest/userguide/upload-objects.html)）。分界线很低：超过几 KB、又不需要当作数据查询的内容，从第一天起就放进桶里，把元数据留在 PostgreSQL 的一行中。 ## 用到之前，先认清信号 {#know-the-signal-before-you-need-it} | 负载 | PostgreSQL 靠什么能走很远 | 引入另一系统的信号 | 系统类别 | | ------------------ | ----------------------------------------- | ------------------------------------------------------- | ---------------------- | | 分析 | 汇总表、分区、副本、pg_duckdb | 扫描大表大部分数据的临时分析损害 OLTP | 列式存储 | | 搜索 | `tsvector`、GIN、BM25 扩展 | 相关性已成为产品功能；需要对海量结果做分面 | 搜索引擎 | | 缓存 | 主键、不记日志表 | 请求速率高到连接和写入开销显现 | 内存键值存储 | | 事件流 | `skip locked` 队列、事务发件箱 | 扇出、保留多日、重放 | 分布式日志 | | 时序 | BRIN、分区、TimescaleDB | 批处理后，摄入速度仍超过 WAL 与重放的承受能力 | 时序数据库或列式存储 | | 全球写入 | 一个主库、只读副本 | 多个大陆上的低延迟写入、地域失效 | 分布式 SQL | | 向量 | pgvector、`halfvec`、量化 | 数亿个向量，索引超出内存 | 向量数据库 | | 图 | 递归 CTE、`ltree`、闭包表 | 大图上的多跳遍历和图算法 | 图数据库 | | 二进制大对象 | 元数据行 | 几乎总是：超过几 KB 的文件 | 对象存储 | **Table 41-6.** 用到之前，先认清信号 ## 有实测理由，再引入第二个系统 {#add-a-second-system-for-a-measured-reason} 这张表的每一行都有相同的结构：一种机制、一个信号，以及一个可以测量的数字。这就是规则。 **能说清 PostgreSQL 缺少什么特性，并拿出测量结果证明需要它时，再增加一个系统。**“p95 仪表盘查询要扫描 300 GB，花四分钟，而且每周的问题都在变”，这是理由。“我们迟早会需要分析”，不是理由。也要如实核算第二个系统的成本：又多一个需要备份、升级、保障安全，并在出问题时叫人处理的东西。 **让 PostgreSQL 始终作为权威数据源。**一行数据在 PostgreSQL 的事务中诞生，接受约束检查。索引、缓存、列式存储和流都由它派生，也都能从它重建。 **用变更数据捕获（change data capture，CDC）向外传送数据，不做双写。**先写 PostgreSQL，再写第二个系统，两次写入之间可能失败，而且无人察觉。用[逻辑解码](/38-replication/#logical-replication-is-for-upgrades-moves-and-feeds)读取已提交的变更流（并[监控复制槽](/38-replication/#replication-slots-are-a-promise-to-keep-wal)），或者在同一个事务中写入一行[事务发件箱](/09-acid/#put-the-message-in-the-same-transaction-as-the-order)记录。让消费者具有幂等性：某些变更会被它们看到两次。 > [!TIP] 向外迁移，是可以规划的 > > 从 PostgreSQL 起步不会把你锁死。借助逻辑解码和事务发件箱，可以在 PostgreSQL 旁搭起列式存储或搜索引擎，回填历史数据，持续传送变更，比对结果，再逐条查询地把读取迁过去。反过来，过早引入第二个数据库之后，要撤回这个决定就困难得多：两边数据已经偏离，写入代码也散落各处。 ## 凭证据离开 {#leave-on-evidence} 这条规则，就是最后再重申一次本书的两个习惯。 **数一数页面。**本书开头的查询访问了 35,173 页，却几乎没返回什么。到这里，同样的 35,173 页，只为回答关于两列的问题而读取，就成了把分析迁出去的理由；若用汇总表的 12 页回答同一个问题，就可以留在原地。 **先用数据证明，该换更大的机器了。**第二个数据库是最大的一台“机器”，还自带一套值班告警，也必须通过同一道关。 这两条合起来，就是本书开篇的原则：从一个数据库开始，凭证据离开。多数时候，证据会让你留下。逐页读懂一个 PostgreSQL，它能走得比名声所及更远。等证据告诉你该走时，你也会知道为什么，理由就在数字里。 ## 本章要点 {#the-short-version} - 行式存储读取每行的所有列：我们的三条查询，每条都读取 35,173 页，无论只指定了多少列。 - ClickHouse 和 DuckDB 存储相同事件所用的字节数不到五分之一，每条查询的读取量不到十分之一，响应速度比调优后的 PostgreSQL 快约十二倍；单核上分别快约七倍和三倍（我们的机器，一次运行）。 - 列式存储牺牲了应用所需的能力：点查、强制键约束、低成本更新、事务、并发写入者。 - 先试折中办法：汇总表（12 页，而非 35,173 页）、专供报表的副本、pg_duckdb。 - 搜索、缓存、流、极高摄入速率、全球写入、海量向量、深层图和二进制大对象，各自都有相应的机制和信号；用到之前先了解。 - 只有能说清需要的特性，并能测出相应数字，才增加系统，而且要让 PostgreSQL 继续作为权威数据源。 - 数一数页面，先用数据证明该换更大的机器，从一个数据库开始：只有证据充分，才离开。 ## 延伸阅读 {#further-reading} - [Parallel query](https://www.postgresql.org/docs/18/parallel-query.html)，PostgreSQL 文档。PostgreSQL 何时使用多个进程扫描，以及限制它的设置；这些因素决定了行式存储在本章这类基准测试中的表现。 - [Logical Decoding](https://www.postgresql.org/docs/18/logicaldecoding.html)，PostgreSQL 文档。所有 CDC 工具赖以构建的变更流。 - [A Practical Introduction to Primary Indexes](https://clickhouse.com/docs/guides/best-practices/sparse-primary-indexes)，ClickHouse 文档。粒度单元、稀疏索引，以及为什么排序键就是整个设计的核心。 - [Concurrency](https://duckdb.org/docs/current/connect/concurrency.html)，DuckDB 文档。嵌入式引擎在进程之间能够、不能够共享什么。 - [pg_duckdb](https://github.com/duckdb/pg_duckdb)，DuckDB 项目。在 PostgreSQL 内部运行 DuckDB 执行器，以及通过 SQL 读取数据湖格式。 - [Debezium connector for PostgreSQL](https://debezium.io/documentation/reference/stable/connectors/postgresql.html)，Debezium 文档。从 PostgreSQL 捕获变更数据的完整参考：复制槽、快照及故障模式。 - [What Goes Around Comes Around... And Around...](https://db.cs.cmu.edu/papers/2024/whatgoesaround-sigmodrec2024.pdf)，Michael Stonebraker 和 Andrew Pavlo，SIGMOD Record 2024。为何专用系统不断出现，关系模型又不断吸收它们。