【PGCCC】DELETE很困难

您的数据库运行良好 - 直到一个简单的 DELETE 操作使其崩溃。哪里出了问题？虽然我们倾向于专注于优化 SELECT 和 INSERT 操作，但我们经常忽略 DELETE 的隐藏复杂性。然而，删除不必要的数据同样重要。过时或不相关的数据会使您的数据库膨胀、降低性能并使维护成为一场噩梦。更糟糕的是，在没有合理理由的情况下保留某些类型的数据甚至可能导致合规性问题。

乍一看，DELETE 命令似乎很简单。甚至PostgreSQL 文档也提供了简单的示例，例如：

DELETE FROM films WHERE kind <> 'Musical';
DELETE FROM films;

这些查询可能在您的开发机器上毫不费力地运行，因为那里只有几百条记录。但是当您尝试在生产环境中运行类似的 DELETE 时会发生什么，因为那里的数据集要大几个数量级？

在本文中，我们将揭示为什么 DELETE 操作需要仔细考虑并探讨如何有效地处理它们。

当您删除数据时究竟会发生什么？

乍一看，DELETE 查询似乎很简单。但是，一旦执行查询，就会发生一系列复杂的步骤：

1. 行识别：与 SELECT 操作类似，查询识别当前事务可见的行（考虑 MVCC）并检查锁。
2. 锁获取：数据库获取行级排他锁，以防止对目标行进行其他操作。
3. BEFORE DELETE 触发器：如果定义了 BEFORE DELETE 触发器，则会在此时执行该触发器。
4. 将行标记为已删除：行不是被物理删除，而是在当前事务中被标记为已删除，从而使它们对未来的查询不可见（取决于事务隔离）。如果表包含大量数据对象，则还必须涉及
   TOAST 表。
5. 索引更新：相应的索引条目也被标记为删除（如果适用）。
6. 级联操作：对相关表执行级联操作，例如 ON DELETE CASCADE。
7. AFTER DELETE 触发器：如果定义了 AFTER DELETE 触发器，则会执行该触发器。
8. 预写日志 (WAL)：更改首先在行级别记录在 WAL 中，然后是索引级别的更新。

仅当事务提交后，这些更改才会成为永久更改，并可供之后启动的事务查看。但是，即使此时，数据也不会被物理删除。这就是膨胀的产生原因。

在自动清理过程或手动 VACUUM 操作回收空间之前，“已删除”的数据会保留下来。这些剩余数据会导致膨胀，随着时间的推移，查询性能会降低。

现在的关键问题是 DELETE 是否真的是我们数据库所能承受的最困难的操作。答案是？很有可能。虽然 UPDATE 的复杂度接近，但它们的设计方式通常使其不那么具有挑战性：

1. UPDATE 通常仅修改有限数量的列，从而降低了索引更新的潜在数量。
2. 并非所有更新都会触发整行 (冷)
   更新，即旧行被标记为死行并创建新行。通过精心设计表和查询，您可以最大限度地减少这些情况。例如，使用固定长度的列更容易实现热 (仅堆元组)
   更新。
3. 与 DELETE 不同，UPDATE 不会触发级联操作 - 它们只涉及明确定义的触发器。

接下来是 AUTOVACUUM

当自动清理启动时（您确实希望它启动）——通常由死元组或表更改的数量阈值触发——需要大量工作来清理它们。让我们一步一步地分解：

• 该过程从扫描表开始。虽然它并不总是全表扫描，但自动清理会检查可见性图和可能存在死元组的页面。这可以逐步发生，甚至可以处理大型表 -
  只要您的自动清理设置允许它足够频繁地运行。
• 检查每个元组以确保它对于任何活动或待处理的交易不再可见。
• 通过可见性检查的死元组将被物理地从表中删除。
• 已删除元组的相应索引条目已被更新。
• 现在空的空间被标记为可在未来的 INSERT 或 UPDATE 操作中重新使用。
• 表统计数据会更新以反映当前状态，从而帮助查询规划者做出更好的决策。
• 这些更改（包括元组删除和索引更新）都记录在预写日志 (WAL) 中，以实现持久性和复制。
• 如果表具有 TOAST 数据（大对象），则会处理相关的 TOAST 表。
• 可见性图已更新，将清理的页面标记为再次完全可见。
• 自动清理会重置阈值来确定下一次清理操作何时发生。

此过程持续进行，直到达到配置的清理成本限制（在自动清理的情况下），此时会暂停或停止。虽然自动清理有助于控制数据库，但很明显，回收死元组并非易事 - 这强调了为什么 DELETE 操作会对数据库性能产生持久影响。

虽然这AUTOVACUUM听起来是个坏消息，但如果没有它，你的数据库很快就会因死元组而变得臃肿，导致性能下降、查询速度变慢、存储使用量增加，甚至可能出现磁盘不足错误，因为未使用的空间无法回收。

进一步考虑

对于看似简单的 DELETE，已经完成了大量的工作，但复杂性还不止于此。DELETE 操作可能会带来额外的挑战，尤其是在涉及复制、资源争用或操作规模时。

在复制到热备用服务器或副本的环境中，DELETE 操作对时间更加敏感。在将相应的 WAL（预写日志）记录写入备用服务器的磁盘之前，事务无法完成。这是在高可用性设置中保持数据一致性的基本要求，其中通常至少涉及一台备用服务器。此外，如果备用服务器正在积极地处理读取操作，它必须在确认更改之前考虑 DELETE，这可能会带来进一步的延迟。

DELETE 操作的大小也起着至关重要的作用。小型 DELETE（例如删除一行）的影响往往很小。但是，随着操作规模的扩大，生成的 WAL 记录量也会增加。大型 DELETE 可能会使系统不堪重负，减慢事务速度并使复制过程变得紧张。备用服务器必须更加努力地处理传入的 WAL 流，如果其吞吐量不足，这可能会成为性能瓶颈。

