删除表与清空数据（DROP TABLE / TRUNCATE）

表可以"退休"，数据也可以"清空重来"。本节讲两组容易混淆的语句：DROP TABLE（连表带数据一起删掉）与 TRUNCATE（保留表结构、只清空数据），以及清空后磁盘空间为什么不会立刻还给操作系统、该用 VACUUM 还是 VACUUM FULL 还是 pg_repack。

前置知识

阅读本节前，建议先了解：修改表结构（ALTER TABLE）

基础概念

先分清四组语句的语义边界：

语句	对象	数据	表结构	索引	可回滚	空间立即释放
DELETE FROM t	行	全删/部分删	保留	保留	是	否（需 VACUUM）
TRUNCATE t	全部行	全删	保留	保留（清空）	是	否（标记待回收）
DROP TABLE t	表	全删	删除	删除	是	是（文件被删）
DROP DATABASE d	库	全删	删除	删除	否	是

几个核心概念：

• RESTRICT 与 CASCADE：DROP 的默认行为是 RESTRICT——只要有任何对象依赖它（视图、外键、函数……）就拒绝执行。CASCADE 则自动连带删除所有依赖对象，包括别的表。
• TRUNCATE 是 DDL 而非 DML：它不走逐行删除的路径，而是直接新建一个空的表文件（物理换文件），因此速度与行数无关，且会立即重置表的存储（relfilenode 变化）。
• ACCESS EXCLUSIVE 锁：TRUNCATE 需要在表上取 ACCESS EXCLUSIVE 锁，连 SELECT 都会被阻塞。这与 DELETE 只取 ROW EXCLUSIVE（不阻塞读写）有本质区别。
• relfilenode：表在磁盘上的物理文件编号。TRUNCATE、VACUUM FULL、CLUSTER 都会改变它。
• 膨胀（Bloat）：DELETE 只是把行标记为"死元组"，空间留在页内待后续复用。若长期不清理，表文件会持续变大——这就是膨胀。
• VACUUM vs VACUUM FULL：前者只把死元组空间标记为"可复用"，文件不缩小；后者会重写整张表，真正把空间还给操作系统，但需要 ACCESS EXCLUSIVE 锁。

DROP TABLE

-- 最简形式：表不存在会报错
DROP TABLE orders;

-- 幂等写法：不存在则静默跳过
DROP TABLE IF EXISTS orders;

-- 删除多张表（可用逗号一次列出）
DROP TABLE IF EXISTS orders, order_items, products;

-- 有依赖时强制删除（危险：会连带删掉视图、外键等）
DROP TABLE IF EXISTS users CASCADE;

DROP TABLE

IF EXISTS 与 CASCADE 的区别必须分清：前者管"表本身存不存在"，后者管"有没有别的对象依赖它"。

-- 有依赖时默认行为：报错退出，保护数据
DROP TABLE users;

ERROR:  cannot drop table users because other objects depend on it
DETAIL:  constraint fk_orders_user on table orders depends on table users
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

TRUNCATE

-- 基本形式：清空所有行
TRUNCATE TABLE orders;

-- 重置自增列（IDENTITY / serial）的计数器
TRUNCATE TABLE orders RESTART IDENTITY;

-- 清空表并连带清空所有引用它的子表（外键级联）
TRUNCATE TABLE orders CASCADE;

-- 一次清空多张表（推荐：避免多次加锁）
TRUNCATE TABLE orders, order_items RESTART IDENTITY;

-- 只清空该表，不动继承的子表
TRUNCATE TABLE ONLY parent_table;

TRUNCATE TABLE

与 DELETE 的对照表如下，这张表决定了生产环境该选哪个：

对比项	DELETE FROM t	TRUNCATE t
语句类别	DML	DDL
行级触发器	逐行触发	不触发
WHERE 条件	支持	不支持（只能全清）
速度（大表）	慢（逐行 + WAL + 触发器）	极快（换文件，与行数无关）
锁级别	ROW EXCLUSIVE	ACCESS EXCLUSIVE
是否阻塞读	否	是
重置自增序列	否	需 RESTART IDENTITY
重置统计信息	否	是（pg_statistic 部分保留）
空间回收	需 VACUUM	文件被替换，原文件待回收
事务内可回滚	是	是
触发器/规则	触发	忽略（ON DELETE 规则不生效）
外键引用	逐行检查	必须 CASCADE 或表被同时清空

