database

PostgreSQL UPSERT、冲突处理与批量写入优化

深入解析 PostgreSQL INSERT ON CONFLICT 语义与冲突处理策略。涵盖 DO UPDATE 与 DO NOTHING 的行为差异、EXCLUDED 虚拟表的用法、冲突键与唯一约束的匹配规则、RETURNING 子句获取受影响行、UNNEST 批量 INSERT 技巧、COPY 命令与批量 INSERT 的性能对比，以及 toasted 值在冲突处理中的注意事项。

Leeting Yan 7 分钟阅读 3046 字

在高并发写入场景中，“存在则更新、不存在则插入"是最常见的需求之一。PostgreSQL 9.5+ 引入的 INSERT ON CONFLICT 提供了原子级的 UPSERT 能力，避免了应用层先 SELECT 再 INSERT OR UPDATE 的竞态窗口。但如果写错了冲突键或忽略了 RETURNING 子句，轻则执行计划退化，重则丢失本应被触发的更新。

核心认知：ON CONFLICT 是原子操作，不需要事务保护；但必须保证冲突键上有唯一约束，否则 PostgreSQL 会直接报错而非进入冲突处理分支。

一、INSERT ON CONFLICT 基础

1.1 DO NOTHING：存在即跳过

-- 使用唯一索引列作为冲突检测目标
INSERT INTO products (sku, name, price, stock)
VALUES ('SKU-001', '无线耳机', 299.00, 100)
ON CONFLICT (sku) DO NOTHING;

若 sku 已存在，该行被静默跳过。DO NOTHING 等价于 “尽最大努力插入”。

1.2 DO UPDATE：存在则更新

-- 冲突时更新价格与库存，但保持不变量
INSERT INTO products (sku, name, price, stock)
VALUES ('SKU-001', '无线耳机', 259.00, 200)
ON CONFLICT (sku) DO UPDATE SET
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock;
关键要素说明
ON CONFLICT (sku)声明冲突检测列，必须匹配唯一约束/索引
EXCLUDED虚拟表，代表本次 INSERT 尝试写入的该行值
products.stock + EXCLUDED.stock可引用原行与 EXCLUDED 新行做计算

1.3 复合唯一键的冲突检测

-- 冲突目标必须是唯一约束或索引的列集合
CREATE UNIQUE INDEX idx_warehouse_product
ON inventory (warehouse_id, product_id);

INSERT INTO inventory (warehouse_id, product_id, qty)
VALUES (1, 42, 100)
ON CONFLICT (warehouse_id, product_id) DO UPDATE SET
    qty = inventory.qty + EXCLUDED.qty;

规则：ON CONFLICT 的列集必须与某个唯一索引/约束的列集精确匹配（顺序无关），否则报错 there is no unique or exclusion constraint matching the ON CONFLICT specification。

二、EXCLUDED 虚拟表与计算逻辑

2.1 EXCLUDED 的可用字段

-- EXCLUDED 包含 INSERT 语句中 VALUES 声明的全部列
INSERT INTO users (id, email, display_name, last_login)
VALUES (1, 'alice@example.com', 'Alice', NOW())
ON CONFLICT (id) DO UPDATE SET
    display_name = EXCLUDED.display_name,
    last_login   = EXCLUDED.last_login;

EXCLUDED.id、EXCLUDED.email 均可引用，即使它们不是冲突目标的一部分。

2.2 条件更新（WHERE）

-- 仅在 EXCLUDED 的新库存大于现有库存时才更新
INSERT INTO products (sku, name, price, stock)
VALUES ('SKU-001', '无线耳机', 259.00, 150)
ON CONFLICT (sku) DO UPDATE SET
    price = EXCLUDED.price,
    stock = EXCLUDED.stock
WHERE products.stock < EXCLUDED.stock;

WHERE 条件过滤冲突行：不符合条件的冲突行什么都不会发生（不报错、不更新）。

2.3 多行批量 UPSERT

