41 PostgreSQL 输在哪里

此前的每一章都在展示，一个 PostgreSQL 能走多远。文档、队列、搜索、向量、时序、租户，还有把整表扫描变成十来页读取的汇总表。多数产品从来不需要别的数据库，我们依然坚持这个判断。

但“多数”不是“全部”，只讲一面的书没有告诉你真相。某些负载在另一类系统上能快一个数量级，原因就在 PostgreSQL 存储和搬运数据的方式里。你应当知道是哪些负载、原因何在、界限大致在哪，这样引入第二个数据库时，才有可以测量的理由。

我们用相同的两百万条事件，在 PostgreSQL 和两个列式存储系统上测量差距最大的一项：分析查询。然后梳理其余差距，并给出引入新系统的一条规则。

行式存储为了用两列，必须读取所有列

PostgreSQL 按行存储。每个堆页都保存完整的行，连同行首部一起（每个页都有相同的布局）。这非常适合“取出这个订单”和“更新这个用户”：一个页里，就有需要的所有列。

分析查询的需求恰好相反。“按类型和日期统计全年每天的事件数”会访问每一行，却只用两列，而行式存储仍要读取每一页。列式存储（column store）将每一列分别压缩后保存在各自的文件里，只读取查询指定的列。下面是实测出的差距。

每个系统返回的答案逐行相同

实验运行以下系统，每个都限制为 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 个项目中最繁忙的十个。三个系统的答案逐行一致。若比较的是不同答案，基准测试就什么也没测出来。

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

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"

列式存储只占十分之一的字节数

同样的两百万行，磁盘占用如下：

列式存储占用的字节数，不到字段拆列后的 PostgreSQL 表的五分之一（大约七分之一），约为本书原有存储方式的十分之一。PostgreSQL 的两个大小都包含 id 上的主键索引；DuckDB 的文件也包含自己的主键，而 ClickHouse 没有这样的索引需要存储。

差距来自两方面。字段拆列后的表，其堆有 22,784 页、178 MB，其中一半都是管理开销：按 ClickHouse 的统计，值本身约 92 MB，而且其中的短文本列已经做了字典编码；每行还要加上 24 字节首部、一个行指针和填充字节（一行的成本高于它的列），这是 MVCC 的代价。另一方面，相似的值放在一起，能压缩到几乎不占空间。

    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

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"

每条查询，PostgreSQL 都读取了所有页

每条查询，PostgreSQL 都读取 events 的全部 35,173 页，共 275 MB，尽管每条查询只指定了两列。ClickHouse 同样读取了全部 2,000,000 行，但只读指定的列，而且是压缩后的数据：

p95 查询指定了 region 和 duration_ms，ClickHouse 恰好读取了 10,000,000 字节的列数据，每行 5 字节（从磁盘取出时是压缩后的 6.9 MB）。为了取得同样的 5 字节，PostgreSQL 每行读取了 144 字节。

一个数字就说明了整个机制。行式存储每次扫描都要为整行的宽度付费；列式存储只为问题涉及的宽度付费。

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

列式存储快约十倍，单核上仍然更快

字节数是证据，时间则是用户的感受。下面是实验一次运行中，缓存预热后测三次取最快值的结果，单位为毫秒：桌面电脑上的 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'（不再溢写），并允许最多八个并行工作进程。这是我们能给它的最公平的行式存储基线。

调优后的 PostgreSQL 执行三条查询合计耗时 910 ms；ClickHouse 用了 74 ms，DuckDB 用了 76 ms，快约十二倍。第一个查询中，更多工作进程没能帮上 PostgreSQL：规划器只用了其中几个，跨进程分配行的开销与省下的时间大致相当。

列式存储默认使用所有核心，因此实验又在各用一个核心的条件下重跑了一遍（PostgreSQL 不使用并行工作进程）：

单核上，调优后的 PostgreSQL 合计耗时 999 ms，ClickHouse 为 131 ms（快约 7.6 倍），DuckDB 为 315 ms（快约 3.2 倍）。第一个查询的差距几乎消失：按天把时间戳分桶时，单核 DuckDB 仅比 PostgreSQL 快一点。其余两个查询的差距仍然很大。精确 p95 是最极端的例子：即使排序完全在内存中，PostgreSQL 仍要逐个元组对两百万个值排序，而列式存储遍历的是紧凑、已经解码的整数列。

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"

列式存储牺牲的，恰是应用数据库的职责

为了换取速度，列式存储牺牲的恰是应用数据库要做好的事。实验检查了四项：

• **点查。**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”。

列式存储最适合的是一张主要追加写入的宽表。你的应用数据模式恰好相反。

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

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"

"${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"

汇总表和列式扩展能大幅缩小差距

添一台服务器之前，先在 PostgreSQL 内试两件事。

