删除数据（DELETE）

DELETE 从表里移除行。它和 UPDATE 是一对孪生兄弟：同样靠 WHERE 决定范围，同样不是原地删除（旧行只被标记为过期，空间要等 VACUUM 回收），同样容易因为忘写条件而清空全表。本节从行级删除的视角讲清它的语义、关联删除、软删除与大批量删除的正确姿势。

前置知识

阅读本节前，建议先了解：更新数据（UPDATE）

基础概念

DELETE 移除满足条件的行，删除的粒度是行，而不是表或分区。它的行为由三个要素决定：删哪些行（USING + WHERE）、删完返回什么（RETURNING）、删不动时怎么办（外键动作）。

最关键的一点：DELETE 只是把行的 xmax 标记为当前事务号，让该版本对后续事务"不可见"。数据仍然躺在表文件里，表占用的磁盘空间不会因为 DELETE 而立刻下降。这一点与 DROP TABLE 完全不同。

组成要素	作用	是否必需
DELETE FROM 表名	目标表（可带别名）	必需
WHERE 条件	过滤要删除哪些行	可选（省略即全表）
USING 其他表	借助别的表过滤（类似隐式 JOIN）	可选
RETURNING	返回被删除行的值	可选

几个关键术语：

• 全表删除（Full Table Delete）：省略 WHERE 的 DELETE 会删掉表里每一行，且逐行走 MVCC 删除路径，比 TRUNCATE 慢得多。
• 死元组（Dead Tuple）：被删除的行版本。它们占据空间、参与扫描、需要被 VACUUM 清理后才能被新行复用。
• USING 的隐式连接：DELETE FROM t USING other WHERE t.id = other.id 是标准写法。若 WHERE 遗漏关联条件，会退化成笛卡尔积，删除范围失控。
• 软删除（Soft Delete）：不真正删行，而是把 deleted_at 置为时间戳，让查询自行过滤。业务上"可恢复"、代价是表持续膨胀且所有查询都要带谓词。
• 级联删除（ON DELETE CASCADE）：删除父表行时，自动删除所有引用它的子表行。它是额外的隐式写入，评估影响时不能漏。
• TRUNCATE：整表清空的 DDL 级操作，不逐行走 MVCC、立即释放空间、但会取 ACCESS EXCLUSIVE 锁。详细差异见 清空与删除表（TRUNCATE / DROP）。

语法与用法