TRUNCATE 不触发 ON DELETE 触发器与规则，这是它最容易被忽略的行为差异——依赖触发器做级联清理或审计的系统，改 DELETE 为 TRUNCATE 后会静默失效。

详细说明

TRUNCATE 为什么快：物理换文件

TRUNCATE 的实现不是"逐行删"，而是为表新建一个空的存储文件，并把 relfilenode 指向新文件。

-- 观察 relfilenode 变化：TRUNCATE 前后不同
SELECT relname, relfilenode, pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class WHERE relname = 'orders';

 relname | relfilenode | size
---------+-------------+-------
 orders  |       24601 | 8192 kB
(1 row)

TRUNCATE TABLE orders;

SELECT relname, relfilenode, pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class WHERE relname = 'orders';

 relname | relfilenode | size
---------+-------------+-------
 orders  |       24699 | 0 bytes
(1 row)

因此 TRUNCATE 的耗时与表大小几乎无关，万行表和亿行表都是毫秒级——代价是全程持有 ACCESS EXCLUSIVE 锁。

空间不会自动还给操作系统

这是 PostgreSQL 运维中最常见的困惑。看一个实验：

-- 建表并写入大量数据
CREATE TABLE bloat_demo (id bigint GENERATED ALWAYS AS IDENTITY, payload text);

INSERT INTO bloat_demo (payload)
SELECT repeat('x', 200) FROM generate_series(1, 500000);

INSERT 0 500000

-- 全表删除
DELETE FROM bloat_demo;

-- 空间几乎没有变化
SELECT pg_size_pretty(pg_total_relation_size('bloat_demo')) AS size_after_delete;

 size_after_delete
-------------------
 76 MB
(1 row)

DELETE 只是把行版本标记为死元组，页仍然属于这张表。空间要经过两级回收：

-- 第一级：VACUUM —— 标记页内空间"可复用"，文件不缩小
VACUUM bloat_demo;
SELECT pg_size_pretty(pg_total_relation_size('bloat_demo')) AS size_after_vacuum;

 size_after_vacuum
-------------------
 76 MB
(1 row)

-- 第二级：VACUUM FULL —— 重写整表，真正归还空间（需 ACCESS EXCLUSIVE 锁）
VACUUM FULL bloat_demo;
SELECT pg_size_pretty(pg_total_relation_size('bloat_demo')) AS size_after_full;

 size_after_full
-----------------
 56 kB
(1 row)

三级手段的取舍：

手段	是否归还 OS 空间	锁	代价	适用场景
VACUUM	否（仅内部复用）	SHARE UPDATE EXCLUSIVE	低，可并发	日常维护，防膨胀
VACUUM FULL	是	ACCESS EXCLUSIVE	高，全程阻塞且需等量磁盘	小表、可停机窗口
VACUUM (FULL) 分区表	是	逐分区	中	分区表可逐区处理
pg_repack（扩展）	是	仅切换瞬间持锁	中，需装扩展	大表在线回收标准方案
CLUSTER	是	ACCESS EXCLUSIVE	高	顺带按索引排序

删表后空间与依赖的处理

-- 查一张表占据的磁盘空间明细
SELECT
  pg_size_pretty(pg_relation_size('orders'))       AS table_size,
  pg_size_pretty(pg_indexes_size('orders'))        AS index_size,
  pg_size_pretty(pg_total_relation_size('orders')) AS total_size;

 table_size | index_size | total_size
------------+------------+------------
 4096 kB    | 2048 kB    | 6144 kB
(1 row)

-- 找出所有"孤儿"大表，判断哪些可以清理
SELECT relname,
       pg_size_pretty(pg_total_relation_size(oid)) AS total_size,
       n_live_tup
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 10;