资源争用又增加了一层复杂性，特别是对于大型 DELETE 而言。生成 WAL 记录、处理常规事务工作负载以及运行后台进程会共同导致系统 I/O 饱和。这会导致 CPU 和内存资源竞争，从而导致整体操作速度变慢。

最后，一旦数据被标记为删除，自动清理过程最终必须介入以物理方式删除它。这带来了一系列挑战，因为自动清理必须处理相同的资源争用和 I/O 需求，从而加剧了初始 DELETE 操作的整体影响。

软删除不是解决方案

软删除似乎是一种避免传统 DELETE 操作复杂性的简单方法。毕竟，更新字段deleted_at很简单，不太可能触发冷更新。然而，软删除并不是真正的数据删除机制，而且它们本身也存在一些复杂性。

虽然软删除可以提供一种简单的方法来实现“撤消”功能，但它们引发了有关数据一致性的严重问题。例如，您是否仅将主要实体标记为已删除，还是还将状态级联到引用表中的所有相关记录？如果级联不当，数据库可能会处于不一致的状态，从而难以维护数据完整性。

软删除也需要您在应用程序逻辑中加以考虑。每个查询都必须包含适当的过滤器以排除“已删除”的行，这会使查询设计复杂化并增加疏忽的风险。一个漏掉的过滤器可能会暴露本不应再显示的数据，从而导致潜在的安全或业务逻辑问题。

最后，软删除并不能解决问题 - 它们只是将问题推迟。数据仍然在您的数据库中，消耗存储空间，并可能导致性能随着时间的推移而下降。迟早，您需要处理这些数据的实际删除，这会让您再次面临 DELETE 带来的相同挑战。

在撰写本文时，我们只能推测 PostgreSQL 18 中对时间 PRIMARY KEY 和 UNIQUE 约束的支持将在未来多大程度上改变平衡。但考虑到该功能的复杂性，我目前还不敢下定论。

答案是批量处理

在处理 DELETE 等操作时，让 PostgreSQL 有时间处理和跟上大规模更改至关重要。这里的核心问题是事务的持续时间和规模。事务越短，所做的更改越少，PostgreSQL 管理和协调这些更改的效果就越好。这一原则适用于所有数据库操作，并强调了将单个事务的影响降至最低的重要性。

虽然您可以优化某些方面，例如行标识（使用索引、聚类或类似技术），但较大的数据集需要更具战略性的方法 - 批处理。例如，在单个事务中删除 100 万行是教科书式的不该做的事情。相反，将操作拆分为较小的批次（例如在 100 次迭代中删除 10,000 行）会更有效。

这种方法会比执行一次大规模 DELETE 操作更快吗？可能不会，特别是如果您在批处理之间包含等待时间以允许 PostgreSQL 处理其他工作负载。但是，这种权衡是值得的。通过批处理，您可以为 PostgreSQL 提供更多的喘息空间来管理更改，而不会压倒常规事务工作负载 - 当然，除非您为该操作安排了专门的维护时间。

如何批量删除

批量执行 DELETE 操作的最简单方法是使用子查询或通用表表达式 (CTE) 来限制每次迭代中受影响的行数。例如，不要像这样执行批量 DELETE：

DELETE FROM films WHERE kind <> 'Musical';

您可以将操作拆分成更小的块。使用类似下面的查询，您可以重复删除可管理批次中的行（例如，在 psql 中使用 \watch 自动执行迭代）：

DELETE FROM films
WHERE ctid IN (
    SELECT ctid FROM films
    WHERE kind <> 'Musical'
    LIMIT 250
);

本例中使用的ctid是 PostgreSQL 系统列，它为每行提供唯一标识符。通过ctid在子查询中选择值，您可以限制每次迭代中受影响的行数。这种方法比LIMIT直接在主查询中使用更有效，因为它避免了为每个批次重新扫描表的需要。

如果您感觉不舒服ctid（这可能值得单独写一篇文章）您可以使用主键和的常规查找LIMIT。

规划 Autovacuum

仅使用批处理并不能直接解决自动清理赶上更改的问题。您需要单独规划。调整自动清理设置或触发手动VACUUM运行VACUUM ANALYZE可以帮助管理 DELETE 过程中产生的膨胀。但是，除非您已仔细规划了整个批处理操作中的手动维护，否则很少建议禁用自动清理。跳过此步骤可能会留下影响性能的膨胀，这将需要以后付出更多努力来解决。

通过分区删除全部数据

自然分段的数据（例如按创建时间）使其成为通过分区删除的最佳选择。分区允许您通过简单地删除或截断相关分区来完全绕过 DELETE 操作。这种方法效率更高，避免了扫描、锁定和将行标记为已删除的开销，从而有效地消除了膨胀问题。

虽然分区增加了模式设计和查询规划的复杂性，但它可以为 DELETE 繁重的工作负载提供显著的性能优势，尤其是与自动分区管理相结合时。

结论

DELETE 操作通常会带来令人不快的意外 - 不仅会影响性能和造成膨胀，还会在我们最意想不到的时候反击。为了有效地处理它们，请专注于批处理、监控自动清理或利用分区处理大型数据集等策略。通过在架构设计期间考虑 DELETE 操作，您可以维护高效的数据库，减少维护麻烦，并确保它在数据增长时继续平稳运行。
#PG证书#PG考试#PostgreSQL培训#PostgreSQL考试#PostgreSQL认证

作者：Radim Marek
原文链接：https://notso.boringsql.com/posts/deletes-are-difficult/

原文地址：https://blog.csdn.net/PGCCC/article/details/144257549

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！