Newshot

PostgreSQL 19:动手体验之旅,附可在浏览器中运行的示例

Translation(model translation, not the source's own text)

一个可以直接在提示符下试用的版本

PostgreSQL 19 在维护和复制改进之外,还带来了实用的新 SQL。试试 IGNORE NULLS、获取或创建式插入、JSON 导出、JSONPath 变换和范围相减,然后再探索规划器建议和真正的复制。

PostgreSQL 19.0 · 发布于 2026 年 11 月 19 日。稳定版公告介绍了本沙箱中安装的版本。每个功能都链接到其官方文档。发布说明仍是完整的变更日志。

关于此终端。实验在真实 PostgreSQL 19 服务器上以 student 身份运行,位于一次性客户机中。维护相关表位于 blog_lab。复制实验按需启动额外的真实服务器:5433 上的逻辑订阅者,以及 5434 上的物理备库,二者都由 5432 上的主库供给。为适应客户机的内存预算,这些额外实例同一时间只运行一个:每张复制卡片会停掉另一个并启动自己的那个。再次检查其当前状态前,请重新运行该卡片。它们的首次运行耗时更长,因为要初始化集群或执行基础备份。Reset 会丢弃全部三个集群及其数据。

如何与 18 对比。边注提供了可在 19 上运行的旧 SQL/工作流,或一个可禁用某项新优化的设置。这些展示的是表达式或行为上的差异。它们不会把 PostgreSQL 19 变成 PostgreSQL 18。关于计时结论,请在真实的 18 和 19 服务器上,以相同的数据、设置、缓存条件和硬件运行相同的工作负载。

可按任意顺序运行实验。以 \! 开头的命令会从 psql 运行一条 shell 命令。逻辑复制演练直接展示其服务器设置和 SQL;as-postgres 以 Linux postgres 账户运行服务器实用程序。查询和对比示例会说明何时使用另一个端口或管理 SQL 角色。

从你实际拥有的服务器开始

在阅读发布文章之前,先问服务器你实际运行的是哪个版本。SELECT 1 也是一个令人愉快地乏味的连通性测试:有结果胜过旋转的状态指示器。

下面返回的版本属于沙箱,而不是文章所覆盖的发布版本。SQL 角色是 student;对已准备好的演示数据库的所有权让你无需管理凭据即可进行实验。

要留意的内容:一行、真实的服务器版本,以及预期的角色。以后比较升级时,保留这个基线。

从 shell 打开演示数据库(PGDATABASE=blog_lab):

然后在 SQL 提示符下运行:


        SELECT 1 AS terminal_is_alive,
       current_setting('server_version') AS actual_version,
       current_user AS sql_role;
      

预期:一行,terminal_is_alive = 1,已安装的服务器版本,以及 student。

REPACK — 重写并回收空间

删除行会留下可复用的空间。常规 vacuum 会清理这些空间;PostgreSQL 19 的原生 REPACK 会重写表,并将未使用的字节归还给操作系统。该卡片删除中间的行,保留仍被占用的尾部页面,使普通 vacuum 无法简单地截断它们。比较 vacuum 之后的文件大小与真正重写之后的大小。这张小表演示的是该操作,而不是存储基准测试。REPACK 文档。

并发变体使用逻辑解码捕获期间的写入,并在最终交换时获取 access-exclusive 锁。完成卡片后,在同一个 psql 提示符下运行此示例。REPACK 需要表的 MAINTAIN 权限;student 拥有 events,因此无需切换角色。


        REPACK (CONCURRENTLY, ANALYZE) events;
      
与 18 对比:VACUUM (FULL, ANALYZE) events; 执行的是旧的阻塞式重写。对于物理索引顺序,比较 CLUSTER events USING events_pkey; 与 REPACK events USING INDEX events_pkey;。每次运行前恢复同一个膨胀表。在较大重写运行期间,使用第二个连接尝试 SELECT/INSERT,并检查 pg_locks:并发形式在交换时仍会短暂加锁。PostgreSQL 18 没有原生的并发等价物;单独安装的 pg_repack 扩展是另一种工作流。

        DELETE FROM events WHERE id BETWEEN 301 AND 700;
      

        VACUUM (ANALYZE) events;
      

        SELECT pg_relation_size('events') AS bytes_before_repack;
      

        REPACK (ANALYZE) events;
      

        SELECT count(*) AS surviving_events,
       pg_relation_size('events') AS bytes_after_repack
FROM events
WHERE id <= 1000;
      

预期:第一次运行输出 DELETE 400,然后 VACUUM、REPACK,最后是 600 个原始存活事件。比较前后字节数;大小会有所不同。

Autovacuum —— 检查真实的优先级分数

Autovacuum 现在使用真实分数对表进行优先级排序,并且可以并行化索引工作。该卡片读取 pg_stat_autovacuum_scores 和配置的分数权重。使用 \x on 将宽分数视图显示为可读记录,然后 \x off 返回表格以查看两列设置查询。分数会随工作负载和统计信息变化,因此维护后为零也是一个有用的结果。Autovacuum 调度、统计视图。

autovacuum_max_parallel_workers 控制服务器范围的允许值;autovacuum_parallel_workers 是每表设置。沙盒当前的并行允许值为零,因此该卡片检查优先级,而不是启动并行 vacuum 工作。PostgreSQL 19 还可以在普通扫描期间将符合条件的页面标记为 all-visible,从而改善未来的 index-only 扫描。

与 18 对比:在管理连接上,使用 ALTER SYSTEM 设置 autovacuum_max_parallel_workers = 0,然后使用 SELECT pg_reload_conf(); 重新加载以禁用此新的并行索引工作。之后恢复先前的值。手动 VACUUM (PARALLEL 0) 与 VACUUM (PARALLEL 2) 也可以在 18 上运行,但测量的是手动 vacuum。没有可靠的开关可以回到 PostgreSQL 18 的 autovacuum 调度器;请在单独的安装上比较调度。

        SELECT *
FROM pg_stat_autovacuum_scores
WHERE relid IN ('events'::regclass, 'orders'::regclass);
      

        SELECT name, setting
FROM pg_settings
WHERE name LIKE 'autovacuum%score_weight'
   OR name = 'autovacuum_max_parallel_workers'
ORDER BY name;
      

预期:先展开每表分数的记录,然后是一个包含配置权重的两列表格;最近 vacuum 过的表分数可能为零。

计划建议 —— 约束一个查询,然后释放它

pg_plan_advice 生成规划器选择的描述,并允许你约束选定的选择。pg_stash_advice 按查询标识符存储建议以供自动重用。在此数据库中,设置已为学生会话启用了建议模块;在另一个安装上,管理员可以使用 LOAD 'pg_plan_advice' 或配置预加载。计划建议、存储的建议。

第一个计划输出生成的建议;第二个请求 SEQ_SCAN(orders)。查找匹配的反馈。建议会限制正常的规划器,并且当数据变化时可能变得过时。

与 18 对比:在同一连接中运行设置、查询和重置:

        SET enable_indexscan = off;
SET enable_bitmapscan = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT sum(amount)
FROM orders
WHERE customer_id = 1;
RESET enable_indexscan;
RESET enable_bitmapscan;
      

这些宽泛的设置会抑制整类路径。PostgreSQL 19 的建议则可以针对查询中的指定表。我们的小表可能已经会选择顺序扫描;有趣的证据是建议及其反馈。

检查生成的计划建议。


        EXPLAIN (PLAN_ADVICE, COSTS OFF)
SELECT sum(amount)
FROM orders
WHERE customer_id = 1;
      

应用会话建议,检查计划,然后重置设置。


        SET pg_plan_advice.advice = 'SEQ_SCAN(orders)';
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT sum(amount)
FROM orders
WHERE customer_id = 1;
RESET pg_plan_advice.advice;
      

预期:先出现 Generated Plan Advice,再出现 Supplied Plan Advice,并带有匹配的反馈和一个 Aggregate 计划。

逻辑复制——看看两台服务器之间移动了什么

跟踪一行数据和一个计数器从源端到目标端。5432 上的发布者拥有源数据。5433 上的订阅者是一个独立的 PostgreSQL 进程,有自己的数据目录。两个数据库都名为 pg19_repl;端口决定了你正在更改哪一个。

对象发布者 · 5432订阅者 · 5433
messages写入一行发布复制该行
scratch写入一行被排除在发布之外;保持为空
ticket_numbers消费 50 个值显式刷新会复制序列状态

下面的命令展示了完整的设置。按顺序运行它们,或使用 Run all steps 输入相同的可见命令。\! 从 psql 运行 shell 命令。as-postgres 以 Linux postgres 账户运行后续命令;initdb 初始化集群,pg_ctl 启动它。\connect database role host port 选择 SQL 连接。此处的管理 SQL 明确使用 SQL 角色 postgres。本地 trust 认证仅用于此一次性访客环境。

先停止实验 06 的备库,以适配访客的内存预算。订阅者在此运行时使用小缓冲区和同步 I/O。其日志为 /tmp/pg19-subscriber.log。条件式设置会在重复运行时保留现有对象;每次重复都会再次推进发布者计数器。

19 有什么新特性?FOR ALL TABLES EXCEPT (TABLE scratch) 排除一个表,ALL SEQUENCES 包含序列状态。初始订阅创建会复制序列值;之后发布者调用 nextval 可能使订阅者落后。REFRESH SEQUENCES 请求再次复制。我们在推进发布者之前等待初始复制完成,这样它就无法掩盖前后对比。可见的轮询循环设有截止时间,因为同步是异步的。两侧服务器上的定义必须匹配。序列同步、发布语法。

订阅通过回环 TCP 接收数据;psql 使用本地 Unix 套接字进行管理。创建发布后,在发布者上比较 wal_level = replica 与 effective_wal_level = logical:PostgreSQL 19 在需要时启用逻辑 WAL,而无需重启此主库。WAL 配置。

与 18 对比:使用 FOR TABLE messages 显式发布被包含的表。发布者需要 wal_level = logical 并重启。序列状态不会被复制:在切换时,停止发布者写入,读取 last_value 和 is_called,并在订阅者上应用 setval('ticket_numbers', captured_value, captured_is_called)。在 19 中,重复下面的步骤,并比较 REFRESH SEQUENCES 前后的计数器。

1 · 为订阅者腾出空间。仅当实验 06 已启动物理备库时才停止它。


        \! if as-postgres pg_ctl status -D /tmp/pg19-standby >/dev/null 2>&1; then as-postgres pg_ctl stop -D /tmp/pg19-standby -m fast -w; fi
      

2 · 初始化一个独立集群。initdb 创建其数据目录;该条件在重复运行时保留它。


        \! if ! as-postgres test -f /tmp/pg19-subscriber/PG_VERSION; then as-postgres initdb -D /tmp/pg19-subscriber -U postgres --auth=trust --locale=C -E UTF8; fi
      

3 · 在端口 5433 上启动该集群。pg_ctl 等待启动;-o 选项为此访客选择端口、套接字和小内存设置。


        \! as-postgres pg_ctl status -D /tmp/pg19-subscriber || as-postgres pg_ctl start -D /tmp/pg19-subscriber -l /tmp/pg19-subscriber.log -w -o "-p 5433 -h 127.0.0.1 -k /tmp -c dynamic_shared_memory_type=mmap -c shared_buffers=16MB -c max_connections=10 -c io_method=sync"
      

4 · 发布端,端口 5432。如果源数据库不存在则创建。psql 的 \gexec 仅在 SELECT 返回 CREATE DATABASE 文本时才执行它。


        \connect postgres postgres /tmp 5432
SELECT 'CREATE DATABASE pg19_repl OWNER student'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'pg19_repl') \gexec
      