-- 查看某张表的复写/清理历史（判断是否长期未 VACUUM）
SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze
FROM pg_stat_user_tables WHERE relname = 'orders';

大表删除的正确姿势

直接 DROP TABLE 一张几百 GB 的表，会在提交瞬间触发大量文件删除，可能造成 IO 抖动；更糟的是，如果表上有外键被其他表引用，CASCADE 会级联删表，风险极高。

生产上的稳妥流程：

-- 第 1 步：先重命名（瞬间完成，只改元数据），让表"消失"在业务视野里
ALTER TABLE orders_old RENAME TO orders_trash_20260924;

-- 第 2 步：观察一段时间，确认没有业务报错、没有依赖遗漏
-- 第 3 步：确认无误后再真正 DROP
DROP TABLE IF EXISTS orders_trash_20260924;

若表很大且删除动作影响了 IO，可以先用 TRUNCATE 清空数据，再 DROP：

-- 先清数据（换文件，几乎瞬间），大幅减少 DROP 时的文件删除量
TRUNCATE TABLE big_orders_archive;
DROP TABLE big_orders_archive;

实战示例

场景：orders 表数据严重膨胀，需要完成三件事——① 归档旧数据到新表；② 清空原表并重置自增；③ 回收磁盘空间，全过程遵守"先备份、后清理、再回收"的顺序。

第 1 步：准备演示环境

DROP TABLE IF EXISTS orders, order_items, users CASCADE;

CREATE TABLE users (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text   NOT NULL UNIQUE
);

CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint NOT NULL REFERENCES users (id),
  order_no   text   NOT NULL,
  amount     numeric(12,2) NOT NULL
);

CREATE TABLE order_items (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id bigint NOT NULL REFERENCES orders (id),
  sku      text   NOT NULL
);

INSERT INTO users (email) VALUES ('a@example.com'), ('b@example.com');

INSERT INTO orders (user_id, order_no, amount)
SELECT (id % 2) + 1,
       'SO-' || lpad(id::text, 8, '0'),
       (random() * 1000)::numeric(12,2)
FROM generate_series(1, 100000) AS id;

INSERT INTO order_items (order_id, sku)
SELECT id, 'SKU-' || (id % 50) FROM orders;

CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 100000
INSERT 0 100000

第 2 步：查看当前体积

SELECT
  relname,
  pg_size_pretty(pg_relation_size(oid))  AS heap,
  pg_size_pretty(pg_indexes_size(oid))   AS indexes,
  pg_size_pretty(pg_total_relation_size(oid)) AS total
FROM pg_class
WHERE relname IN ('orders', 'order_items')
ORDER BY pg_total_relation_size(oid) DESC;

   relname   |  heap   | indexes | total
-------------+---------+---------+--------
 orders      | 6104 kB | 4040 kB | 10 MB
 order_items | 5752 kB | 4040 kB | 9800 kB
(2 rows)

第 3 步：归档旧数据（CTAS，后续章节详讲）

-- 先备份再删除：这是不可省略的一步
CREATE TABLE orders_archive_2026h1 AS
SELECT * FROM orders WHERE id <= 50000;

SELECT count(*) FROM orders_archive_2026h1;

SELECT 50000

 count
-------
 50000
(1 row)

第 4 步：用 DELETE 删部分数据，观察空间不释放

DELETE FROM order_items WHERE order_id <= 50000;
DELETE FROM orders      WHERE id <= 50000;

DELETE 50000
DELETE 50000

-- 行数减半，但体积几乎不变
SELECT count(*) AS rows_left FROM orders;

SELECT pg_size_pretty(pg_total_relation_size('orders')) AS size_still;

 rows_left
-----------
     50000
(1 row)

 size_still
------------
 10 MB
(1 row)

第 5 步：VACUUM 与 VACUUM FULL 的效果差异

-- 普通 VACUUM：空间只标记可复用，文件不缩小
VACUUM orders;
SELECT pg_size_pretty(pg_total_relation_size('orders')) AS after_vacuum;