完整语法骨架：

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    [ USING from_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

四种最常用的形态：

-- 1. 按主键删单行（务必带 WHERE）
DELETE FROM orders WHERE id = 42;

-- 2. 按条件批量删
DELETE FROM orders WHERE status = 'cancelled' AND created_at < now() - interval '30 days';

-- 3. 借助其他表过滤（USING）
DELETE FROM order_items oi
USING orders o
WHERE oi.order_no = o.order_no AND o.status = 'cancelled';

-- 4. 删除并返回被删内容（便于审计或搬移到归档表）
DELETE FROM orders WHERE status = 'cancelled' RETURNING *;

DELETE、TRUNCATE、DROP TABLE 三者的边界：

操作	删除粒度	可带 WHERE	空间回收	锁级别	可否回滚
DELETE	行	是	需 VACUUM	行级锁	可以
TRUNCATE	全表（或指定分区）	否	立即释放	ACCESS EXCLUSIVE	可以
DROP TABLE	整张表（含结构）	否	立即释放	ACCESS EXCLUSIVE	可以

WHERE 中可用的典型条件：

条件形式	示例	说明
等值	WHERE id = 42	走主键，最快
范围	WHERE created_at < now() - interval '7 days'	建议建索引
子查询	WHERE order_no IN (SELECT order_no FROM blacklist)	注意 NULL 陷阱
EXISTS	WHERE EXISTS (SELECT 1 FROM p WHERE p.oid = o.id)	大表关联通常优于 IN
ctid 列表	WHERE ctid = ANY ($1)	配合分批删除使用

详细说明

WHERE 决定一切

DELETE 的破坏力与 WHERE 的准确性直接成正比。执行前先用同样的条件做一次 count(*)，是这个操作最基本的纪律。

-- 危险：删除全表所有行（逐行走 MVCC，大表上极慢）
DELETE FROM orders;

-- 正确：先确认范围，再删除
SELECT count(*) FROM orders WHERE status = 'cancelled';
DELETE FROM orders WHERE status = 'cancelled';

RETURNING 让"删之前先看一眼"和"删除"合并成一条语句，非常适合迁移到归档表的场景：

-- 先确认将删除哪些行
SELECT id, order_no, status FROM orders WHERE status = 'cancelled';

-- 确认无误后再执行，同时返回被删内容
DELETE FROM orders WHERE status = 'cancelled' RETURNING id, order_no;

USING：借助其他表过滤

DELETE 没有 JOIN 关键字，关联过滤靠 USING 完成，语义与 UPDATE ... FROM 完全对应。

-- 删除所有属于已取消订单的明细行
DELETE FROM order_items oi
USING orders o
WHERE oi.order_no = o.order_no
  AND o.status = 'cancelled';

DELETE 12

遗漏关联条件会退化为笛卡尔积，导致每一行都被判定为待删除：

-- 错误！缺少 oi.order_no = o.order_no
DELETE FROM order_items oi
USING orders o
WHERE o.status = 'cancelled';

还有一个容易忽略的点：若 USING 中的表被同一语句删除（自连接场景），行为取决于连接条件的写法，应当避免在一条语句里既删父又删子。

-- 不推荐：在一条语句里同时处理父子表
DELETE FROM orders o USING order_items oi WHERE oi.order_no = o.order_no;
-- 正确：分两条语句，先删子表再删父表（或有外键级联时只删父表）

外键的删除动作

子表引用父表时，ON DELETE 决定父表行被删时子表怎么办。这是 DELETE 最容易引发"雪崩"的地方。

ON DELETE 动作	子表行为	适用场景
NO ACTION（默认）	有引用则报错（可延迟到语句末）	保守，强制应用处理
RESTRICT	有引用立即报错（不延迟）	更严格的保护
CASCADE	自动删除所有引用行	强从属关系（订单 → 明细）
SET NULL	把外键列置为 NULL	弱关联（可选归属）
SET DEFAULT	把外键列置为默认值	少见，需默认值有效

-- 建立带级联删除的父子表
CREATE TABLE orders_parent (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no text NOT NULL UNIQUE
);

CREATE TABLE order_items_child (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no text NOT NULL REFERENCES orders_parent (order_no) ON DELETE CASCADE,
  sku      text NOT NULL,
  qty      int  NOT NULL
);

-- 删除父表一行，子表对应行自动消失
DELETE FROM orders_parent WHERE order_no = 'ORD-2001';

DELETE 1

若把动作改为 RESTRICT，只要子表还有引用，删除就会被拒绝：

ERROR:  update or delete on table "orders_parent" violates foreign key constraint
        "order_items_child_order_no_fkey" on table "order_items_child"
DETAIL:  Key (order_no)=(ORD-2001) is still referenced from table "order_items_child".

软删除：删除标记代替真删

很多业务要求"可恢复"，于是用一列 deleted_at 代替物理删除。它把所有删除压力转移成了更新压力。

-- 软删除：只标记，不删行
UPDATE orders SET deleted_at = now() WHERE order_no = 'ORD-1004';

UPDATE 1

代价与对策：

-- 1. 所有业务查询都必须带谓词，否则已删除的行会"复活"
SELECT * FROM orders WHERE deleted_at IS NULL;

-- 2. 用部分索引只索引未删除的行，索引更小、命中更快
CREATE INDEX idx_orders_alive ON orders (created_at) WHERE deleted_at IS NULL;

-- 3. 唯一约束也要改成部分唯一索引，否则删除后无法重建同号记录
CREATE UNIQUE INDEX uq_orders_no_alive ON orders (order_no) WHERE deleted_at IS NULL;

CREATE INDEX
CREATE INDEX

需要"恢复"时只需清空标记，这是物理删除无法做到的：

-- 恢复一条被软删除的订单
UPDATE orders SET deleted_at = NULL WHERE order_no = 'ORD-1004';

UPDATE 1

大批量删除与空间回收

大表上一口气删掉几百万行，会带来长事务、大量 WAL、主从延迟、锁等待，以及删完之后空间依然不释放的困惑。

-- 分批删除：每轮删 1000 行，重复执行直到返回 DELETE 0
WITH victim AS (
  SELECT ctid FROM orders_demo WHERE status = 'cancelled' LIMIT 1000
)
DELETE FROM orders_demo o
USING victim v
WHERE o.ctid = v.ctid;

DELETE 1000
-- 重复执行；返回 DELETE 0 表示处理完毕

ctid 会随行版本变化，因此每轮都必须重新查询（上面的写法正是如此）。另一个思路是按主键区间推进：

-- 按主键区间分批（适合 id 连续、可断点续跑）
DELETE FROM orders_demo
WHERE id BETWEEN 1 AND 1000 AND status = 'cancelled';
-- 下一轮改为 1001 ~ 2000，依此类推

分批依据	优点	缺点
ctid	精准命中待删行，不空转	必须每轮重新查询
主键区间	逻辑简单、可断点续跑	区间内可能无待删行，存在空转

删除完成后空间并不会归还操作系统，只是被标记为"可复用"：

-- 查看表占用与死元组
SELECT pg_size_pretty(pg_total_relation_size('orders_demo')) AS total_size,
       n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders_demo';

 total_size | n_live_tup | n_dead_tup |     last_autovacuum
------------+------------+------------+-------------------------
 142 MB     |      12000 |      88000 | 2026-09-24 16:20:41.223+08
(1 row)

要让空间真正归还操作系统，只能重建表：

-- 方案 A：VACUUM FULL（会持有 ACCESS EXCLUSIVE 锁，阻塞读写）
VACUUM FULL orders_demo;

-- 方案 B：pg_repack（在线整理，需要额外安装扩展）
-- pg_repack --table=orders_demo mydb

DELETE 的并发语义

DELETE 与 UPDATE 一样在目标行上取排他锁。并发场景下，READ COMMITTED 中的删除会等待对方事务结束后在最新版本上重新求值 WHERE，若不满足则跳过该行。

-- 事务 A
BEGIN;
DELETE FROM inventory WHERE sku = 'SKU-1001';
-- 尚未提交

-- 事务 B（会阻塞等待 A 结束）
DELETE FROM inventory WHERE sku = 'SKU-1001';

"先查出来再删"的写法在高并发下会漏删或重复处理，正确做法是让数据库一次性完成筛选与加锁：

-- 取一批任务行并锁住，避免多消费者重复处理（跳过已被锁定的行）
DELETE FROM job_queue
WHERE ctid IN (
  SELECT ctid FROM job_queue
  ORDER BY created_at
  LIMIT 10
  FOR UPDATE SKIP LOCKED
)
RETURNING id, payload;

DELETE 10

用 CTE 把子表数据先"消费"掉再删父表，是常见的原子搬移写法：

-- 原子搬移：把待归档订单移入归档表并删除原行
WITH moved AS (
  DELETE FROM orders_demo
  WHERE status = 'expired'
  RETURNING *
)
INSERT INTO orders_archive (order_no, user_email, amount, status)
SELECT order_no, user_email, amount, status FROM moved;

INSERT 0 3

实战示例

下面用一个"取消订单 → 级联删除明细 → 归档 → 核对"的完整流程演示 DELETE 的典型用法。

-- 步骤 1：准备数据
DROP TABLE IF EXISTS order_items_demo;
DROP TABLE IF EXISTS orders_demo;
DROP TABLE IF EXISTS orders_archive;

CREATE TABLE orders_demo (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no   text          NOT NULL UNIQUE,
  user_email text          NOT NULL,
  amount     numeric(12,2) NOT NULL CHECK (amount >= 0),
  status     text          NOT NULL DEFAULT 'pending',
  deleted_at timestamptz,
  created_at timestamptz   NOT NULL DEFAULT now()
);

CREATE TABLE order_items_demo (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no text NOT NULL REFERENCES orders_demo (order_no) ON DELETE CASCADE,
  sku      text NOT NULL,
  qty      int  NOT NULL CHECK (qty > 0)
);

CREATE TABLE orders_archive (LIKE orders_demo INCLUDING ALL);

INSERT INTO orders_demo (order_no, user_email, amount, status) VALUES
  ('ORD-1001', 'alice@example.com',  199.00, 'cancelled'),
  ('ORD-1002', 'bob@example.com',    880.00, 'paid'),
  ('ORD-1003', 'carol@example.com', 3200.00, 'cancelled'),
  ('ORD-1004', 'dave@example.com',    15.90, 'pending');

INSERT INTO order_items_demo (order_no, sku, qty) VALUES
  ('ORD-1001', 'SKU-1001', 1),
  ('ORD-1001', 'SKU-1002', 2),
  ('ORD-1003', 'SKU-2001', 1),
  ('ORD-1004', 'SKU-3001', 5);

DROP TABLE
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 4
INSERT 0 4

-- 步骤 2：按条件删除前先确认范围
SELECT count(*) AS will_delete FROM order_items_demo
WHERE order_no IN (SELECT order_no FROM orders_demo WHERE status = 'cancelled');

 will_delete
-------------
           3
(1 row)

-- 步骤 3：删除前先归档（把要删的行搬进归档表）
WITH moved AS (
  DELETE FROM orders_demo
  WHERE status = 'cancelled'
  RETURNING *
)
INSERT INTO orders_archive (order_no, user_email, amount, status, created_at)
SELECT order_no, user_email, amount, status, created_at FROM moved;

INSERT 0 2

-- 步骤 4：确认级联删除已生效（子表对应明细行自动消失）
SELECT count(*) AS remaining_items FROM order_items_demo;

 remaining_items
-----------------
               1
(1 row)

-- 步骤 5：验证外键保护 —— 删除仍被引用的父表行应当报错
DELETE FROM orders_demo WHERE order_no = 'ORD-1004';

ERROR:  update or delete on table "orders_demo" violates foreign key constraint
        "order_items_demo_order_no_fkey" on table "order_items_demo"
DETAIL:  Key (order_no)=(ORD-1004) is still referenced from table "order_items_demo".

-- 步骤 6：先删子行，再删父行
DELETE FROM order_items_demo WHERE order_no = 'ORD-1004';
DELETE FROM orders_demo WHERE order_no = 'ORD-1004' RETURNING order_no, status;

DELETE 1

 order_no | status
----------+--------
 ORD-1004 | pending
(1 row)

DELETE 1

-- 步骤 7：软删除一条订单并验证查询过滤
UPDATE orders_demo SET deleted_at = now() WHERE order_no = 'ORD-1002';

SELECT count(*) AS all_rows,
       count(*) FILTER (WHERE deleted_at IS NULL) AS visible_rows
FROM orders_demo;

 all_rows | visible_rows
----------+--------------
        1 |            0
(1 row)

UPDATE 1

-- 步骤 8：核对归档表内容
SELECT order_no, status, amount FROM orders_archive ORDER BY order_no;

 order_no |  status   | amount
----------+-----------+---------
 ORD-1001 | cancelled |  199.00
 ORD-1003 | cancelled | 3200.00
(2 rows)

-- 步骤 9：观察删除统计与死元组
SELECT relname, n_tup_del, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname IN ('orders_demo', 'order_items_demo')
ORDER BY relname;

     relname      | n_tup_del | n_live_tup | n_dead_tup
------------------+-----------+------------+------------
 order_items_demo |         3 |          1 |          3
 orders_demo      |         3 |          1 |          3
(2 rows)

上面的流程覆盖了 DELETE 的主要形态：条件删除、USING 关联、RETURNING 归档、外键级联与保护、软删除，以及死元组统计。

注意事项

• 省略 WHERE 会删空整表。 且 DELETE 逐行走 MVCC，比 TRUNCATE 慢几个数量级。清空整表请用 TRUNCATE。

-- 灾难！逐行删除全表
DELETE FROM orders_demo;

-- 正确：整表清空用 TRUNCATE（见 DDL 章节）
TRUNCATE TABLE orders_demo;

• USING 少了关联条件会静默删掉所有行。 与 UPDATE ... FROM 是同一个坑。

-- 错误！缺少 oi.order_no = o.order_no
DELETE FROM order_items_demo oi
USING orders_demo o
WHERE o.status = 'cancelled';

• DELETE 不会释放磁盘空间。 被删的行变成死元组，空间要等 VACUUM 后复用，VACUUM FULL 或 pg_repack 才会归还操作系统。

• 级联删除是隐式放大。 删父表一行可能连带删除子表成千上万行，还可能在多层外键上继续传递。评估影响时务必沿着外键链数一遍。

• IN 子查询遇 NULL 会失效。 WHERE id NOT IN (SELECT ...) 若子查询返回 NULL，整个条件恒为 NULL，一行都删不掉。

-- 危险：子查询若有 NULL，NOT IN 永远不成立
DELETE FROM orders WHERE id NOT IN (SELECT order_id FROM blacklist);

-- 更稳：用 NOT EXISTS
DELETE FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM blacklist b WHERE b.order_id = o.id);