5 · 发布端 schema 与发布。messages 被包含,scratch 被排除,ticket_numbers 通过 ALL SEQUENCES 显式发布。存在性检查使配置可重复执行。


        \connect pg19_repl postgres /tmp 5432
CREATE TABLE IF NOT EXISTS messages (id integer PRIMARY KEY, body text);
CREATE TABLE IF NOT EXISTS scratch (id integer);
CREATE SEQUENCE IF NOT EXISTS ticket_numbers START 1;
SELECT 'CREATE PUBLICATION pg19_pub FOR ALL TABLES EXCEPT (TABLE scratch), ALL SEQUENCES'
WHERE NOT EXISTS (SELECT FROM pg_publication WHERE pubname = 'pg19_pub') \gexec
SELECT pubname, puballtables, puballsequences FROM pg_publication;
SHOW wal_level;
SHOW effective_wal_level;
      

6 · 订阅端,端口 5433。创建目标数据库及匹配的对象。逻辑复制不会创建它们的定义。


        \connect postgres postgres /tmp 5433
SELECT 'CREATE DATABASE pg19_repl'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'pg19_repl') \gexec
\connect pg19_repl postgres /tmp 5433
CREATE TABLE IF NOT EXISTS messages (id integer PRIMARY KEY, body text);
CREATE TABLE IF NOT EXISTS scratch (id integer);
CREATE SEQUENCE IF NOT EXISTS ticket_numbers START 1;
      