-- VACUUM FULL：真正重写并归还空间（阻塞）
VACUUM FULL orders;
SELECT pg_size_pretty(pg_total_relation_size('orders')) AS after_full;

VACUUM

 after_vacuum
--------------
 10 MB
(1 row)

VACUUM

 after_full
------------
 5424 kB
(1 row)

第 6 步：TRUNCATE 清空并重置自增

-- 注意：orders 被 order_items 外键引用，直接 TRUNCATE 会报错
TRUNCATE TABLE orders;

ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "order_items" references "orders".
HINT:  Truncate table "order_items" at the same time, or use TRUNCATE ... CASCADE.

-- 正确：一次 TRUNCATE 多张表，并重置 IDENTITY 计数器
TRUNCATE TABLE order_items, orders RESTART IDENTITY;

-- 验证行数归零、自增归位
SELECT (SELECT count(*) FROM orders) AS orders_rows,
       (SELECT count(*) FROM order_items) AS items_rows;

TRUNCATE TABLE

 orders_rows | items_rows
-------------+------------
           0 |          0
(1 row)

-- 插入新行，确认 id 从 1 重新开始
INSERT INTO orders (user_id, order_no, amount) VALUES (1, 'SO-NEW-0001', 10.00);
SELECT id, order_no FROM orders;

INSERT 0 1

 id |  order_no
----+------------
  1 | SO-NEW-0001
(1 row)

第 7 步：观察 TRUNCATE 后 relfilenode 变化

SELECT relname, relfilenode, pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class WHERE relname IN ('orders', 'order_items') ORDER BY relname;

   relname   | relfilenode | size
-------------+-------------+---------
 order_items |       24710 | 0 bytes
 orders      |       24708 | 16 kB
(2 rows)

第 8 步：验证 DROP 的依赖保护

-- 直接删被引用的表会失败
DROP TABLE users;

ERROR:  cannot drop table users because other objects depend on it
DETAIL:  constraint orders_user_id_fkey on table orders depends on table users
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

-- 正确：按依赖顺序先删子表，再删父表
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS users;

DROP TABLE
DROP TABLE
DROP TABLE

第 9 步：清理归档表

DROP TABLE IF EXISTS orders_archive_2026h1;
DROP TABLE IF EXISTS bloat_demo;

DROP TABLE
DROP TABLE

注意事项

• DROP TABLE ... CASCADE 会连锁删除其他表。 它不只删依赖的视图，也可能删掉整张引用了它的表。

-- 危险！orders 与 order_items 都会被删
DROP TABLE users CASCADE;

执行前务必先用 pg_depend 确认影响范围：

-- 查出会级联删除哪些对象
SELECT dependent.relname AS would_be_dropped
FROM pg_depend d
JOIN pg_rewrite r ON r.oid = d.objid
JOIN pg_class dependent ON dependent.oid = r.ev_class
WHERE d.refobjid = 'users'::regclass;

• TRUNCATE 需要 ACCESS EXCLUSIVE 锁，会阻塞该表所有查询。 生产环境清空大表前先设锁超时。

SET lock_timeout = '3s';
TRUNCATE TABLE big_table RESTART IDENTITY;
RESET lock_timeout;

• TRUNCATE 不触发 ON DELETE 触发器。 依赖触发器做审计、级联、通知的系统会静默失效。

-- 该触发器在 TRUNCATE 时不会执行
CREATE TRIGGER trg_audit_delete AFTER DELETE ON orders
  FOR EACH ROW EXECUTE FUNCTION log_delete();

• TRUNCATE 不是行级操作，因此不能带 WHERE。 想"删一部分"只能用 DELETE。

-- 错误！
TRUNCATE TABLE orders WHERE amount < 10;

ERROR:  syntax error at or near "WHERE"

• TRUNCATE ... RESTART IDENTITY 只重置序列，不清空其他表的引用数据。 外键引用需要 CASCADE 或同时清空。

• DELETE 后空间不会自动归还操作系统。 长期高频删除的表必须配套 autovacuum 与定期 pg_repack。

-- 检查表膨胀程度（近似估算）
SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