INSERT INTO products (sku, name, price, stock)
VALUES
    ('SKU-001', '无线耳机', 259.00, 200),
    ('SKU-002', '机械键盘', 599.00, 50),
    ('SKU-003', '显示器支架', 199.00, 80)
ON CONFLICT (sku) DO UPDATE SET
    name  = EXCLUDED.name,
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock;

多行 VALUES 中每一行独立参与冲突检测与更新。部分行冲突、部分行不冲突，最终结果一致。

三、RETURNING 返回受影响行

3.1 获取插入或更新后的数据

INSERT INTO order_items (order_id, product_id, qty, unit_price)
VALUES (1001, 42, 2, 299.00)
ON CONFLICT (order_id, product_id) DO UPDATE SET
    qty = order_items.qty + EXCLUDED.qty
RETURNING *;

RETURNING 返回的是最终写入表中的行版本。对于 DO UPDATE，返回的是更新后的行；对于 DO NOTHING，返回不冲突的插入行（冲突行不返回）。

3.2 区分 INSERT 与 UPDATE

需要应用层判断哪些行是新增、哪些是更新时，可用以下技巧：

INSERT INTO products (sku, price, stock)
VALUES ('SKU-001', 259.00, 200)
ON CONFLICT (sku) DO UPDATE SET
    price = EXCLUDED.price,
    stock = EXCLUDED.stock,
    updated_at = NOW()
RETURNING sku, price, stock, xmax::text;
-- xmax = 0 表示该行是刚 INSERT 的；非 0 表示更新

更推荐的做法是在表上加一个不可变的 created_at 字段，通过判断 created_at 是否变化来区分。或者使用 PostgreSQL 14+ 的 xmax 映射：

INSERT INTO products (sku, price, stock)
VALUES ('SKU-001', 259.00, 200)
ON CONFLICT (sku) DO UPDATE SET
    price = EXCLUDED.price,
    stock = EXCLUDED.stock
RETURNING sku, price, stock,
          (xmax = 0) AS is_inserted;
-- is_inserted = true: INSERT；false: UPDATE

四、UNNEST 批量写入

4.1 UNNEST 实现高效批量 INSERT

-- 输入数组：Java/Python 等把列表转成 SQL 数组参数
INSERT INTO events (event_id, event_type, payload, created_at)
SELECT * FROM UNNEST(
    ARRAY[1001, 1002, 1003, 1004],
    ARRAY['click', 'view', 'purchase', 'click'],
    ARRAY['{"a":1}'::jsonb, '{"a":2}'::jsonb, '{"a":3}'::jsonb, '{"a":4}'::jsonb],
    ARRAY[NOW(), NOW(), NOW(), NOW()]
) AS t(event_id, event_type, payload, created_at);

UNNEST 的优势：

方式批大小 1000Parse/Plan 次数
单行 INSERT（循环）1000 次网络往返1000 次
多行 VALUES（1000 行）1 次1 次
UNNEST1 次1 次（更节省协议解析）

UNNEST 在 JDBC PreparedStatement executeBatch 场景下尤为有效，因为它天然适配数组参数绑定。

4.2 UNNEST 与 UPSERT 结合

INSERT INTO inventory (warehouse_id, product_id, qty)
SELECT * FROM UNNEST(
    ARRAY[1, 1, 2],
    ARRAY[42, 43, 44],
    ARRAY[10, 20, 30]
) AS t(warehouse_id, product_id, qty)
ON CONFLICT (warehouse_id, product_id) DO UPDATE SET
    qty = inventory.qty + EXCLUDED.qty
RETURNING warehouse_id, product_id, qty;

五、COPY 命令 vs 批量 INSERT

5.1 COPY 的极致写入速度

-- 从 CSV 文件高速导入
COPY products (sku, name, price, stock)
FROM '/data/products.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

COPY 完全跳过解析器、规划器、执行器的常规路径，直接写入堆页，是 PostgreSQL 最快的数据加载方式。