7 · 订阅端。订阅 5432 上的发布端。连接字符串是源;当前 psql 连接是目标。


        SELECT $sql$CREATE SUBSCRIPTION pg19_sub
  CONNECTION 'host=127.0.0.1 port=5432 user=postgres dbname=pg19_repl'
  PUBLICATION pg19_pub$sql$
WHERE NOT EXISTS (SELECT FROM pg_subscription WHERE subname = 'pg19_sub') \gexec
      

8 · 在修改发布端之前,等待初始序列复制完成。这个可见的 shell 循环检查同步元数据,最多检查 300 次,每次检查之间休眠 0.1 秒。


        \! ready=0; for attempt in $(seq 1 300); do if [ "$(psql -w -X -h /tmp -p 5433 -U postgres -d pg19_repl -At -c "SELECT count(*) FROM pg_subscription_rel WHERE srrelid = 'ticket_numbers'::regclass AND srsublsn IS NOT NULL")" = 1 ]; then ready=1; break; fi; sleep 0.1; done; test "$ready" = 1 && echo 'Initial sequence copy ready' || echo 'Initial sequence copy timed out' >&2
      

9 · 发布端。写入一行被包含的行和一行被排除的行。将序列推进 50 次,使变更跨越一个序列 WAL 批次;记录 publisher_ticket。


        \connect pg19_repl postgres /tmp 5432
INSERT INTO messages VALUES (1, 'hello from the publisher')
ON CONFLICT (id) DO UPDATE SET body = EXCLUDED.body;
INSERT INTO scratch SELECT 1 WHERE NOT EXISTS (SELECT FROM scratch WHERE id = 1);
SELECT max(n) AS publisher_ticket
FROM (SELECT nextval('ticket_numbers') AS n FROM generate_series(1, 50)) q;
SELECT last_value AS publisher_ticket, is_called FROM ticket_numbers;
      

10 · 订阅端,刷新之前。即使表变更在复制,其计数器也可能滞后。将 subscriber_ticket 与上面的发布端值进行比较。


        \connect pg19_repl postgres /tmp 5433
SELECT last_value AS subscriber_ticket_before_refresh, is_called FROM ticket_numbers;
      

11 · 订阅端。显式请求序列同步。ALTER SUBSCRIPTION 会调度异步工作;它不会等待新值。


        ALTER SUBSCRIPTION pg19_sub REFRESH SEQUENCES;
      

12 · 等待序列值相等以及被包含的行。此处 SQL 和截止时间可见;此循环仅观察服务器。


        \! target=$(psql -w -X -h /tmp -p 5432 -U postgres -d pg19_repl -At -c 'SELECT last_value FROM ticket_numbers'); synced=0; for attempt in $(seq 1 300); do if [ "$(psql -w -X -h /tmp -p 5433 -U postgres -d pg19_repl -At -c "SELECT (last_value = $target AND is_called AND EXISTS (SELECT FROM messages WHERE id = 1 AND body = 'hello from the publisher'))::int FROM ticket_numbers")" = 1 ]; then synced=1; break; fi; sleep 0.1; done; test "$synced" = 1 && echo 'Sequence synchronization confirmed' || echo 'Sequence synchronization timed out' >&2
      

13 · 订阅端证据。messages 中包含已发布的这一行,scratch 保持为空,且 subscriber_ticket 与 publisher_ticket 一致。查看同步元数据,然后以 student 身份返回 blog_lab。


        SELECT * FROM messages;
SELECT count(*) AS excluded_scratch_rows FROM scratch;
SELECT last_value AS subscriber_ticket_after_refresh, is_called FROM ticket_numbers;
SELECT srrelid::regclass, srsubstate, srsublsn
FROM pg_subscription_rel ORDER BY 1;
\connect blog_lab student /tmp 5432
      

预期结果:messages 包含 1 | hello from the publisher;excluded_scratch_rows 为 0;subscriber_ticket_after_refresh 与 publisher_ticket 一致。配置的 WAL 为 replica;实际生效的 WAL 为 logical。超时意味着同步尚未确认。重复运行时这些值会继续推进。

WAIT——追踪一次已提交写入到物理备库的过程

一次写入可能已在主库提交,而备库还读不到它。本演练跟踪一个 ID 为 2000 的事件,从 5432 上的主库到 5434 上的物理备库。与第 05 节中的逻辑订阅端不同,物理备库从整个集群的备份开始,并重放 WAL 以保持该副本为最新。

按顺序执行这些步骤,或使用 Run all steps。下面展示了每条服务器命令和 SQL 语句。\! 从 psql 中执行 shell 命令;as-postgres 以 Linux 的 postgres 账户运行服务器工具。SQL 写入和读取使用 student,每次 \connect 都会指明目标端口。首先停止逻辑订阅端,以适配该 guest 的内存预算。备份及其复制槽在重复运行时会复用。物理备库设置。

读取分为三个阶段:提交写入 → 捕获其 WAL 位置 → 等待备库重放。在 INSERT 提交后捕获 LSN。等待 WAL 到达或完成刷盘,并不能说明这些行已被应用。MODE 'standby_replay' 会等待该应用步骤;接下来的 SELECT 会获取一个新快照。在本示例中,请将 WAIT 保持在显式事务之外。WAIT 文档。

请留意:WAIT 返回成功,然后是 is_standby = true、replay_reached_target = true,以及预期的行。如果 WAIT 超时,请检查 /tmp/pg19-standby.log,并在解决延迟后重试等待。不要将超时视为可以执行依赖读取的许可。如果故障切换改变了时间线,请确认捕获的 LSN 是否仍指向预期的历史。