**汇总表。**如果问题事先已知，就提前算好。按类型和日期做每日汇总，只有 1,826 行、12 页，却能给出与扫描 35,173 页相同的答案（预先算好要展示的内容）。没有哪个列式存储能胜过只读 12 页。问题变成临时分析时，汇总表就帮不上忙了。

以扩展形式提供的列式引擎（目前的选择）。pg_duckdb 在普通 PostgreSQL 表上运行 DuckDB 的向量化执行器。p95 查询快了约五倍，答案相同；另外两个查询几乎没变（最繁忙项目查询还稍慢了一点）。它仍然读取了字段拆列后的表副本的全部 22,752 个堆页（在独立服务器上，因此与前面的 22,784 页略有不同），所以使用自己列式文件的 DuckDB 还能再快约六倍（三条查询合计 76 ms，对比 474 ms）。向量化执行解决 CPU 这一半，只有列式存储才能解决 I/O 那一半。

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"

四个条件同时成立时，再把分析移出去

如果是两亿多条事件，相同的扫描就要读取约一百倍的页。这是算术推算，不是实测，但正是这种算术，让一次仪表盘查询变成了喝杯咖啡的等待。

四个条件同时成立时，再把分析移出去：查询是临时提出的，会扫描大表的大部分数据，数据以追加写入为主，而在主库或副本上运行它们会造成可以测量的损害。在此之前，带汇总表的副本能长期支撑报表。迁出之后，列式存储也只是从 PostgreSQL 接收数据的副本，绝不能成为行诞生的地方。

其他差距，只有规模上来才显现

分析查询是我们能在笔记本电脑上测出的差距。其他差距要到规模上来才显现，因此对每一项，我们都会说明机制、表现更好的系统类别和分界线；依据是资料，而非基准测试。提到的名字只是某类系统的例子，不是推荐。

搜索：PostgreSQL 排名不使用语料库统计信息

PostgreSQL 的排名函数 “do not use any global information”（Ranking Search Results），并且会读取每个匹配项。Elasticsearch、OpenSearch 这类引擎用语料库统计信息评分（BM25），增加同义词和分面功能，并对索引分片。分界线是：相关性已经成为有人专门负责的产品功能（参见何时 PostgreSQL 已经够用；先试 BM25 扩展）。

缓存：数据库不是缓存

让 PostgreSQL 在自己前面充当缓存，会被自身机制拖累：每个连接都是一个进程，每次写入都是一个新行版本（更新就是插入），数据不会自行过期，而且还与它本该保护的数据库共用一台机器。Valkey（采用 BSD 许可证的 Redis 分支）和 Redis 这类存储把数据放在内存中，支持生存时间，并允许调节持久性。分界线是：请求速率高到连接和单次写入开销已在测量中显现；在此之前，用不记日志表就好。

流：表不是日志

PostgreSQL 中的作业队列很好用。但供许多消费者使用、保留多日的事件日志就不合适了：消费会产生死元组，每个消费者的位置都要靠你自己实现的游标记录。Apache Kafka 这类日志系统在消费之后仍保留事件（根据主题配置，默认七天），各消费者可以按自己的节奏读取。通过 Debezium 这类 CDC 工具，从逻辑解码向它传送数据，不要再额外写一次。分界线是：需要扇出，以及以天计的重放。

时序：写入路径就是瓶颈

BRIN 和分区很适合处理按时间排序的数据（时序适合 BRIN 和按月分区）。摄入速率极高时，写入路径成了瓶颈：每行 24 字节的首部，每次单行插入约 600 字节 WAL（所有写入都汇入同一条 WAL 流），每个副本只有一个重放进程（重放只有一个进程）。时序数据库把数据批量写入压缩的列式块；TimescaleDB 在 PostgreSQL 内部完成这件事（有主张的分区）。分界线是：即使做了批处理，WAL 或重放仍跟不上摄入速度。

全球写入：每次提交都要付出往返时间

悉尼的用户向弗吉尼亚写入，每次提交都要承受一次往返延迟；地域级故障切换还可能丢失尚未传送的数据。分布式 SQL 数据库（Spanner、CockroachDB、YugabyteDB；后两者支持 PostgreSQL 协议，但有差异）通过共识提交，能承受一个地域的失效。它们无法打破物理规律，只是选择把往返开销放在哪里。分界线是：确实需要多大陆低延迟写入，或地域故障时零数据丢失。

向量：可建索引的列最多 2,000 维

pgvector 让相似度搜索就在数据旁边完成（向量搜索会找到临近的点，不在乎文字是什么），但也有限制：可建索引的列最多 2,000 维（使用 halfvec 时为 4,000 维，见 pgvector README），而过滤条件与近似索引会相互掣肘。专用向量数据库会分布式存储并量化索引。分界线（何时该用它）是：数亿个向量，或者索引比最大机器的内存还大。

图：每一跳仍是一次连接