• VACUUM FULL 需要 ACCESS EXCLUSIVE 锁，且磁盘需要与表等量的空闲空间。 大表上切勿贸然执行。

-- 执行前先确认磁盘余量足够（表大小的 2 倍更安全）
SELECT pg_size_pretty(pg_total_relation_size('big_table')) AS need_free_space;

• VACUUM 不能在事务块内执行。

-- 错误！
BEGIN;
VACUUM orders;
COMMIT;

ERROR:  VACUUM cannot run inside a transaction block

• DROP DATABASE 不能回滚，也不能在连接到该库的会话里执行。

-- 错误！cannot drop the currently open database
DROP DATABASE demo;

• 表被其他会话占用时 DROP 会一直等待。 先断开连接，或使用 WITH (FORCE)（PostgreSQL 13+）。

DROP DATABASE demo WITH (FORCE);

• 删除大表可能引发 IO 抖动。 建议先 TRUNCATE 再 DROP，或先在低峰期重命名再延迟删除。

• TRUNCATE 会清空索引但保留定义。 索引文件也被替换，不需要额外重建。

• 删除表不会自动删除它依赖的序列。 serial 创建的序列会随表一起 drop（有 OWNED BY），但手工建的独立序列需要自己删。

-- 检查孤儿序列
SELECT c.relname AS orphan_sequence
FROM pg_class c
LEFT JOIN pg_depend d ON d.objid = c.oid AND d.deptype = 'a'
WHERE c.relkind = 'S' AND d.objid IS NULL;

最佳实践

• DROP 一律写 IF EXISTS，让清理脚本可重复执行。

DROP TABLE IF EXISTS orders, order_items, products CASCADE;

• DROP ... CASCADE 只在测试环境使用。 生产环境按依赖顺序逐张删除，或先用 pg_depend 查清影响面。

• 清空全表优先用 TRUNCATE 而非 DELETE，前提是确认没有依赖 ON DELETE 触发器的逻辑。

TRUNCATE TABLE orders RESTART IDENTITY CASCADE;

• TRUNCATE 一次列出所有相关表，避免多张表逐个加锁、也避免外键报错。

TRUNCATE TABLE order_items, orders, order_logs RESTART IDENTITY;

• 删表前先重命名，观察一个发布周期再真删。 这是最省心的"软删除表"方案。

ALTER TABLE orders RENAME TO orders_trash_20260924;
-- 观察数天无异常后
DROP TABLE orders_trash_20260924;

• 高频删除的表调快 autovacuum，避免死元组堆积。

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold    = 1000
);

• 大表空间回收用 pg_repack 而非 VACUUM FULL，前者只在切换瞬间持锁。

pg_repack -d demo -t orders

• 定期检查膨胀率，把 dead tuple 占比纳入监控告警。

SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS size,
       round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 1) AS bloat_pct
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 0
ORDER BY bloat_pct DESC NULLS LAST
LIMIT 10;

• 归档优先于删除。 用 CREATE TABLE ... AS SELECT 或 COPY TO 备份后再清理，删除是不可逆的。

CREATE TABLE orders_archive_2026h1 AS SELECT * FROM orders WHERE created_at < '2026-07-01';

• TRUNCATE ... CASCADE 只清空数据，不改表结构。 需要"重置整张表"时用它比 DROP + CREATE 更安全（视图、权限、注释都保留）。

• 在事务里包裹 DROP/TRUNCATE，出问题可整体回滚。

BEGIN;
TRUNCATE TABLE order_items, orders RESTART IDENTITY;
-- 校验业务无异常后
COMMIT;

• 删除生产数据前务必先备份，确认归档文件可用后再执行。

pg_dump -d demo -t orders -f orders_backup_20260924.sql

下一节

继续学习：CREATE TABLE AS 与 SELECT INTO

参考链接

• PostgreSQL 官方文档：DROP TABLE
• PostgreSQL 官方文档：TRUNCATE
• PostgreSQL 官方文档：VACUUM
• PostgreSQL 官方文档：日常清理（Routine Vacuuming）
• PostgreSQL 官方文档：磁盘使用情况监控