与 18 对比:在备库上轮询 SELECT pg_last_wal_replay_lsn() >= :'target_lsn'::pg_lsn;,配合短暂 sleep 和总体截止时间。只有它返回 true 后才读取。PostgreSQL 19 的 WAIT 在服务器内部执行这一等待。可选的 NO_THROW 会将超时或未处于恢复状态作为状态返回,而不是报错;你的应用程序必须在读取前处理该状态。

1 · 为物理备库腾出空间。在停止实验 05 的逻辑订阅端之前,先连接到主库;此 guest 中只能再容纳一台额外的服务器。


        \connect blog_lab student /tmp 5432
\! if as-postgres pg_ctl status -D /tmp/pg19-subscriber >/dev/null 2>&1; then as-postgres pg_ctl stop -D /tmp/pg19-subscriber -m fast -w; fi
      

文章标题:PostgreSQL 19:可运行浏览器示例的实操之旅

正文: 2 · 复制主集群。pg_basebackup 从 5432 端口获取初始物理备份。-R 配置备用模式及其上游连接;-X stream 包含备份期间生成的 WAL。-C -S 创建一个命名复制槽,为该备用节点保留 WAL。该条件在重复运行时复用备份。


        \! if ! as-postgres test -f /tmp/pg19-standby/PG_VERSION; then as-postgres pg_basebackup -h /tmp -p 5432 -U postgres -D /tmp/pg19-standby -R -X stream -c fast -C -S pg19_standby; fi
      

3 · 为复制的集群分配其自己的端口。它继承了主节点的配置,因此将端口 5432 覆盖为 5434。清空预加载库并禁用日志收集,使这个额外实例保持在客户机的内存预算之内。


        \! as-postgres sh -c "printf '%s\n' 'port = 5434' \"shared_preload_libraries = ''\" 'logging_collector = off' >> /tmp/pg19-standby/postgresql.auto.conf"
      

4 · 启动备用节点。pg_ctl 从复制的数据目录启动 PostgreSQL,并等待其接受连接。正在运行的备用节点保持运行;启动消息写入 /tmp/pg19-standby.log。


        \! as-postgres pg_ctl status -D /tmp/pg19-standby || as-postgres pg_ctl start -D /tmp/pg19-standby -l /tmp/pg19-standby.log -w
      

5 · 验证目标端。以 student 身份连接到 5434 端口上的 blog_lab。is_standby 必须为 true,port 必须为 5434。物理备份包含数据库及其表;此处无需 CREATE TABLE。


        \connect blog_lab student /tmp 5434
SELECT pg_is_in_recovery() AS is_standby, current_setting('port') AS port;
      

6 · 在主节点上提交一次写入。切换到 5432,启用 psql 自动提交,并插入一条 ID 为 2000 的事件。下一条命令在此 INSERT 提交后运行。ON CONFLICT 使该演示可重复执行。


        \connect blog_lab student /tmp 5432
\set AUTOCOMMIT on
INSERT INTO events VALUES (2000, 'replica-visible write')
ON CONFLICT (id) DO UPDATE SET payload = EXCLUDED.payload;
      

7 · 捕获提交后的 WAL 位置。LSN 是预写日志中的一个位置。该位置处于或超出我们事务的提交记录。psql 的 \gset 将其保存为 target_lsn;\echo 显示该值。客户端变量在切换服务器后仍然保留。


        SELECT pg_current_wal_insert_lsn() AS target_lsn \gset
\echo Target WAL position: :target_lsn
      

8 · 在备用节点上等待重放。切换到 5434,并用 :'target_lsn' 替换已保存的 LSN。standby_replay 会等到 WAL 被应用到数据库。TIMEOUT 将等待限制为 30 秒;出错会在读取之前停止 Run all steps。已追上的备用节点会立即返回成功。


        \connect blog_lab student /tmp 5434
WAIT FOR LSN :'target_lsn'
WITH (MODE 'standby_replay', TIMEOUT '30s');
      

9 · WAIT 成功后读取。这个单独的 SELECT 在备用节点上获取一个新的快照。预期 is_standby 和 replay_reached_target 为 true,并且存在 ID 为 2000、payload 为 replica-visible write 的事件。最后返回主节点以进行下一个实验。


        SELECT pg_is_in_recovery() AS is_standby,
       pg_last_wal_replay_lsn() >= :'target_lsn'::pg_lsn AS replay_reached_target,
       id, payload
FROM events WHERE id = 2000;
\connect blog_lab student /tmp 5432
      

预期结果:在 5434 端口上:is_standby = true;WAIT 返回成功;单独读取显示 replay_reached_target = true,并且存在 ID 为 2000、payload 为 replica-visible write 的事件。目标 LSN 值会有所不同。超时意味着备用节点尚未重放目标。

EXPLAIN IO —— 看清等待背后的工作

EXPLAIN (ANALYZE, IO) 在 buffers 之外增加了异步 I/O 证据。Worker I/O 现在可以使用 io_min_workers、io_max_workers、io_worker_idle_timeout 和 io_worker_launch_interval 来扩展和收缩其 worker 池。EXPLAIN 选项、I/O 设置。

沙箱配置为同步 I/O。该卡片演示新的 EXPLAIN 语法;它在此处无法测量异步 worker 池。微小的热表也不适合作为存储基准测试。

PostgreSQL 19:带可运行浏览器示例的实操之旅

与 18 对比:用 EXPLAIN (ANALYZE, BUFFERS) 运行一次查询,再用新的 IO 选项运行一次,以查看额外报告。在配置了 worker I/O 的原生服务器上,将 19 的最小和最大 worker 数设为相同值,可近似 18 的固定 worker 数策略;这并不会恢复旧实现。请保留一份单独的、真实的 18 基线用于吞吐量比较。

        SHOW io_method;
      

        EXPLAIN (ANALYZE, IO, BUFFERS, COSTS OFF)
SELECT count(*)
FROM events
WHERE length(payload) > 100;
      

预期:访客使用 sync I/O。EXPLAIN 接受 IO 并报告 buffers;异步活动可能缺失或为零。