对比项COPYINSERTUNNEST
速度最快中等快
冲突处理不支持 ON CONFLICT支持支持
应用集成需文件/标准输入最灵活数组参数
并行单进程可并发可并发
WAL 生成批量优化逐行逐行

5.2 分批 COPY 解决冲突

当数据可能冲突且无法使用 ON CONFLICT 时，常见策略：先 COPY 到临时表，再 INSERT ON CONFLICT：

CREATE TEMP TABLE tmp_products (sku text, name text, price numeric, stock int);

COPY tmp_products FROM '/data/update.csv' WITH (FORMAT csv, HEADER true);

INSERT INTO products (sku, name, price, stock)
SELECT sku, name, price, stock FROM tmp_products
ON CONFLICT (sku) DO UPDATE SET
    name  = EXCLUDED.name,
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock;

六、toasted 值与冲突处理的陷阱

6.1 TOAST 是什么

当某列值超过约 2KB 时，PostgreSQL 将其压缩/切块后存到单独的 TOAST 表中，主表只保留指针。大文本（text、jsonb、bytea）是 TOAST 的典型目标。

6.2 冲突更新时反复 TOAST

-- 问题：每次冲突时 products.description（大文本）都会被重写
INSERT INTO products (sku, name, description, stock)
VALUES ('SKU-001', '耳机', repeat('x', 10000), 100)
ON CONFLICT (sku) DO UPDATE SET
    stock = products.stock + EXCLUDED.stock,
    description = products.description;  -- 即使没变也重写了

如果 description 是 TOAST 目标列（大文本），即使赋值为原值，PostgreSQL 仍可能触发 TOAST 重写。

6.3 避免 TOAST 重写

-- 优化：仅在必要时更新大字段
INSERT INTO products (sku, name, description, stock)
VALUES ('SKU-001', '耳机', repeat('x', 10000), 100)
ON CONFLICT (sku) DO UPDATE SET
    stock = products.stock + EXCLUDED.stock,
    description = COALESCE(EXCLUDED.description, products.description)
WHERE products.stock IS DISTINCT FROM products.stock + EXCLUDED.stock
   OR products.description IS DISTINCT FROM COALESCE(EXCLUDED.description, products.description);

但更简洁的做法是：在应用层保证 INSERT 数据中不携带不变的大字段，只对真正变化的列触发更新。

常见问题（FAQ）

ON CONFLICT 的列必须和索引完全一致吗？

是的。ON CONFLICT 指定的列集必须精确匹配某个 UNIQUE 约束或索引的列集（列名和列数一致）。你可以使用 ON CONFLICT ON CONSTRAINT constraint_name 显式指定约束名来避免列集匹配问题。

DO UPDATE 时如何只更新变化的列？

用 WHERE 条件过滤：

ON CONFLICT (sku) DO UPDATE SET
    price = EXCLUDED.price,
    updated_at = NOW()
WHERE products.price IS DISTINCT FROM EXCLUDED.price;

未满足条件的冲突行不会被更新，避免了无意义的写放大。

COPY 可以 ON CONFLICT 吗？

不可以。COPY 不经过 INSERT 路径，无法使用 ON CONFLICT。变通方案是先 COPY 到临时表，再 INSERT INTO … SELECT … ON CONFLICT。

UNNEST 比多行 VALUES 好在哪里？

UNNEST 配合 PreparedStatement 的数组参数绑定，批量数据不需要拼接到 SQL 字符串中，避免了 SQL 注入风险、节省了解析器开销，且批大小不受 SQL 长度限制。

大批量 UPSERT 会导致锁等待吗？

会。INSERT ON CONFLICT 在冲突行上持有 ROW EXCLUSIVE 及后续的 ROW UPDATE 锁。频繁更新同一行会造成锁竞争。对极高频的冲突行，建议先在应用层做写缓冲/合并。

相关阅读

延伸阅读

完整示例（一键复制）

