PHP小码农

Appearance

批量操作优化 ​

数据迁移、定时任务、报表回填里最容易被忽视的性能黑洞，是逐行往返：一条 INSERT 一次网络交互、一次事务提交、一次 WAL 刷盘，写 100 万行就要重复 100 万次。本节系统对比批量写入手段（多值 INSERT、COPY、INSERT ... SELECT、UNNEST、execute_values），并覆盖批量 UPDATE 分批策略、UPSERT、RETURNING、进度观察与常见事故。

前置知识

阅读本节前，建议先了解：分页优化（键集分页）

基础概念 ​

批量优化的核心思路只有一条：把 N 次往返合并成 1 次。每次往返都包含网络、解析、规划、事务提交、WAL 刷盘等固定成本，把这些固定成本摊薄到整批数据上，性能就能提升一两个数量级。

概念术语说明
批量插入Bulk Insert一次语句插入多行，摊薄固定成本
预写日志WAL (Write-Ahead Log)每次提交都要刷盘，是逐行写入的主要瓶颈
同步提交synchronous_commit控制提交是否等待 WAL 落盘
服务器端复制Server-side COPY数据库进程直接读写文件，最快
客户端复制\copy / STDIN数据经客户端转发，权限要求低
冻结FREEZE导入时直接标记行为可见，跳过后续 VACUUM
非日志表UNLOGGED Table不写 WAL，写入极快，崩溃后数据丢失
冲突处理UPSERTON CONFLICT DO UPDATE，插入或更新
返回子句RETURNING返回被写入的行，避免二次查询
试运行Dry Run先执行再回滚，验证影响行数

批量手段的代价对照（100 万行，单机 SSD，示意性数据）：

方式往返次数事务数相对耗时适用场景
单行 INSERT × 100 万100 万100 万1.000仅交互式单条写入
多值 INSERT（每批 1000 行）100010000.024应用层通用批量
INSERT ... SELECT110.008库内表间搬运
COPY（默认）110.006外部数据导入
COPY + FREEZE + synchronous_commit = off110.004新表全量导入
text
-- 固定成本的构成（每次往返都要付）
网络往返       约 0.1 ~ 1 ms
语句解析与规划  约 0.05 ~ 0.5 ms
事务提交 + WAL 刷盘  约 1 ~ 10 ms（synchronous_commit = on 时）
-----------------------------------------------------
单行 INSERT 每条至少付出 1 ms 量级，100 万条即 1000 秒
批量写入把上述成本除以批大小，收益呈数量级

语法与用法 ​

先把「逐行」和「批量」两种写法放在一起对比，直观感受差异：

sql
-- 写法一：逐行插入（每条一条语句，最慢）
INSERT INTO metrics (device_id, metric, value, recorded_at)
VALUES (1, 'cpu', 12.5, now());
INSERT INTO metrics (device_id, metric, value, recorded_at)
VALUES (2, 'cpu', 31.2, now());
-- ... 重复 100 万次

-- 写法二：多值批量插入（一条语句插入多行）
INSERT INTO metrics (device_id, metric, value, recorded_at) VALUES
  (1, 'cpu', 12.5, now()),
  (2, 'cpu', 31.2, now()),
  (3, 'cpu', 18.7, now());

-- 写法三：COPY 从标准输入导入（导入之王）
COPY metrics (device_id, metric, value, recorded_at) FROM STDIN
  WITH (FORMAT csv, HEADER true);

准备本节贯穿使用的示例表（在 demo 库中执行）：

sql
-- 设备指标表：用于演示批量导入与批量更新
CREATE TABLE metrics (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  device_id   INTEGER        NOT NULL,
  metric      TEXT           NOT NULL,
  value       NUMERIC(12, 3) NOT NULL,
  recorded_at TIMESTAMPTZ    NOT NULL
);