• 软删除让表无限膨胀。 所有查询都必须带 deleted_at IS NULL，否则"已删除"的数据会重新出现。务必用部分索引、视图或 RLS 把过滤条件固化下来。

• DELETE ... RETURNING 配合 CTE 能做原子搬移，但要注意并发。 同一批行不会被两条并发语句同时取到，因为删除会加行锁。

• WHERE CURRENT OF 只能配合游标使用。 它删除游标当前指向的行，通常只在存储过程里出现。

• 删除后统计信息不会自动更新。 大比例删除会显著改变数据分布，应及时 ANALYZE，否则后续查询可能选错计划。

• 大表删除必须分批提交。 单条语句删除千万行会长时间持锁、制造巨额 WAL、把主从延迟推高，甚至触发 lock_timeout。

最佳实践

1. 执行前一律先 SELECT count(*) 验证范围，并用事务包裹。 这是成本最低的一道保险。

BEGIN;
  SELECT count(*) FROM orders_demo WHERE status = 'cancelled';   -- 先看范围
  DELETE FROM orders_demo WHERE status = 'cancelled' RETURNING order_no;
  -- 核对返回结果无误后提交
COMMIT;
-- 有异常则 ROLLBACK;

2. 把影响行数当业务断言。 期望删 1 行却得到 0 行，说明状态不符合预期，应作为异常路径处理而不是静默通过。