-- ========== 1. 基础表与唯一约束 ==========
CREATE TABLE products (
    sku        text PRIMARY KEY,
    name       text NOT NULL,
    price      numeric(10,2) NOT NULL,
    stock      int NOT NULL DEFAULT 0,
    updated_at timestamptz DEFAULT NOW()
);

CREATE TABLE inventory (
    warehouse_id int,
    product_id   int,
    qty          int NOT NULL,
    PRIMARY KEY (warehouse_id, product_id)
);

-- ========== 2. 基础 UPSERT（DO UPDATE） ==========
INSERT INTO products (sku, name, price, stock)
VALUES ('SKU-001', '无线耳机', 299.00, 100)
ON CONFLICT (sku) DO UPDATE SET
    name  = EXCLUDED.name,
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock,
    updated_at = NOW()
RETURNING *;

-- ========== 3. 条件更新（避免不必要的写） ==========
INSERT INTO products (sku, name, price, stock)
VALUES ('SKU-001', '无线耳机', 259.00, 50)
ON CONFLICT (sku) DO UPDATE SET
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock
WHERE products.price IS DISTINCT FROM EXCLUDED.price
   OR products.stock IS DISTINCT FROM products.stock + EXCLUDED.stock;

-- ========== 4. 多行批量 UPSERT ==========
INSERT INTO products (sku, name, price, stock)
VALUES
    ('SKU-001', '无线耳机', 259.00, 200),
    ('SKU-002', '机械键盘', 599.00, 50),
    ('SKU-003', '显示器支架', 199.00, 80)
ON CONFLICT (sku) DO UPDATE SET
    name  = EXCLUDED.name,
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock;

-- ========== 5. USING UNNEST 批量 INSERT ==========
INSERT INTO events (event_id, event_type, payload, created_at)
SELECT * FROM UNNEST(
    ARRAY[1001, 1002, 1003],
    ARRAY['click', 'view', 'purchase'],
    ARRAY['{"a":1}'::jsonb, '{"a":2}'::jsonb, '{"a":3}'::jsonb],
    ARRAY[NOW(), NOW(), NOW()]
) AS t(event_id, event_type, payload, created_at);

-- ========== 6. USING UNNEST + UPSERT ==========
INSERT INTO inventory (warehouse_id, product_id, qty)
SELECT * FROM UNNEST(
    ARRAY[1, 1, 2],
    ARRAY[42, 43, 44],
    ARRAY[10, 20, 30]
) AS t(warehouse_id, product_id, qty)
ON CONFLICT (warehouse_id, product_id) DO UPDATE SET
    qty = inventory.qty + EXCLUDED.qty
RETURNING warehouse_id, product_id, qty;

-- ========== 7. COPY 到临时表再 UPSERT ==========
CREATE TEMP TABLE tmp_products (sku text, name text, price numeric, stock int);
-- COPY tmp_products FROM '/data/products.csv' WITH (FORMAT csv, HEADER true);

-- 执行 UPSERT
INSERT INTO products (sku, name, price, stock)
SELECT sku, name, price, stock FROM tmp_products
ON CONFLICT (sku) DO UPDATE SET
    name  = EXCLUDED.name,
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock;

-- ========== 8. 区分 INSERT vs UPDATE（xmax 技巧） ==========
INSERT INTO products (sku, price, stock)
VALUES ('SKU-001', 259.00, 200)
ON CONFLICT (sku) DO UPDATE SET
    price = EXCLUDED.price,
    stock = EXCLUDED.stock
RETURNING sku, price, stock, (xmax = 0) AS is_inserted;

继续阅读

探索更多技术文章

浏览归档，发现更多关于系统设计、工具链和工程实践的内容。

全部文章 返回首页

「database」更多文章

  1. Supabase 平台与 PostgreSQL 边缘函数实践
  2. PostgreSQL 事件触发器与审计日志实现
  3. Kubernetes 上 PostgreSQL 运维与 CloudNativePG 实战