递归 CTE 和闭包表可以处理树和少量跳转（Postgres 用三种方式存储图），但每一跳都是一次连接，指数增长的搜索前沿会让每次连接越来越大。图数据库直接沿存储好的关系走；Apache AGE把 openCypher 带进 PostgreSQL，但每一跳仍然是连接。分界线是：在大规模稠密图上运行图算法，或在请求路径上做多跳匹配。组织架构图远没到这个程度。

二进制大对象：字节应放进对象存储

存进去的每个字节都要经过 WAL，进入每个副本、备份和恢复过程。一张经过 TOAST 存储的 20 MB 图片，即使只为了生成缩略图，也得完整读取。对象存储放在 CDN 后面，可以容纳数十 TB 的对象（Amazon S3 为 50 TB）。分界线很低：超过几 KB、又不需要当作数据查询的内容，从第一天起就放进桶里，把元数据留在 PostgreSQL 的一行中。

用到之前，先认清信号

有实测理由，再引入第二个系统

这张表的每一行都有相同的结构：一种机制、一个信号，以及一个可以测量的数字。这就是规则。

能说清 PostgreSQL 缺少什么特性，并拿出测量结果证明需要它时，再增加一个系统。“p95 仪表盘查询要扫描 300 GB，花四分钟，而且每周的问题都在变”，这是理由。“我们迟早会需要分析”，不是理由。也要如实核算第二个系统的成本：又多一个需要备份、升级、保障安全，并在出问题时叫人处理的东西。

**让 PostgreSQL 始终作为权威数据源。**一行数据在 PostgreSQL 的事务中诞生，接受约束检查。索引、缓存、列式存储和流都由它派生，也都能从它重建。

**用变更数据捕获（change data capture，CDC）向外传送数据，不做双写。**先写 PostgreSQL，再写第二个系统，两次写入之间可能失败，而且无人察觉。用逻辑解码读取已提交的变更流（并监控复制槽），或者在同一个事务中写入一行事务发件箱记录。让消费者具有幂等性：某些变更会被它们看到两次。

凭证据离开

这条规则，就是最后再重申一次本书的两个习惯。

**数一数页面。**本书开头的查询访问了 35,173 页，却几乎没返回什么。到这里，同样的 35,173 页，只为回答关于两列的问题而读取，就成了把分析迁出去的理由；若用汇总表的 12 页回答同一个问题，就可以留在原地。

**先用数据证明，该换更大的机器了。**第二个数据库是最大的一台“机器”，还自带一套值班告警，也必须通过同一道关。

这两条合起来，就是本书开篇的原则：从一个数据库开始，凭证据离开。多数时候，证据会让你留下。逐页读懂一个 PostgreSQL，它能走得比名声所及更远。等证据告诉你该走时，你也会知道为什么，理由就在数字里。

本章要点

• 行式存储读取每行的所有列：我们的三条查询，每条都读取 35,173 页，无论只指定了多少列。
• ClickHouse 和 DuckDB 存储相同事件所用的字节数不到五分之一，每条查询的读取量不到十分之一，响应速度比调优后的 PostgreSQL 快约十二倍；单核上分别快约七倍和三倍（我们的机器，一次运行）。
• 列式存储牺牲了应用所需的能力：点查、强制键约束、低成本更新、事务、并发写入者。
• 先试折中办法：汇总表（12 页，而非 35,173 页）、专供报表的副本、pg_duckdb。
• 搜索、缓存、流、极高摄入速率、全球写入、海量向量、深层图和二进制大对象，各自都有相应的机制和信号；用到之前先了解。
• 只有能说清需要的特性，并能测出相应数字，才增加系统，而且要让 PostgreSQL 继续作为权威数据源。
• 数一数页面，先用数据证明该换更大的机器，从一个数据库开始：只有证据充分，才离开。

延伸阅读

• Parallel query，PostgreSQL 文档。PostgreSQL 何时使用多个进程扫描，以及限制它的设置；这些因素决定了行式存储在本章这类基准测试中的表现。
• Logical Decoding，PostgreSQL 文档。所有 CDC 工具赖以构建的变更流。
• A Practical Introduction to Primary Indexes，ClickHouse 文档。粒度单元、稀疏索引，以及为什么排序键就是整个设计的核心。
• Concurrency，DuckDB 文档。嵌入式引擎在进程之间能够、不能够共享什么。
• pg_duckdb，DuckDB 项目。在 PostgreSQL 内部运行 DuckDB 执行器，以及通过 SQL 读取数据湖格式。
• Debezium connector for PostgreSQL，Debezium 文档。从 PostgreSQL 捕获变更数据的完整参考：复制槽、快照及故障模式。
• What Goes Around Comes Around… And Around…，Michael Stonebraker 和 Andrew Pavlo，SIGMOD Record 2024。为何专用系统不断出现，关系模型又不断吸收它们。