-- 设备维度表：演示 UPSERT 与 MERGE
CREATE TABLE devices (
  device_id   INTEGER PRIMARY KEY,
  device_name TEXT    NOT NULL,
  region      TEXT    NOT NULL,
  is_active   BOOLEAN NOT NULL DEFAULT true,
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 造一份待导入的 CSV（100 万行）
-- 实际环境中由 ETL 或应用导出，这里用 SQL 生成到客户端临时文件
\copy (SELECT 1 + (i % 5000) AS device_id, 'cpu' AS metric, round((random() * 100)::numeric, 3) AS value, TIMESTAMPTZ '2026-01-01 00:00:00+08' + (i * INTERVAL '1 second') AS recorded_at FROM generate_series(1, 1000000) AS i) TO '/tmp/metrics.csv' WITH (FORMAT csv, HEADER true)
bash
# 生成的文件大小参考
ls -lh /tmp/metrics.csv
-rw-r--r--  1 ch  staff  48M  metrics.csv

最小可运行对照示例（同样是 100 万行，三种写入方式）：

sql
-- 方式一：多值 INSERT，每批 1000 行（由应用层循环 1000 次）
INSERT INTO metrics (device_id, metric, value, recorded_at) VALUES
  (1, 'cpu', 12.5, now()),
  (2, 'cpu', 31.2, now())
  -- ... 每批 1000 组
;

-- 方式二：库内 INSERT ... SELECT 直接生成并插入（一次往返）
INSERT INTO metrics (device_id, metric, value, recorded_at)
SELECT
  1 + (i % 5000),
  'cpu',
  round((random() * 100)::numeric, 3),
  TIMESTAMPTZ '2026-01-01 00:00:00+08' + (i * INTERVAL '1 second')
FROM generate_series(1, 1000000) AS i;

-- 方式三：COPY FROM STDIN 从文件导入（外部数据首选）
COPY metrics (device_id, metric, value, recorded_at) FROM '/tmp/metrics.csv'
  WITH (FORMAT csv, HEADER true);
text
-- 示意性实测（具体数值随环境变化，请以本地实测为准）
方式                          耗时        相对倍数
多值 INSERT（批 1000）        9800 ms     1.000
INSERT ... SELECT             3200 ms     0.327
COPY                          2400 ms     0.245
COPY（FREEZE + 关同步提交）    1650 ms     0.168

详细说明 ​

逐行 INSERT 的代价从哪来 ​

逐行插入慢不是因为「写数据」本身慢，而是因为每一行都要重复付出解析、规划、事务、WAL 刷盘四项固定成本。synchronous_commit = on（默认）时，每次提交都必须确认 WAL 已落盘。

sql
-- 查看当前提交模式
SHOW synchronous_commit;
text
 synchronous_commit
--------------------
 on
(1 row)
sql
-- 单行插入 1 万行的耗时基线（示意）
\timing on
DO $$
BEGIN
  FOR i IN 1..10000 LOOP
    INSERT INTO metrics (device_id, metric, value, recorded_at)
    VALUES (1 + (i % 5000), 'cpu', i::numeric, now());
  END LOOP;
END $$;
text
DO
Time: 8420.117 ms
-- 1 万行耗 8.4 秒，单行约 0.84 ms，其中绝大部分是提交与 WAL 成本
sql
-- 批量写入同样 1 万行
INSERT INTO metrics (device_id, metric, value, recorded_at)
SELECT 1 + (i % 5000), 'cpu', i::numeric, now()
FROM generate_series(1, 10000) AS i;
text
INSERT 0 10000
Time: 28.442 ms
-- 从 8420 ms 降到 28 ms，约 296× 提升

多值 INSERT ​

多值 INSERT 是最容易落地的批量手段，应用层把 N 行拼成一条语句即可。但批大小不是越大越好：语句过大时解析时间、内存占用、参数数量上限都会成为问题。

sql
-- 推荐：每批 500 ~ 1000 行
INSERT INTO metrics (device_id, metric, value, recorded_at) VALUES
  (1, 'cpu', 12.500, TIMESTAMPTZ '2026-01-01 00:00:00+08'),
  (2, 'cpu', 31.200, TIMESTAMPTZ '2026-01-01 00:00:01+08'),
  (3, 'cpu', 18.700, TIMESTAMPTZ '2026-01-01 00:00:02+08')
  -- 继续追加到 1000 行左右
;
text
-- 不同批大小的相对耗时（1 万行总量，示意）
每批行数   语句数   相对耗时
1          10000    1.00
10         1000     0.22
100        100      0.06
1000       10       0.035
10000      1        0.030   -- 再往上收益趋平，但内存与解析成本上升
批大小优点风险
1~10实现简单、错误定位精确性能收益有限
100~1000性能与可维护性平衡推荐区间
5000~10000单批性能最优内存占用高、失败重试贵
> 10000收益趋平语句过长、可能触发参数上限

COPY：导入之王 ​

COPY 是最快的批量写入方式，因为它单次往返搬运整批数据、批量写 WAL、批量填页。注意区分服务端 COPY ... FROM 'file'（数据库进程读文件）与客户端 \copy（数据流经客户端）。

sql
-- 服务端 COPY：数据库进程直接读文件（需超级用户或 pg_read_server_files 角色）
COPY metrics (device_id, metric, value, recorded_at) FROM '/tmp/metrics.csv'
  WITH (FORMAT csv, HEADER true);

-- 客户端 \copy：psql 元命令，数据经客户端转发，普通用户即可执行
\copy metrics (device_id, metric, value, recorded_at) FROM '/tmp/metrics.csv' WITH (FORMAT csv, HEADER true)
text
COPY 1000000
Time: 2412.338 ms
-- 100 万行约 2.4 秒

COPY 的关键选项：

选项作用示例
FORMAT csv指定 CSV 格式WITH (FORMAT csv)
HEADER true首行是列名，跳过WITH (HEADER true)
DELIMITER ','分隔符WITH (DELIMITER '\t')
NULL ''空串视为 NULLWITH (NULL '')
ENCODING 'GBK'指定源文件编码WITH (ENCODING 'GBK')
FREEZE true直接冻结行，跳过后续 VACUUM仅限新建表
ON_ERROR ignore跳过坏行继续导入PostgreSQL 17+
sql
-- 完整性能组合：新表 + 单事务 + FREEZE + 关闭同步提交
BEGIN;
SET LOCAL synchronous_commit = off;   -- 本次事务提交不等待 WAL 落盘
CREATE TABLE metrics_bulk (LIKE metrics INCLUDING ALL);
COPY metrics_bulk (device_id, metric, value, recorded_at) FROM '/tmp/metrics.csv'
  WITH (FORMAT csv, HEADER true, FREEZE true);   -- PostgreSQL 18 中 FREEZE 选项可用
COMMIT;
text
COPY 1000000
Time: 1628.114 ms
-- 相比基线 2412 ms，再降约 32%
bash
# 更安全的方式：不暴露服务端文件路径，改用标准输入
cat /tmp/metrics.csv | psql -h localhost -U app -d demo \
  -c "COPY metrics (device_id, metric, value, recorded_at) FROM STDIN WITH (FORMAT csv, HEADER true)"

FREEZE 的使用前提

FREEZE 只应作用在刚创建或刚 TRUNCATE 过的表上。对已有数据的表使用会让新导入的行与旧行的可见性规则冲突，属于破坏性误用。

在线导入优化清单 ​

向已有数据的表导入时，可以按下面的清单逐项检查，每一项都是可量化的收益。

优化项做法预期收益风险
先导数据后建索引DROP INDEX → COPY → CREATE INDEX30%~60%期间无索引可用
关闭同步提交SET LOCAL synchronous_commit = off10%~30%断电可能丢最近事务
用 UNLOGGED 表CREATE UNLOGGED TABLE50%+崩溃后表被清空
分批提交每 10 万行 COMMIT避免长事务需处理批次失败
提高 maintenance_work_memSET maintenance_work_mem = '1GB'加速后续建索引占用内存
提高 max_wal_sizeSET max_wal_size = '8GB'减少检查点磁盘占用
导入后 ANALYZEVACUUM (ANALYZE) t优化器估算准确需额外时间
sql
-- 组合优化：先删索引，灌数据，最后重建索引
BEGIN;
SET LOCAL synchronous_commit = off;
SET LOCAL maintenance_work_mem = '1GB';

ALTER TABLE metrics DROP CONSTRAINT IF EXISTS metrics_pkey;
DROP INDEX IF EXISTS idx_metrics_device_time;

COPY metrics (id, device_id, metric, value, recorded_at) FROM '/tmp/metrics.csv'
  WITH (FORMAT csv, HEADER true);

ALTER TABLE metrics ADD PRIMARY KEY (id);
CREATE INDEX idx_metrics_device_time ON metrics (device_id, recorded_at);
COMMIT;

VACUUM (ANALYZE) metrics;
sql
-- UNLOGGED 表：不写 WAL，写入极快，适合可重建的中间表
CREATE UNLOGGED TABLE metrics_staging (
  device_id   INTEGER        NOT NULL,
  metric      TEXT           NOT NULL,
  value       NUMERIC(12, 3) NOT NULL,
  recorded_at TIMESTAMPTZ    NOT NULL
);
-- 导入完成后用 INSERT ... SELECT 转入正式表
text
-- 各优化项的叠加效果（100 万行导入，示意）
配置                                        耗时      相对
基线（有索引 + 默认参数）                    2412 ms   1.000
+ DROP INDEX                                1620 ms   0.672
+ synchronous_commit = off                  1310 ms   0.543
+ UNLOGGED 表                                 880 ms   0.365
+ 提高 maintenance_work_mem                   870 ms   0.361

批量 UPDATE 的分批策略 ​

大批量 UPDATE 会长时间持有行锁、产生大量死元组、并可能阻塞其他写入。策略是按主键区间分批，每批提交一次。

sql
-- 错误！一条语句更新全表：长事务 + 大量锁 + WAL 膨胀
UPDATE metrics SET value = value * 1.1 WHERE recorded_at < TIMESTAMPTZ '2026-06-01 00:00:00+08';
sql
-- 正确：用 DO 块按 id 区间分批更新，每批提交
DO $$
DECLARE
  v_min  BIGINT;
  v_max  BIGINT;
  v_step BIGINT := 50000;   -- 每批 5 万行
  v_from BIGINT;
BEGIN
  SELECT min(id), max(id) INTO v_min, v_max FROM metrics;
  v_from := v_min;

  WHILE v_from <= v_max LOOP
    UPDATE metrics
    SET value = value * 1.1
    WHERE id >= v_from
      AND id < v_from + v_step
      AND recorded_at < TIMESTAMPTZ '2026-06-01 00:00:00+08';   -- 业务条件不丢

    RAISE NOTICE '已完成区间 [%, %)', v_from, v_from + v_step;
    v_from := v_from + v_step;

    -- DO 块在单事务内，用 COMMIT 不可用；生产环境应由应用层循环提交
  END LOOP;
END $$;

关于 DO 块内提交

DO 块整体运行在一个事务里，无法在其中 COMMIT。上面的写法适合中小批量；超大数据量应改为应用层循环：每次查询一批主键 → 执行 UPDATE → 提交 → 进入下一批。这样每批都是独立事务，锁持有时间短、WAL 可及时归档。

sql
-- 应用层分批的推荐 SQL 形态：每次取一批主键
SELECT id FROM metrics
WHERE recorded_at < TIMESTAMPTZ '2026-06-01 00:00:00+08'
  AND value > 0
ORDER BY id
LIMIT 50000;   -- 拿到 id 列表后执行 UPDATE ... WHERE id = ANY(:ids)，然后提交

批量 UPDATE 的替代方案对比：

方案适用场景优点缺点
单条全表 UPDATE小表（< 10 万行）简单长事务、锁范围大
按主键区间分批大表、需控制锁锁持有时长短需应用层循环
UPDATE ... FROM (VALUES ...)少量行按各自值更新一条语句完成值列表不宜过大
创建新表 + 交换几乎全表重写无死元组需要额外空间
逻辑重建定期全量重算结果干净需要额外表与切换逻辑
sql
-- UPDATE ... FROM (VALUES ...)：少量行按各自不同值更新
UPDATE metrics AS m
SET value = v.new_value
FROM (VALUES
  (1::bigint, 100.000::numeric),
  (2::bigint, 200.000::numeric),
  (3::bigint, 300.000::numeric)
) AS v(id, new_value)
WHERE m.id = v.id;

批量 DELETE 与 TRUNCATE ​

DELETE 是逐行标记删除，TRUNCATE 是整表重置。两者在代价上差几个数量级，选错的代价很大。

sql
-- DELETE：逐行标记，产生死元组，需要后续 VACUUM
DELETE FROM metrics WHERE recorded_at < TIMESTAMPTZ '2026-01-02 00:00:00+08';
text
DELETE 86400
Time: 412.338 ms
-- 删 8.6 万行耗时 412 ms，且产生等量死元组，需要 VACUUM
sql
-- TRUNCATE：整表重置，几乎瞬时，但无法带 WHERE 条件
TRUNCATE TABLE metrics_staging;
text
TRUNCATE TABLE
Time: 3.118 ms
-- 无论表多大都是毫秒级，因为只删文件与元数据
操作条件支持速度死元组可回滚触发器
DELETE支持 WHERE慢，与行数成正比产生是触发 DELETE 触发器
TRUNCATE不支持 WHERE极快，与行数无关不产生是（事务内）触发 TRUNCATE 触发器
DROP TABLE整表极快不产生是（事务内）不触发
sql
-- 大表按时间清理历史数据：优先考虑分区 + DROP PARTITION
DROP TABLE metrics_2025_01;   -- 分区表按分区丢弃，代价极小

TRUNCATE 的锁与依赖

TRUNCATE 会获取 ACCESS EXCLUSIVE 锁，阻塞所有读写；同时会级联清空外键引用它的表，除非指定 RESTRICT 或先处理好依赖。

批量 UPSERT ​

「有则更新、无则插入」是数据同步的高频需求。PostgreSQL 提供 ON CONFLICT（即 UPSERT）和 SQL 标准的 MERGE 两种写法。

sql
-- ON CONFLICT：单表冲突处理，推荐用于高频同步
INSERT INTO devices (device_id, device_name, region, is_active, updated_at) VALUES
  (1, '网关-01', '华北', true,  now()),
  (2, '网关-02', '华南', false, now()),
  (3, '网关-03', '华东', true,  now())
ON CONFLICT (device_id) DO UPDATE
SET device_name = EXCLUDED.device_name,
    region      = EXCLUDED.region,
    is_active   = EXCLUDED.is_active,
    updated_at  = now();
sql
-- 带条件的 DO UPDATE：只在与原值不同时写入（减少无意义的更新）
INSERT INTO devices (device_id, device_name, region, is_active, updated_at) VALUES
  (1, '网关-01', '华北', true, now())
ON CONFLICT (device_id) DO UPDATE
SET device_name = EXCLUDED.device_name,
    updated_at  = now()
WHERE devices.device_name IS DISTINCT FROM EXCLUDED.device_name;
sql
-- DO NOTHING：冲突时跳过，适合「只插入新设备」
INSERT INTO devices (device_id, device_name, region) VALUES
  (1, '网关-01', '华北')
ON CONFLICT (device_id) DO NOTHING;
sql
-- MERGE：PostgreSQL 15+，适合多源、带 DELETE 分支的复杂同步
MERGE INTO devices AS d
USING (
  VALUES
    (1, '网关-01', '华北', true),
    (2, '网关-02', '华南', false)
) AS s(device_id, device_name, region, is_active)
ON d.device_id = s.device_id
WHEN MATCHED AND d.device_name IS DISTINCT FROM s.device_name THEN
  UPDATE SET device_name = s.device_name,
             region      = s.region,
             is_active   = s.is_active,
             updated_at  = now()
WHEN NOT MATCHED THEN
  INSERT (device_id, device_name, region, is_active)
  VALUES (s.device_id, s.device_name, s.region, s.is_active);

ON CONFLICT 与 MERGE 的选择：

维度ON CONFLICTMERGE
引入版本PostgreSQL 9.5PostgreSQL 15
数据源单表 INSERT 语句任意子查询 / 表
支持分支DO NOTHING / DO UPDATEWHEN MATCHED / WHEN NOT MATCHED（含 DELETE）
冲突判定唯一索引/约束ON 条件
并发安全冲突时可能 serialization_failure同样需要事务重试
推荐场景高频单表 upsert复杂多源同步

UPSERT 的必要前提

ON CONFLICT (col) 要求 col 上存在唯一索引或唯一约束，否则会报错：ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification。

RETURNING 与进度观察 ​

RETURNING 让你在一次写入中拿到结果，避免「写一次 + 查一次」的双倍往返。批量操作时它的价值尤其明显。

sql
-- 插入后返回生成的 id 与时间戳，无需再查询
INSERT INTO devices (device_id, device_name, region) VALUES
  (10, '采集器-10', '西南'),
  (11, '采集器-11', '西南')
RETURNING device_id, device_name, updated_at;
text
 device_id | device_name |          updated_at
-----------+-------------+-------------------------------
        10 | 采集器-10   | 2026-09-24 10:14:22.118+08
        11 | 采集器-11   | 2026-09-24 10:14:22.118+08
(2 rows)
sql
-- UPSERT + RETURNING：区分哪些是新增、哪些是更新
INSERT INTO devices (device_id, device_name, region) VALUES
  (1, '网关-01-改', '华北'),
  (20, '新设备-20', '华东')
ON CONFLICT (device_id) DO UPDATE
SET device_name = EXCLUDED.device_name,
    updated_at  = now()
RETURNING device_id, device_name, (xmax = 0) AS 是否新增;
text
 device_id | device_name | 是否新增
-----------+-------------+----------
         1 | 网关-01-改  | f        -- xmax <> 0 表示这是更新
        20 | 新设备-20   | t        -- xmax = 0 表示这是插入
(2 rows)
sql
-- 批量操作期间观察进度：COPY
SELECT
  pid,
  relid::regclass AS 目标表,
  command,
  type,
  bytes_processed,
  bytes_total,
  CASE WHEN bytes_total > 0
       THEN round(100.0 * bytes_processed / bytes_total, 1) || '%'
       ELSE '计算中'
  END AS 进度,
  tuples_processed
FROM pg_stat_progress_copy;
text
  pid  | 目标表  | command | type  | bytes_processed | bytes_total | 进度  | tuples_processed
-------+---------+---------+-------+-----------------+-------------+-------+------------------
 24118 | metrics | COPY FROM | FILE |        31457280 |    50331648 | 62.5% |           620000
(1 row)
sql
-- 批量操作期间观察进度：CREATE INDEX
SELECT
  pid,
  relid::regclass AS 表名,
  index_relid::regclass AS 索引名,
  phase,
  tuples_done,
  tuples_total,
  round(100.0 * tuples_done / NULLIF(tuples_total, 0), 1) AS 百分比
FROM pg_stat_progress_create_index;
text
  pid  |  表名   |      索引名            |        phase        | tuples_done | tuples_total | 百分比
-------+---------+------------------------+---------------------+-------------+--------------+--------
 24198 | metrics | idx_metrics_device_time| building index      |      680000 |      1000000 |   68.0
(1 row)
sql
-- 实时监控所有活动语句的进度（通用）
SELECT pid, state, wait_event_type, wait_event, left(query, 60) AS 语句
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

实战示例 ​

步骤 1：完整导入流程（外部 CSV → 正式表） ​

sql
-- 第 1 步：建临时非日志表（不写 WAL，写入极快）
CREATE UNLOGGED TABLE metrics_staging (
  device_id   INTEGER        NOT NULL,
  metric      TEXT           NOT NULL,
  value       NUMERIC(12, 3) NOT NULL,
  recorded_at TIMESTAMPTZ    NOT NULL
);
sql
-- 第 2 步：单事务导入，关同步提交，提高维护内存
BEGIN;
SET LOCAL synchronous_commit = off;
COPY metrics_staging (device_id, metric, value, recorded_at) FROM '/tmp/metrics.csv'
  WITH (FORMAT csv, HEADER true);
COMMIT;
text
COPY 1000000
Time: 880.214 ms
-- 相比有索引、默认参数的 2412 ms，提升约 2.7×
sql
-- 第 3 步：转入正式表（库内搬运，不经过客户端）
INSERT INTO metrics (device_id, metric, value, recorded_at)
SELECT device_id, metric, value, recorded_at FROM metrics_staging;

VACUUM (ANALYZE) metrics;
DROP TABLE metrics_staging;
text
INSERT 0 1000000
Time: 2841.882 ms
sql
-- 第 4 步：验证行数与数据范围
SELECT
  count(*)                 AS 总行数,
  min(recorded_at)         AS 最早时间,
  max(recorded_at)         AS 最晚时间,
  count(DISTINCT device_id) AS 设备数
FROM metrics;
text
  总行数  |       最早时间        |       最晚时间        | 设备数
----------+-----------------------+-----------------------+--------
  1000000 | 2026-01-01 00:00:01+08| 2026-01-12 13:46:40+08|   5000
(1 row)

步骤 2：分区表导入（超越 COPY 的手段） ​

当数据按月分区时，可以直接把数据导入对应分区，甚至用 DETACH/ATTACH 秒级加载历史分区。

sql
-- 按 recorded_at 按月分区的指标表
CREATE TABLE metrics_p (
  id          BIGINT GENERATED ALWAYS AS IDENTITY,
  device_id   INTEGER        NOT NULL,
  metric      TEXT           NOT NULL,
  value       NUMERIC(12, 3) NOT NULL,
  recorded_at TIMESTAMPTZ    NOT NULL
) PARTITION BY RANGE (recorded_at);

CREATE TABLE metrics_p_2026_01 PARTITION OF metrics_p
  FOR VALUES FROM ('2026-01-01+08') TO ('2026-02-01+08');
CREATE TABLE metrics_p_2026_02 PARTITION OF metrics_p
  FOR VALUES FROM ('2026-02-01+08') TO ('2026-03-01+08');
sql
-- 只往对应分区导入：影响面小、WAL 集中
COPY metrics_p_2026_01 (device_id, metric, value, recorded_at) FROM '/tmp/metrics.csv'
  WITH (FORMAT csv, HEADER true);
sql
-- 历史数据整体装载：先建好带数据的独立表，再 ATTACH 进分区表（瞬时完成）
CREATE TABLE metrics_p_2026_03 (LIKE metrics_p INCLUDING ALL);
COPY metrics_p_2026_03 (device_id, metric, value, recorded_at) FROM '/tmp/metrics_202603.csv'
  WITH (FORMAT csv, HEADER true);

ALTER TABLE metrics_p ATTACH PARTITION metrics_p_2026_03
  FOR VALUES FROM ('2026-03-01+08') TO ('2026-04-01+08');
text
ALTER TABLE
Time: 12.118 ms
-- 100 万行数据「瞬间」加入分区表，因为只是元数据操作
导入方式耗时（100 万行）说明
直接 COPY 进分区表约 2600 ms每行需路由到对应分区
COPY 进独立表 + ATTACH约 880 ms + 12 ms绕过分区路由，最快

步骤 3：批量更新的分批实现 ​

sql
-- 目标：把所有 2026-01 的指标值上调 10%
-- 先确认影响行数，避免误操作
SELECT count(*) AS 待更新行数
FROM metrics
WHERE recorded_at >= TIMESTAMPTZ '2026-01-01 00:00:00+08'
  AND recorded_at <  TIMESTAMPTZ '2026-02-01 00:00:00+08';
text
 待更新行数
------------
      86400
(1 row)
sql
-- 分批更新：每批 10000 行，用 CTE 圈定一批主键
WITH batch AS (
  SELECT id FROM metrics
  WHERE recorded_at >= TIMESTAMPTZ '2026-01-01 00:00:00+08'
    AND recorded_at <  TIMESTAMPTZ '2026-02-01 00:00:00+08'
    AND value > 0
  ORDER BY id
  LIMIT 10000
)
UPDATE metrics m
SET value = m.value * 1.1
FROM batch b
WHERE m.id = b.id;
text
UPDATE 10000
Time: 182.114 ms
-- 由应用层重复执行直到 UPDATE 0，每批提交一次
sql
-- 验证：抽查若干行的值变化
SELECT id, value FROM metrics ORDER BY id LIMIT 3;
text
 id |  value
----+---------
  1 | 110.000   -- 原 100.000，×1.1 正确
  2 | 220.000
  3 | 330.000
(3 rows)

步骤 4：用 RETURNING 与 xmax 区分插入/更新 ​

sql
-- 一批数据同步：既有新设备也有已存在设备
INSERT INTO devices (device_id, device_name, region, is_active) VALUES
  (1,  '网关-01',   '华北', true),
  (2,  '网关-02',   '华南', true),
  (30, '采集器-30', '西北', true),
  (31, '采集器-31', '西北', true)
ON CONFLICT (device_id) DO UPDATE
SET device_name = EXCLUDED.device_name,
    region      = EXCLUDED.region,
    is_active   = EXCLUDED.is_active,
    updated_at  = now()
RETURNING
  device_id,
  device_name,
  (xmax = 0) AS 本批新增   -- true 表示本次是插入
;
text
 device_id | device_name | 本批新增
-----------+-------------+----------
         1 | 网关-01     | f
         2 | 网关-02     | f
        30 | 采集器-30   | t
        31 | 采集器-31   | t
(4 rows)
sql
-- 应用层统计：一次写入即知新增 2 条、更新 2 条
-- 无需再查一次数据库，省去一次往返

步骤 5：观察批量操作进度 ​

sql
-- 会话 A：执行大批量 COPY（另开会话执行）
-- COPY metrics (device_id, metric, value, recorded_at) FROM '/tmp/metrics_large.csv'
--   WITH (FORMAT csv, HEADER true);

-- 会话 B：实时查看进度
\d+ pg_stat_progress_copy
SELECT
  pid,
  relid::regclass AS 目标表,
  command,
  bytes_processed,
  bytes_total,
  tuples_processed
FROM pg_stat_progress_copy;
text
  pid  | 目标表 |  command   | bytes_processed | bytes_total | tuples_processed
-------+--------+------------+-----------------+-------------+------------------
 24118 | metrics| COPY FROM  |        41943040 |    83886080 |           820000
(1 row)
-- 进度约 50%，已处理 82 万行
sql
-- 若导入很慢，先确认等待事件
SELECT pid, wait_event_type, wait_event, left(query, 50) AS 语句
FROM pg_stat_activity
WHERE pid = 24118;
text
  pid  | wait_event_type |  wait_event  |             语句
-------+-----------------+--------------+--------------------------------
 24118 | IO              | WALWrite     | COPY metrics (...) FROM STDIN
(1 row)
-- 等待 WAL 落盘 → 考虑 synchronous_commit = off 与提高 max_wal_size

注意事项 ​

  1. 不要逐行 INSERT 循环，这是批量写入最常见的性能事故：
    sql
    -- 错误！每条一次往返，100 万行要 1000 秒量级
-- FOR i IN 1..1000000 LOOP INSERT INTO metrics ... END LOOP;
  2. 多值 INSERT 的批大小控制在 500~1000 行，过大语句会导致解析变慢、内存升高。
  3. FREEZE 只能用在新建或刚 TRUNCATE 的表上，用在已有数据的表会造成可见性错误：
    sql
    -- 错误！metrics 已有数据，FREEZE 会导致旧行可见性异常
COPY metrics FROM '/tmp/metrics.csv' WITH (FREEZE true);
  4. UNLOGGED 表的代价是崩溃即丢数据，只用于可重建的中间表：
    sql
    -- 仅中间表可用；不要用于最终业务表
CREATE UNLOGGED TABLE metrics_staging (...);
  5. synchronous_commit = off 不是无损优化，断电可能丢失最近若干已提交事务，只应在可接受该风险时使用：
    sql
    -- 会话级临时设置，不要写进全局配置后忘记
SET LOCAL synchronous_commit = off;
  6. 批量 UPDATE 不要一条语句改全表，长事务会阻塞 VACUUM、撑大 WAL、持有大量锁。
  7. TRUNCATE 不支持 WHERE，需要条件删除时只能用 DELETE 或分区裁剪：
    sql
    -- 错误！TRUNCATE 不接受条件
-- TRUNCATE TABLE metrics WHERE recorded_at < '2026-01-02';
  8. ON CONFLICT (col) 前 col 必须有唯一索引/约束，否则直接报错。
  9. ON CONFLICT 在高并发下可能抛序列化失败，应用层必须实现重试；不要假设它永远成功。
  10. 导入完成后必须 ANALYZE，否则优化器仍按旧统计信息选计划，可能导致导入后查询反而变慢。
  11. 导入前先备份或确认可重跑，COPY 遇到格式错误会中止（除非 ON_ERROR），已导入部分需靠事务回滚。
  12. 不要在导入期间做 VACUUM FULL 或 CLUSTER，会与导入争抢锁并产生额外 IO，让两边都变慢。

最佳实践 ​

  1. 能一次做完，就绝不分多次：把 N 次单行写入合并成一次批量语句。
  2. 外部数据导入首选 COPY，库内搬运首选 INSERT ... SELECT，应用层批量选中多值 INSERT，三者各司其职。
  3. 大批量导入遵循「先删索引 → 灌数据 → 建索引 → ANALYZE」的顺序，索引维护是导入阶段最大的额外成本。
  4. 能重建的中间表用 UNLOGGED，导入完成后再转入正式表，写入速度提升显著。
  5. 导入事务内用 SET LOCAL synchronous_commit = off，把 WAL 刷盘成本从关键路径移开。
  6. 大表批量 UPDATE / DELETE 一律分批，每批 1 万~5 万行、独立事务提交，控制锁与 WAL。
  7. 历史数据清理优先用分区 DETACH / DROP PARTITION，比 DELETE 快几个数量级且不产生死元组。
  8. UPSERT 优先选 ON CONFLICT，它更适合高频单表同步；多源复杂同步再用 MERGE。
  9. 用 RETURNING 拿回写入结果，配合 (xmax = 0) 判断插入还是更新，省掉一次往返。
  10. 批量操作期间监控 pg_stat_progress_copy 与 pg_stat_progress_create_index，用进度与 wait_event 定位瓶颈。
  11. 执行破坏性批量操作前先做 Dry Run：BEGIN; ...; SELECT count(*); ROLLBACK; 验证影响范围。
  12. 批量操作安排在低峰期并预留足够磁盘：导入、建索引、VACUUM 都会临时占用大量空间与 IO。

下一节

继续学习：字符串拼接与连接（|| / CONCAT）

参考链接 ​