PostgreSQL 17：面向应用程序开发人员的新功能

PostgreSQL 17 带来的功能集最终使几个长期要求的 SQL 功能成为主流。虽然数据库管理员将重点关注真空改进和逻辑复制增强，但应用程序开发人员也获得了同样引人注目的附加功能：用于将 JSON 作为关系数据查询的 JSON_TABLE、显着的 MERGE 语句改进、新的聚合函数以及影响每个查询的性能提升。

本指南专门关注改变您编写应用程序代码方式的功能 - SQL 模式、ORM 集成点以及在 PostgreSQL 17 中变得可能或更好的架构决策。

要点

• JSON_TABLE 现在是标准的 — 查询 JSON 数组，就好像它们是没有自定义函数的表一样
• MERGE 语句获得 RETURNING 子句 — 无需第二次查询即可获取受影响的行
• ANY_VALUE() 聚合使分组查询更简单，无需完整的 GROUP BY 列表
• 增量排序改进降低了有序数据的查询计划成本
• COPY 命令支持 ON_ERROR 跳过坏行而不是完全失败
• pg_stat_io 视图为每个关系提供精确的 I/O 统计信息以进行性能分析
• 逻辑复制现在支持序列，缩小了主动-主动设置的主要差距
• VACUUM 改进减少了膨胀积累——减少了紧急自动清理

---

JSON_TABLE：查询 JSON 作为关系数据

对于应用程序开发人员来说最有影响力的 SQL 功能是 JSON_TABLE。应用程序经常将数组或嵌套对象存储在 JSONB 列中。在 PostgreSQL 17 之前，查询这些数据需要使用横向连接进行笨拙的 jsonb_array_elements 函数调用。

考虑一个存储订单行项目的 JSONB 列：

-- orders.metadata column:
-- {
--   "items": [
--     {"sku": "PROD-001", "qty": 2, "price": 49.99},
--     {"sku": "PROD-002", "qty": 1, "price": 129.99}
--   ]
-- }

在 PostgreSQL 17 之前（使用 jsonb_array_elements）：

SELECT
  o.id,
  o.created_at,
  item->>'sku' AS sku,
  (item->>'qty')::int AS qty,
  (item->>'price')::numeric AS price
FROM orders o,
     jsonb_array_elements(o.metadata->'items') AS item
WHERE o.organization_id = $1;

带有 JSON_TABLE 的 PostgreSQL 17：

SELECT
  o.id,
  o.created_at,
  items.sku,
  items.qty,
  items.price
FROM orders o,
     JSON_TABLE(
       o.metadata,
       '$.items[*]'
       COLUMNS (
         sku VARCHAR PATH '$.sku',
         qty INTEGER PATH '$.qty',
         price NUMERIC PATH '$.price'
       )
     ) AS items
WHERE o.organization_id = $1;

JSON_TABLE 版本更具可读性，提供显式列类型，并参与查询规划器的连接优化。它还使用 DEFAULT NULL ON EMPTY 和 DEFAULT 0 ON ERROR 列选项优雅地处理丢失的键。

更复杂的示例 - 带有错误处理的嵌套 JSON：

SELECT
  products.id,
  specs.weight_kg,
  specs.color,
  specs.dimensions_cm
FROM products,
     JSON_TABLE(
       products.specifications,
       '$.specs'
       COLUMNS (
         weight_kg NUMERIC PATH '$.weight'
           DEFAULT 0 ON EMPTY DEFAULT 0 ON ERROR,
         color VARCHAR PATH '$.color'
           DEFAULT 'unknown' ON EMPTY,
         dimensions_cm VARCHAR PATH '$.dimensions'
           DEFAULT NULL ON EMPTY
       )
     ) AS specs
WHERE products.organization_id = $1;

---

合并并返回

MERGE 语句（在 PostgreSQL 15 中引入）允许在单个语句中执行条件插入或更新逻辑。 PostgreSQL 17 添加了 RETURNING，这在以前是不可能的：

-- Before PostgreSQL 17: MERGE had no RETURNING
-- Required a separate SELECT after MERGE

-- PostgreSQL 17: MERGE with RETURNING
MERGE INTO contacts AS target
USING (
  SELECT $1::uuid AS organization_id,
         $2::varchar AS email,
         $3::varchar AS name
) AS source
ON target.organization_id = source.organization_id
   AND target.email = source.email
WHEN MATCHED THEN
  UPDATE SET
    name = source.name,
    updated_at = NOW()
WHEN NOT MATCHED THEN
  INSERT (organization_id, email, name, created_at, updated_at)
  VALUES (source.organization_id, source.email, source.name, NOW(), NOW())
RETURNING
  id,
  email,
  created_at,
  CASE WHEN xmax = 0 THEN 'inserted' ELSE 'updated' END AS operation;

xmax = 0 技巧识别该行是被插入（xmax = 0）还是被更新（xmax != 0）。此模式对于幂等数据导入管道至关重要，您需要知道实际更改的内容。

