Postgres中唯一可扩展的删除操作是DROP TABLE

# Postgres 唯一可扩展的删除操作是 DROP TABLE — PlanetScale 来源：https://planetscale.com/blog/the-only-scalable-delete Tom Pang (https://planetscale.com/blog/author/tom) | 2026 年 6 月 11 日 与直觉相反，大规模 `DELETE` 操作会给数据库增加工作量。 根据经验，我们可以明确地说：最可扩展的 Postgres 数据删除策略都围绕着删除整个表。 小规模下，逐行 `DELETE` 没有问题。然而，大批量的 `DELETE` 操作并不会立即释放物理磁盘空间，会增加写入和复制开销，并且从长远来看不利于大规模行清理。 如果你的应用程序需要删除大量数据（即使是极少数情况），我们建议转向能够让你将其表达为 `DROP TABLE` 或 `TRUNCATE` 的 schema 设计。 让我们通过了解 `DELETE` 在 Postgres 中的工作原理来分析原因。 ## 删除有害 (https://planetscale.com/blog/the-only-scalable-delete#deletes-hurt) 当行发生变化时，Postgres 可以维护同一行的多个版本，以便不同的事务能够看到查询时该行的值。这是 Postgres 对“多版本并发控制”（MVCC）的实现，也是其设计的核心原则。 Postgres 在此做了刻意的权衡。它将修改和删除的行与当前行存储在一起，依靠事务 ID 和可见性映射来跳过“死元组”。之后，清理进程会过来说：“嘿，这个堆页面中的这些字节现在空闲了，你可以覆盖它们。” Postgres 表的更新与删除 删除操作也需要完全复制；它们仍然是写入工作，这意味着大规模 `DELETE` 可能会影响你的应用程序的其他写入操作，并导致它们等待 `DELETE` 复制完成（在同步和半同步复制模式下）。 值得注意的是，`DELETE` 甚至自动清理通常不会将数据返回给操作系统；它们仅表示“这些页面中的空间可以被覆盖”。这是 Postgres 有意为之的选择。它优化了 `DELETE` 工作负载与 `INSERT` 工作负载混合的场景，将空间释放回操作系统然后再请求回来的操作相对昂贵，应当避免。`VACUUM FULL` 可以实现这一点，但会长时间持有昂贵的锁。 Postgres 做出的另一个相关权衡是：执行 `DELETE` 时索引数据根本不会被触及；相反，读取索引的读取器必须解析“这个元组是否已死”。还有一种尽力优化的方法：如果索引扫描发现一个死行，它可以自行将该条目标记为已死。 总的来说，`DELETE` 实质上是“增加工作量”，而不是“完成工作”。如果你想了解更多关于 Postgres MVCC 的细节，请参阅《保持 Postgres 队列健康》 (https://planetscale.com/blog/keeping-a-postgres-queue-healthy)。 如果你对大量数据执行 `DELETE`，可以想象它会如何给每次读取查询和自动清理增加工作量。请注意，使用外键和 `CASCADE` 进行删除可能会导致单行删除删除千兆字节的数据，从而引发同样的问题。 ## 用 DROP 替代 DELETE (https://planetscale.com/blog/the-only-scalable-delete#drop-delete-for-drop) 相比之下，`DROP TABLE` 和 `TRUNCATE` 需要在表上持有重量级的 `AccessExclusiveLock`，但基本与数据大小无关。在物理层，它们直接移除操作系统中的文件，并扫除 Postgres 缓冲区缓存中与该表相关的页面。 在共享缓冲区较大的数据库上，这种扫除可能不那么微不足道，但它仅仅是元数据的扫除。Postgres 为每个 8KB 缓冲区保留一个小的固定大小头部（`BufferDesc`，填充至 64 字节），删除表时会扫描这些头部，而不是页面本身。每个 8KB 页面对应 64 字节，即缓存大小的 1/128：对于 128GB 的共享缓冲区，只需顺序扫描大约 1GB 的内存，这在现代硬件上非常快。 `DROP TABLE` 和 `TRUNCATE` 的扩展性远好于 `DELETE`。它们不会产生任何死元组、无清理债务、不给读取器增加工作量。它们立即向操作系统释放空间。 ## 一次性的高性能删除 (https://planetscale.com/blog/the-only-scalable-delete#a-performant-one-off-delete) 一个常见的需要删除大量数据的场景是“由于错误，我的表里充满了垃圾数据”。我们最近在内部一个可观测性工具中遇到了这种情况。一个 bug 导致该工具写入了数百万行我们想从数据库中删除的行。坏行具有旧的 `updated_at` 时间戳；任何具有较新时间戳的行都是需要保留的。只有几十万行需要保留；大部分数据都是垃圾。 针对这种情况，尤其是因为“锁住数据库几分钟”完全不是问题，我们实施了一次外科手术，依托 Postgres 的事务性 DDL： 1. `BEGIN` 2. 显式 `LOCK TABLE ... IN ACCESS EXCLUSIVE MODE` 锁定相关表；这可以阻止其他事务读取或写入该表，从而获得一致的数据。 3. 创建一个临时表来仅保留需要的数据，如下所示： `` CREATE TEMP TABLE temp_keep_big_table AS SELECT * FROM big_table WHERE updated_at >= '2026-04-01'; `` 4. `TRUNCATE big_table;` 5. `INSERT INTO big_table SELECT * FROM temp_keep_big_table;`。在我们的示例中，这在一个非常小的实例上处理了数十万行，耗时几分钟。 使用临时表 + 全表删除来移除行 这种一次性操作效果很好；唯一写入预写日志（WAL）的数据是重新插入 `big_table` 的行。 如果在 `TRUNCATE` 期间持有 `AccessExclusiveLock` 几分钟是不可接受的，请改用基于触发器的方法：将写入镜像到新表，然后通过原子重命名进行交换。 你还应该知道，这种更高级的操作大致就是 Postgres 扩展 `pg_squeeze`（`pg_repack` 的更新版本）所做的。`pg_squeeze` 用于优化已经存在严重膨胀的表。这篇博客文章实际上就是关于从一开始就防止膨胀。通过将 schema 设计为避免大规模批量 `DELETE`，`pg_squeeze` 就变得不那么必要了。 在需要保留的数据远大于需要丢弃的数据，但需要丢弃的数据仍然很大的情况下，典型的做法是在循环中执行许多孤立的批量删除，例如一次删除 10,000 行。这可以保持事务简短，避免锁积压，并能让你控制节奏，使自动清理跟上步伐。 ## 用于持续删除的 Postgres 分区 (https://planetscale.com/blog/the-only-scalable-delete#postgres-partitions-for-ongoing-deletes) 自版本 10 起，Postgres 就具有良好的分区支持。“父”表可以有“子”表，查询可以自动路由到这些子表。Postgres 支持多种分区方案；其中一种极其有用的是基于日期的分区，但还有许多其他方案可用。 分区可以将“大量 DELETE”的工作负载转变为“偶尔 DROP TABLE”的工作负载。例如，如果你有需要过期的历史数据，你可以为每天创建一个子分区，并设置定期进程来删除较旧的子分区（或使用 `pg_partman` 扩展）。 基于年龄的 Postgres 分区 你甚至可以更进一步。Postgres 的分区是递归的，因此你可以按 `LIST`（例如一个“可见行”分区）对顶层进行分区，然后按 `RANGE` 对“不可见”子表进行分区，以便过期旧数据。 ## 去执行 DROP 吧 (https://planetscale.com/blog/the-only-scalable-delete#go-forth-and-drop) 设计你的 schema 和应用程序，使大规模 `DELETE` 变成 `DROP` 或 `TRUNCATE`，可以极大地改善你的数据库。在某些情况下，它有助于降低读取查询延迟，减轻复制延迟峰值，并整体提升数据库健康度。