DELETE FROM orders_demo WHERE order_no = 'ORD-9999';
-- 应用层检查：若 DELETE 0，抛出"订单不存在或状态已变更"

3. "先写成 SELECT，再改写成 DELETE"，尤其是带 USING 的关联删除。

-- 第一步：验证关联行数与内容
SELECT oi.* FROM order_items_demo oi
JOIN orders_demo o ON oi.order_no = o.order_no
WHERE o.status = 'cancelled';

-- 第二步：确认无误后改写为 DELETE ... USING
DELETE FROM order_items_demo oi
USING orders_demo o
WHERE oi.order_no = o.order_no AND o.status = 'cancelled';

4. 需要留痕就用 RETURNING 或 CTE 搬移。 直接删除无法审计，而"删除 + 落归档表"可以在一条语句内原子完成。

WITH moved AS (
  DELETE FROM orders_demo WHERE status = 'expired' RETURNING *
)
INSERT INTO orders_archive (order_no, user_email, amount, status)
SELECT order_no, user_email, amount, status FROM moved;

5. 业务要求可恢复就用软删除，并把过滤条件固化。 用 deleted_at + 部分索引 + 视图，避免每个查询手写谓词。

CREATE INDEX idx_orders_alive ON orders_demo (created_at) WHERE deleted_at IS NULL;