在 Drizzle 中使用 MERGE（通过原始 SQL，直到 Drizzle 添加本机 MERGE 支持）：

import { sql } from 'drizzle-orm';
import { db } from '@ecosire/db';

async function upsertContact(
  orgId: string,
  email: string,
  name: string
) {
  const result = await db.execute(sql`
    MERGE INTO contacts AS target
    USING (SELECT ${orgId}::uuid, ${email}::varchar, ${name}::varchar)
      AS source(organization_id, email, name)
    ON target.organization_id = source.organization_id
       AND target.email = source.email
    WHEN MATCHED THEN
      UPDATE SET name = source.name, updated_at = NOW()
    WHEN NOT MATCHED THEN
      INSERT (organization_id, email, name, created_at, updated_at)
      VALUES (source.organization_id, source.email, source.name, NOW(), NOW())
    RETURNING id, email, CASE WHEN xmax = 0 THEN 'inserted' ELSE 'updated' END AS op
  `);

  return result.rows[0];
}

---

ANY_VALUE() 聚合

PostgreSQL 中常见的 GROUP BY 挫折：您想要按列进行分组，但又从同一行中选择一个非聚合列。以前，您必须将其添加到 GROUP BY（更改分组语义）或使用 MAX() 作为解决方法。

-- Before: Awkward workaround
SELECT
  department_id,
  MAX(employee_name) AS any_employee_name, -- Wrong semantic
  COUNT(*) AS employee_count,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- PostgreSQL 17: ANY_VALUE() expresses intent clearly
SELECT
  department_id,
  ANY_VALUE(employee_name) AS sample_employee, -- Pick any one
  COUNT(*) AS employee_count,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

ANY_VALUE() 在语义上是正确的 - 它明确表示“我不在乎组中的哪个值，只需给我一个。”这对于诊断查询、调试以及任何代表值都可接受的情况非常有用。

---

增量排序改进

PostgreSQL 17 显着提高了增量排序性能。这会影响非前导索引列上使用 ORDER BY 的任何查询：

-- This benefits from incremental sorting in PostgreSQL 17
SELECT *
FROM orders
WHERE organization_id = $1    -- Index on (organization_id, created_at)
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- Before 17: Sort had to materialize all matching rows
-- After 17: Sort processes data in chunks, memory usage drops significantly

对于分页量大的应用程序（仪表板、列表视图、报告），这种改进可以减少内存压力并缩短响应时间，而无需更改任何查询。

---

复制时出现 ON_ERROR

数据导入管道经常遇到格式错误的行。在 PostgreSQL 17 之前，单个坏行会使整个 COPY 操作失败。现在您可以跳过坏行：

-- Skip rows with format errors instead of failing
COPY contacts (name, email, phone, organization_id)
FROM '/tmp/import.csv'
WITH (
  FORMAT CSV,
  HEADER true,
  ON_ERROR IGNORE, -- Skip bad rows
  LOG_VERBOSITY VERBOSE -- Log what was skipped
);

LOG_VERBOSITY VERBOSE 选项将每个跳过的行记录到 PostgreSQL 日志中，以便您可以审核被拒绝的内容。这使得批量导入管道变得安全——第 47,832 行的格式错误的行不再中止百万行导入。

对于通过 ORM 进行编程导入，您可以通过 node-postgres 驱动程序使用 PostgreSQL COPY 协议：

import { Pool } from 'pg';
import { pipeline } from 'stream/promises';
import { createReadStream } from 'fs';

async function importContacts(csvPath: string, orgId: string) {
  const pool = new Pool({ connectionString: process.env.DATABASE_URL });
  const client = await pool.connect();

  try {
    const copyStream = client.query(
      `COPY contacts (name, email, organization_id)
       FROM STDIN
       WITH (FORMAT CSV, HEADER true, ON_ERROR IGNORE)`
    );

    await pipeline(createReadStream(csvPath), copyStream);
    console.log(`Imported rows: ${copyStream.rowCount}`);
  } finally {
    client.release();
  }
}

---

pg_stat_io：I/O 性能分析

PostgreSQL 17 添加了 pg_stat_io，这是一个按关系、块类型和上下文分解 I/O 统计信息的视图。这对于诊断由于磁盘读取过多而导致的慢查询非常有价值：

-- Top tables by cache misses (reads going to disk)
SELECT
  object,
  reads,
  hits,
  ROUND(hits::numeric / NULLIF(hits + reads, 0) * 100, 2) AS hit_rate_pct,
  evictions,
  reuses
FROM pg_stat_io
WHERE backend_type = 'client backend'
  AND object = 'relation'
  AND reads > 0
ORDER BY reads DESC
LIMIT 20;

-- Identify tables causing the most buffer evictions
SELECT
  schemaname,
  tablename,
  seq_scan,
  seq_tup_read,
  idx_scan,
  idx_tup_fetch,
  n_dead_tup,
  n_live_tup
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 20;