外键——更快的插入,同样的约束

大多数应用模式都有关系。这里,一个客户可以有零个或多个订单,而每个订单可以引用零个或一个客户,因为 customer_id 可为空。每个非 NULL 的客户引用都必须存在,因此外键检查位于普通订单插入的热路径上。

Beta 1 公告报告,对于涉及外键检查的工作负载,插入性能最高约提升至两倍。这是特定于工作负载的结果,并不保证每个 INSERT 都会快两倍。

插入一个演示订单,然后将其与客户连接。ON CONFLICT 子句使实验可重复。引用不存在客户的插入仍会被拒绝:更快的检查保留了约束的职责。

观察点:订单 1001 关联到 Ada。要评估升级,请使用相同的关系、事务大小和争用情况重放现实批次。不要关闭约束来让比较结果看起来更好。


        INSERT INTO orders VALUES (1001, 1, 42)
ON CONFLICT (id) DO NOTHING;
      

        SELECT o.id, c.name, o.amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.id = 1001;
      

预期:首次运行返回 INSERT 0 1,然后返回 1001 | Ada | 42。

IGNORE NULLS——向前携带最后已知值

lag、lead、first_value、last_value 和 nth_value 现在支持 IGNORE NULLS。这里,last_value 在间隙中携带最近已知的读数。显式 frame 结束于当前行,因此未来的观测不会向后泄漏。窗口函数。

与 18 对比:当读数下降时,运行最大值是错误的。在同一数据上运行这个旧的双窗口变通方案,并将其结果与卡片进行比较:

        -- PostgreSQL 18 approach: group readings, then fill each group.
WITH islands AS (
  SELECT tick, reading,
         count(reading) OVER (ORDER BY tick ROWS UNBOUNDED PRECEDING) AS grp
  FROM readings
)
SELECT tick, reading, max(reading) OVER (PARTITION BY grp) AS carried
FROM islands ORDER BY tick;
      

每个非空值开始一个新组。结果匹配;19 的表达式需要一个窗口,而不是分组变通方案。


        -- PostgreSQL 19 approach: carry the latest non-null reading forward.