CREATE VIEW orders_active AS
SELECT * FROM orders_demo WHERE deleted_at IS NULL;

6. 大表删除一律分批。 每批 1000 ~ 10000 行并提交一次，批之间可短暂休眠让 autovacuum 跟上。

-- 每批 1000 行的循环（示意）
WITH victim AS (
  SELECT ctid FROM orders_demo WHERE status = 'cancelled' LIMIT 1000
)
DELETE FROM orders_demo o USING victim v WHERE o.ctid = v.ctid;
-- 重复执行直至返回 DELETE 0

7. 数据量占比极高时改用"重建表"策略。 删除全表 90% 的行，不如把保留的行灌进新表再原子切换，速度与空间回收都更好。

BEGIN;
  CREATE TABLE orders_demo_new (LIKE orders_demo INCLUDING ALL);
  INSERT INTO orders_demo_new
  SELECT * FROM orders_demo WHERE status <> 'cancelled';
  ALTER TABLE orders_demo RENAME TO orders_demo_old;
  ALTER TABLE orders_demo_new RENAME TO orders_demo;
COMMIT;
-- 确认无误后再 DROP TABLE orders_demo_old;

8. 消费者模型用 FOR UPDATE SKIP LOCKED 抢占任务。 让多个 worker 各自领走不同的行，避免重复处理与相互阻塞。

DELETE FROM job_queue
WHERE ctid IN (
  SELECT ctid FROM job_queue ORDER BY created_at LIMIT 10 FOR UPDATE SKIP LOCKED
)
RETURNING id, payload;

9. 删除后及时 ANALYZE，并对高频删除的表调高 autovacuum 频率。 死元组与统计信息滞后是删除操作留下的两份"技术债"。

VACUUM (ANALYZE) orders_demo;

-- 对该表更激进的 autovacuum 策略
ALTER TABLE orders_demo SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_threshold      = 100
);

下一节

继续学习：合并数据（MERGE）

参考链接

• DELETE
• Deleting Data
• Foreign Keys
• Routine Vacuuming