对于具有高顺序扫描计数和低缓存命中率的表，您需要索引或更大的 shared_buffers 配置。

---

序列的逻辑复制

PostgreSQL 17 添加了对序列的逻辑复制支持。这弥补了主动-主动复制设置的一个关键差距——以前，备用服务器上的序列存在分歧，导致在升级备用服务器时导致重复的键冲突。

对于应用程序开发人员来说，这意味着：

-- Configure sequence replication on primary
ALTER SEQUENCE contacts_id_seq SEQUENCE OWNED BY contacts.id;

-- Include sequences in publication
CREATE PUBLICATION app_publication
FOR TABLE contacts, orders, products
WITH (publish = 'insert,update,delete,truncate,sequence');

-- Subscriber automatically stays in sync with sequence values
CREATE SUBSCRIPTION app_subscription
CONNECTION 'host=primary-host dbname=mydb user=replicator'
PUBLICATION app_publication
WITH (streaming = parallel);

---

性能基准

PostgreSQL 17 的性能改进在实际应用中是可以衡量的：

这些基准来自 EC2 r6g.xlarge 实例上的真实工作负载。结果因数据分布和查询模式而异。

---

升级到 PostgreSQL 17

对于基于 Docker 的开发环境，请更新您的 compose 文件：

# docker-compose.dev.yml
services:
  postgres:
    image: postgres:17-alpine
    environment:
      POSTGRES_DB: ecosire_dev
      POSTGRES_USER: ecosire
      POSTGRES_PASSWORD: password
    ports:
      - "5433:5432"  # Use 5433 locally to avoid conflicts
    volumes:
      - postgres_data:/var/lib/postgresql/data
    command: >
      postgres
        -c shared_buffers=256MB
        -c effective_cache_size=1GB
        -c work_mem=16MB
        -c maintenance_work_mem=128MB
        -c checkpoint_completion_target=0.9
        -c wal_buffers=16MB
        -c max_connections=100

对于生产升级，请使用 pg_upgrade：

# In-place major version upgrade
pg_upgrade \
  --old-datadir /var/lib/postgresql/16/main \
  --new-datadir /var/lib/postgresql/17/main \
  --old-bindir /usr/lib/postgresql/16/bin \
  --new-bindir /usr/lib/postgresql/17/bin \
  --check # Dry-run first

始终首先运行 --check。对于生产中的零停机升级，请使用逻辑复制迁移到新的 PostgreSQL 17 实例，同时旧实例保持活动状态。

---

常见问题

JSON_TABLE 比 jsonb_array_elements 更快吗？

对于等效查询，JSON_TABLE 通常执行与 jsonb_array_elements 类似的操作，但它更有效地参与查询规划器的优化过程。真正的好处是可读性、可维护性和标准 SQL 合规性——您的查询看起来像 SQL，而不是函数调用汤。对于大规模的性能关​​键型 JSON 查询，请确保您的 JSONB 列具有适当的 GIN 索引。

我应该立即从 PostgreSQL 16 升级到 17 吗？

Wait 3-6 months after a major PostgreSQL release before upgrading production systems.这为您的托管提供商提供了时间来支持新版本、您依赖更新的扩展以及修补任何初始错误。对于新项目或开发环境，请立即从 PostgreSQL 17 开始。使用 pg_upgrade 从 16 到 17 的升级路径非常简单。

如何启用新的 pg_stat_io 视图？

pg_stat_io 在 PostgreSQL 17 中自动启用 - 无需配置。在任何 PostgreSQL 17 数据库中使用 SELECT * FROM pg_stat_io 进行查询。该视图是自上次统计重置 (pg_stat_reset()) 以来累积的，因此在进行更改之前建立基线并在之后进行比较。

我可以将 MERGE 与 Drizzle ORM 结合使用吗？

Drizzle 目前没有原生 MERGE 支持 - 使用 db.execute(sql\MERGE ...`)和参数化值。切勿将sql.raw()` 用于用户提供的数据。请观看 Drizzle GitHub 问题以了解 MERGE 支持跟踪，因为这是一项普遍要求的功能。

我应该为新项目定位哪个 PostgreSQL 版本？

所有新项目都以 PostgreSQL 17 为目标。它具有最佳性能、最新的 SQL 功能，并将在 2029 年 11 月之前收到安全更新。避免以 PostgreSQL 14 或更早版本为目标 — 它们将在未来 2 年内达到生命周期终点，无论如何您都需要升级。

---

后续步骤

PostgreSQL 17 的新功能 — 特别是 JSON_TABLE、MERGE with RETURNING 以及增量排序改进 — 开启了以前不可行的架构模式。 ECOSIRE 在生产环境中针对所有数据库工作负载运行 PostgreSQL 17，在超过 65 个表架构中利用这些改进。

无论您需要数据库架构咨询、查询优化还是使用现代工具进行全栈开发，探索我们的工程服务。