SELECT tick, reading,
       last_value(reading) IGNORE NULLS OVER (
         ORDER BY tick
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS carried
FROM readings ORDER BY tick;
      

预期:携带值为 10、10、7、7,而原始缺失读数保持为 NULL。

获取或创建——返回冲突行而不更新它

ON CONFLICT DO SELECT ... RETURNING 实现了无需虚拟更新的获取或创建。运行两次:第二次调用返回现有 ID 和元组 xmin。可选的 FOR UPDATE / FOR SHARE 可以锁定冲突行。INSERT 文档。

与 18 对比:将 DO SELECT 替换为 DO UPDATE SET handle = EXCLUDED.handle 并重复执行。它会返回 ID,但也会创建一个新的行版本,并且可能触达 UPDATE 触发器。检查 before/after 的 xmin;在 autocommit 下它会变化。DO NOTHING RETURNING 在发生冲突时不返回任何行。常见的单语句 CTE 回退方案在 READ COMMITTED 下可能漏掉并发插入的行;插入后再查询的重试循环是另一种 18 的方案。即使是在新的冲突路径上,序列也可能出现间隙,因为默认值是在解决冲突之前求值的。

        INSERT INTO handles(handle) VALUES ('ada')
ON CONFLICT (handle) DO SELECT
RETURNING id, handle, xmin;
      

预期:两次调用都为 ada 返回相同的 id 和 xmin;第二次调用会选择已存在的行。

COPY TO JSON — 直接导出对象

COPY TO 现在可以直接输出 JSON,既可以每行一个对象,也可以使用 FORCE_ARRAY 输出单个数组。SQL NULL 会变成 JSON null。COPY 格式。

与 18 对比:使用 SQL 函数生成对象或数组:

        -- PostgreSQL 18: one JSON object per row.
SELECT row_to_json(c)
FROM (SELECT id, name FROM customers ORDER BY id) c;

-- PostgreSQL 18: one JSON array.
SELECT json_agg(c ORDER BY id)
FROM (SELECT id, name FROM customers) c;
      

要在 shell 中获得干净整洁的输出流:


        psql -X -At -d blog_lab \
  -c 'SELECT row_to_json(c) FROM customers c ORDER BY id'
      

应比较解析后的 JSON,而不是空白字符。聚合会在内存中构造一个数组;COPY 提供直接的导出路径。


        COPY (SELECT id, name FROM customers ORDER BY id)
TO STDOUT (FORMAT JSON);
      

        COPY (SELECT id, name FROM customers ORDER BY id)
TO STDOUT (FORMAT JSON, FORCE_ARRAY);
      

预期:先是两个 JSON 对象,然后是一个包含 Ada 和 Grace 的 JSON 数组。

COPY FROM — 保留含单个畸形字段的行

准备好的 CSV 包含两行前言/表头行和 2,oops。HEADER 2 跳过这两行;ON_ERROR set_null 保留第 2 行,并将其中无效的整数替换为 NULL。示例使用客户端 \copy,因此学生不需要服务器端文件权限。COPY 错误处理。

与 18 对比:在 CSV 预处理步骤中删除第一行前言行,使用 HEADER,并选择 ON_ERROR ignore:坏行会被丢弃而不是保留。要在 18 上保留它,可将文本导入暂存表并转换:

        CASE
  WHEN pg_input_is_valid(reading, 'integer')
  THEN reading::integer
END
      

之后检查这三行。set_null 处理输入转换失败;CHECK、NOT NULL、外键或畸形 CSV 错误仍可能中止导入。


        \! cat /home/student/pg19-import.csv
      

        TRUNCATE imported_readings;
      

        \copy imported_readings FROM /home/student/pg19-import.csv WITH (FORMAT CSV, HEADER 2, ON_ERROR set_null)
      

        SELECT id, reading,
       reading IS NULL AS repaired
FROM imported_readings
ORDER BY id;
      

预期:COPY 3。第 2 行保留下来,reading 为 NULL;第 1 行和第 3 行分别保留 42 和 7。

COPY 分区父表 — 包含每个分区

COPY partitioned_parent TO ... 现在直接导出其子行。示例中的两个分区各贡献一行。COPY 关系行为。

与 18 对比:使用较旧的查询包装器:

        COPY (SELECT * FROM export_events ORDER BY id)
TO STDOUT (FORMAT CSV, HEADER);
      

它也提供确定的顺序;普通表 COPY 不保证任何顺序。


        COPY export_events TO STDOUT (FORMAT CSV, HEADER);
      

预期:id,label 表头以及 1,low 和 11,high 两行(行顺序未指定)。

JSONPath — 在路径内部转换字符串

JSONPath 新增 lower、upper、initcap、replace、split_part、ltrim、rtrim 和 btrim。示例直接在路径中将每个 email 转换为小写 handle。JSONPath 方法。

与 18 对比:提取到 SQL 字符串,再将结果聚合回 JSON:

文章标题:PostgreSQL 19:可运行浏览器示例的实操之旅

正文:


        SELECT jsonb_agg(split_part(lower(email), '@', 1) ORDER BY ord)
FROM jsonb_array_elements_text(
  '["[email protected]","[email protected]"]'::jsonb
) WITH ORDINALITY AS e(email, ord);
      

这保留了输入顺序,与字符串数组示例一致。混合类型数组需要显式处理;JSONPath 的 lax/strict 行为可能与将每个元素作为文本提取的方式不同。


        SELECT jsonb_path_query_array(
  '["[email protected]","[email protected]"]'::jsonb,
  '$[*].lower().split_part("@", 1)'
) AS handles_from_json;
      

预期:JSON 数组 ["ada", "grace"]。

范围相减——返回剩余的两段

range_minus_multi 将每个非空的剩余范围作为一行返回。multirange_minus_multi 返回非空的 multirange 结果,若没有剩余则返回零行。适用于可用时间减去已预订时间。Range 函数。

与 18 对比:先相减 multirange,再将结果展开为行:

        SELECT unnest(
  int4multirange('[0,10)'::int4range)
  - int4multirange('[3,7)'::int4range)
);
      

这同样得到两段。直接用 - 进行范围相减无法表示被拆分成两个不相交范围的结果。


        SELECT remaining
FROM range_minus_multi(
  '[0,10)'::int4range,
  '[3,7)'::int4range
) AS remaining
ORDER BY remaining;
      

预期:两行:[0,3) 和 [7,10)。

随机日期与时间戳——生成贴近实际的测试数据

random(min, max) 现在接受 date、timestamp 和 timestamptz 边界。该卡片生成五个测试样本并检查日期边界。Random 函数。

与 18 对比:用 date '2026-01-01' + floor(random() 365)::int 生成日期,或用 timestamp '2026-01-01' + random() interval '1 day' 生成时间戳。保持相同的边界;输出样本会不同。这些是测试数据生成器,不是加密随机性。

        WITH samples AS (
  SELECT random(date '2026-01-01', date '2026-12-31') AS d,
         random(
           timestamp '2026-01-01',
           timestamp '2026-01-02'
         ) AS ts,
         random(
           timestamptz '2026-01-01 00:00+00',
           timestamptz '2026-01-02 00:00+00'
         ) AS tz
  FROM generate_series(1,5)
)
SELECT d, ts, tz,
       d BETWEEN date '2026-01-01' AND date '2026-12-31' AS within_bounds
FROM samples;
      

预期:五个各不相同的样本;所有 within_bounds 值均为 true。

二进制值——UUID 转换与 URL 友好的编码

本节演示 PostgreSQL 19 的两项新增功能:

  • UUID 转换:UUID 可以直接转换为 bytea(PostgreSQL 的原始字节类型),也可以转换回来。第一个查询检查其大小,并验证转换回来后能得到原始 UUID。
  • 文本编码:base64url 和 base32hex 将原始字节转换为可打印字符串。第二个查询从文本 Hi 开始,将其转换为 UTF-8 字节,并以两种格式对这些字节进行编码:在 Base64URL 中为 SGk,在 Base32hex 中为 91KG====。末尾的 = 字符是填充,不是额外数据。编码改变字节的表示方式;解码可还原字节。

base64url 使用 - 和 _ 代替标准 Base64 中的 + 和 / 字符。base32hex 使用数字 0–9 后跟字母 A–V;通过逐字节比较,该字母表支持保留字节顺序。这个小示例展示的是编码结果,而非测试其排序。二进制编码,UUID 类型。

与 18 对比:对于 UUID 字节,使用 decode(replace(u::text, '-', ''), 'hex')。对于 URL 令牌,使用 rtrim(translate(encode(bytes, 'base64'), '+/', '-_'), '='),对于较长的字节字符串需移除嵌入的换行符。在相同字节上与 19 的原生编码器进行比较。

1 · 将 UUID 转换为字节再转换回来。示例 UUID 命名为 u。u::bytea 将其转换为原始字节;octet_length 计算字节数。将这些字节转换回 uuid 并与 u 比较,可检查原始值是否保持不变。预期 uuid_bytes = 16 且 round_trip = true。


        WITH value AS (
  SELECT '123e4567-e89b-12d3-a456-426614174000'::uuid AS u
)
SELECT octet_length(u::bytea) AS uuid_bytes,
       (u::bytea)::uuid = u AS round_trip
FROM value;
      

2 · 将字节转换为文本令牌。convert_to('Hi', 'UTF8') 将熟悉的文本 Hi 转换为其两个 UTF-8 字节。两个 encode 调用都将这些相同的字节表示为文本:base64url 使用适合 URL 令牌的字母表,而 base32hex 使用数字 0–9 和字母 A–V。比较这两种表示;本示例不使用上面的 UUID。


        SELECT encode(convert_to('Hi', 'UTF8'), 'base64url') AS url_token,
       encode(convert_to('Hi', 'UTF8'), 'base32hex') AS ordered_encoding;
      

预期:第一个结果:uuid_bytes = 16 且 round_trip = true。第二个结果:Hi 变为 url_token = SGk 且 ordered_encoding = 91KG====。两者编码相同的两个 UTF-8 字节;等号是 Base32hex 填充。

目录标识 — 数据库名称和物理元组地址

regdatabase 将数据库名称解析为其 OID,并再次显示该名称。oid8 是无符号 64 位类型;tid_block 和 tid_offset 拆分物理元组标识符,而无需解析其文本。标识符类型、系统函数。

与 18 对比:使用 SELECT oid FROM pg_database WHERE datname = current_database(); 查找 OID。如果需要建模无符号 64 位值,请使用带边界 CHECK 的 numeric(20,0)。ctid 在更新或重写后可能变化:用它检查存储,而不是作为永久业务标识符。

        SELECT current_database()::regdatabase::oid AS database_oid,
       '18446744073709551615'::oid8 AS unsigned_max;
      

        SELECT id, ctid,
       tid_block(ctid) AS block_number,
       tid_offset(ctid) AS tuple_offset
FROM customers
ORDER BY id;
      

预期:数据库 OID、完整的无符号 64 位最大值,以及每个 ctid 的块/偏移部分。

CHECK 约束 — 显式切换强制执行

版本 19 允许 ALTER TABLE ... ALTER CONSTRAINT 切换 CHECK 强制执行;这以前适用于外键。该卡片暂时禁用规则,插入负价格,清理它,并恢复强制执行。约束修改。

与 18 对比:删除并重新创建 CHECK。ADD CONSTRAINT ... NOT VALID 是另一种操作:它跳过验证旧行,同时仍对新写入强制执行规则。版本 19 还允许在虚拟生成列具有 CHECK 约束时更改其表达式;扩展统计信息现在可以包含虚拟生成列。

        TRUNCATE prices;
      

        ALTER TABLE prices ALTER CONSTRAINT positive_price NOT ENFORCED;
      

        INSERT INTO prices VALUES (-10);
      

        SELECT amount FROM prices;
      

        DELETE FROM prices WHERE amount <= 0;
      

        ALTER TABLE prices ALTER CONSTRAINT positive_price ENFORCED;
      

        SELECT conname, conenforced
FROM pg_constraint
WHERE conrelid = 'prices'::regclass;
      

预期:强制执行关闭时,负金额会被接受;清理它后,positive_price 再次被强制执行。

NOT IN — 查找未被阻止的候选者

查找不在阻止列表中的候选者。准备好的 candidate_ids 表包含 ID 1–1000;blocked_ids 包含 1–500。NOT IN 条件排除那些被阻止的 ID,留下 501–1000:500 行。

PostgreSQL 19 可以将此排除转换为反连接:仅当没有匹配的阻止行时才保留候选者。这里,两个 ID 列都是主键,因此二者都不能包含 NULL。这使规划器能够使用此优化;其连接方法的选择仍取决于成本。规划器变更。

第一个命令展示查询如何执行。第二个展示它返回多少行。

1 · 检查 PostgreSQL 如何找到未阻止的 ID。EXPLAIN ANALYZE 执行 SELECT 并显示其计划。查找反连接:它保留没有匹配被阻止 ID 的候选行。确切的连接方法取决于规划器成本。

文章标题:PostgreSQL 19:一次可运行的浏览器示例动手之旅

正文:


        EXPLAIN (ANALYZE, COSTS OFF)
SELECT id
FROM candidate_ids
WHERE id NOT IN (SELECT id FROM blocked_ids);
      

2 · 统计结果。这一步在不使用 EXPLAIN 的情况下执行同样的排除逻辑,并返回未被封锁的 ID 数量。预期 unblocked = 500,对应 ID 501–1000。


        SELECT count(*) AS unblocked
FROM candidate_ids
WHERE id NOT IN (SELECT id FROM blocked_ids);
      

预期:第一段:一个执行计划,可能使用 Anti Join。第二段:unblocked = 500。两条语句都排除了被封锁的 ID 1–500。

在 PostgreSQL 18 上,表达同样排除逻辑的常见方式是 NOT EXISTS。可以这样理解:“如果没有任何被封锁的行具有相同的 ID,就保留这个候选行”:


        -- PostgreSQL 18 approach: exclude candidates with a matching blocked ID.
SELECT c.id
FROM candidate_ids c
WHERE NOT EXISTS (
  SELECT 1
  FROM blocked_ids b
  WHERE b.id = c.id
);
      

对于这些主键 ID,该查询返回相同的 ID 501–1000。要比较计划,可在前面加上 EXPLAIN (ANALYZE, COSTS OFF)。

如果被封锁列表可能包含 NULL,NOT IN 和 NOT EXISTS 可能产生不同结果。该示例通过其主键约束避免了这一差异。在 19 上运行旧查询是在比较 SQL 形式;要比较计划器版本,请使用实际的 PostgreSQL 18 服务器。

Eager aggregation —— 在连接之前进行聚合

版本 19 可以在连接之前先聚合符合条件的行,从而减少中间行数量。该卡片在默认开启 enable_eager_aggregate 的情况下运行同一个聚合,然后在一个会话中将其关闭。请留意连接下方的 Partial HashAggregate:数百条订单在连接之前被归并为两个客户汇总。关闭 eager aggregation 后,连接会先处理订单,然后再聚合。计划选择仍取决于统计信息和数据。优化器变更。

与 18 对比:第二条命令在 19 上禁用了这一特定新优化。如下所示,在同一个连接中运行 SET、EXPLAIN 和 RESET。这是一种有用的受控比较,但 19 的其他所有计划器改进仍然生效。

在启用 eager aggregation 的情况下检查计划。


        EXPLAIN (ANALYZE, COSTS OFF)
SELECT c.name, sum(o.amount)
FROM customers c
JOIN orders o ON o.customer_id=c.id
GROUP BY c.name;
      

为进行比较而禁用 eager aggregation,然后重置它。


        SET enable_eager_aggregate = off;
EXPLAIN (ANALYZE, COSTS OFF)
SELECT c.name, sum(o.amount)
FROM customers c
JOIN orders o ON o.customer_id=c.id
GROUP BY c.name;
RESET enable_eager_aggregate;
      

预期:同一结果的两个计划。较小的数据集可能产生相同的计划形态;第二个计划禁用了 eager aggregation。

默认值与诊断 —— 检查有哪些变化

版本 19 默认禁用 JIT,启用锁等待日志记录,在支持的情况下将默认 TOAST 压缩器改为 LZ4,并允许 wal_compression = on 选择可用的最佳压缩方法。max_locks_per_transaction 从 64 变为 128,同时伴随锁分配方式的改变。普通字符串始终使用符合标准的行为;旧的 off 模式已被移除。该卡片区分了每个已配置设置与其启动默认值。此沙箱在构建时不支持 LZ4,因此其 TOAST 默认值仍为 pglz。迁移说明。

新增的 pg_stat_lock 和 pg_stat_recovery 视图提供了运维证据。主库上的恢复视图可能没有值得关注的活动;启动备库后,也要在那里查询它。统计信息视图。

与 18 对比:对于有代表性的分析查询,在同一套原生安装中比较 SET jit = on 与 SET jit = off;使用 EXPLAIN (ANALYZE, BUFFERS),之后重置。此构建可能缺少 LLVM JIT。对于新写入的 TOAST 值,在支持的情况下比较 SET default_toast_compression = 'pglz' 与 lz4。更改该设置不会重新压缩现有行。需要转义的字符串字面量应使用显式的 E'...' 语法。

文章标题:PostgreSQL 19:带可运行浏览器示例的实操之旅

正文:


        SELECT name, setting, boot_val, source
FROM pg_settings
WHERE name IN (
  'jit',
  'log_lock_waits',
  'default_toast_compression',
  'wal_compression',
  'max_locks_per_transaction',
  'standard_conforming_strings',
  'password_expiration_warning_threshold',
  'md5_password_warnings'
)
ORDER BY name;
      

        SELECT * FROM pg_stat_lock LIMIT 5;
      

        SELECT * FROM pg_stat_recovery;
      

预期结果:显示已配置的默认值和内置默认值,以及新增的锁/恢复视图;在主库上,恢复计数器可能处于空闲状态。

哪些内容没有入选

早期 beta 版本的新闻标题并不是可靠的功能清单。SQL/PGQ 属性图已在 Beta 4 公告中被回滚。在线切换校验和同样缺席:pg_checksums 仍要求服务器以干净状态停止。

PostgreSQL 19 会拒绝不带分组元素的简写 GROUP BY ALL。当前的 SELECT 和 ALTER TABLE 语法页面也删去了此前宣传的推断式 GROUP BY ALL、分区 SPLIT/MERGE 以及 FOR PORTION OF DML。这些内容不在本次导览范围内。PostgreSQL 18 的时间约束仍然可用;它们不是 19 新增的 DML。

该卡片读取当前校验和设置,并返回编辑整理的移除标签。这些标签并非服务器能力检测。在选择应用依赖时,请以最终发布文档为准。


        SHOW data_checksums;
      

        SELECT feature, status
FROM (VALUES
  ('online checksum toggling', 'not available in PostgreSQL 19'),
  ('SQL/PGQ property graphs', 'not available in PostgreSQL 19')
) AS release_watch(feature, status);
      

预期结果:当前的校验和设置,随后是两行明确标注的编辑整理数据。

其他值得了解的变更

这些可运行的卡片涵盖了主要的 SQL 和运维新增内容。该版本的其余部分在特定工作负载中才有意义;请使用链接的文档来选择你的下一个实验。

  • 更多优化器和执行器工作:额外的 LEFT JOIN 到反连接转换、对符合条件的反连接使用 Memoize、Append/MergeAppend 下的增量排序,以及空值/布尔表达式的简化。更快的 FK 检查、行变形、排序、UTF-8 大小写折叠、选择性 NOTIFY 唤醒、SIMD COPY 解析、流式索引清理以及并行 TID 范围扫描,共同构成了性能工作的其余部分。完整的优化器/性能清单。
  • 复制控制:发布排除、序列刷新、通过外部服务器的订阅者连接参数、每订阅的接收器超时,以及保留的冲突检测信息(retain_dead_tuples,由 max_retention_duration 限制)。请评估保留开销以及新的冲突/错误计数器。CREATE SUBSCRIPTION、ALTER SUBSCRIPTION。
  • 可观测性:更多关系/函数/冲突视图上的统计重置时间戳;vacuum/analyze 发起者;autovacuum 评分;序列同步错误;slot 同步跳过原因;WAL 全页镜像字节数;扩展安装位置;以及动态共享内存分配。EXPLAIN 还会报告 Memoize 估算。统计信息、EXPLAIN。
  • 日志与认证:单独的 autoanalyze 持续时间日志、按进程的日志级别、更多 COPY/WAL 等待事件、更早的回卷警告、密码过期警告,以及 MD5 认证后的警告。RADIUS 认证被移除。服务器 SNI 使用 pg_hosts.conf;OAuth 具有自定义验证器选项以及改进的客户端钩子/CA 配置。日志、认证、libpq。
  • 更小的 SQL 便利性:error_on_null(value) 在 NULL 时抛出错误,而不是静默传递;IS JSON 接受基于支持的基础类型的域;波兰语/世界语词干提取器;PL/Python 事件触发器;CREATE SCHEMA 内的正向 FK 引用;CHECKPOINT 选项;聚合支持函数;以及权限变更的 GRANTED BY。函数新增、CHECKPOINT、GRANT。
  • psql 19:使用 %S 显示搜索路径,使用 %i 显示备库状态,自定义 true/false 的显示方式,查看 publications/subscriptions/statistics 上的注释,并使用显式的 service 文件。客户端版本与服务器版本各自独立,需分别关注。试试 \pset display_true yes 和 \pset display_false no,然后执行 SELECT true, false;。psql 参考。
  • 运维与扩展:导出/恢复扩展的规划器统计信息;vacuumdb --dry-run;分区表分析改进;备份验证/WAL 检查中支持 tar-WAL;大对象/表空间升级改进;pgbench --continue-on-error;pg_stat_statements 中的通用/自定义计划计数器;postgres_fdw 中的外部统计信息导入;以及 pg_buffercache 中的缓冲区映射/脏页标记工具。pg_dump、vacuumdb、postgres_fdw、pg_stat_statements、pg_buffercache。

对于扩展作者,有新的规划器钩子、索引处理程序/API 变更、C11 要求、额外的平台支持,以及共享内存注册改进。参见源代码变更。

The translation is machine-generated and individual terms may be off. Nothing is rewritten or cut; the original is authoritative.

Published by Hacker News
This site only summarises and aggregates; copyright stays with the original author. Headlines and summaries follow the source — where they differ, the original wins.
Read the original ↗