Vertica 数据删除最佳实践¶
原文：Best Practices for Deleting Data
适用版本： 本文基于 Enterprise Mode 编写。Eon Mode 无 WOS 概念（数据写入 depot 后持久化到公共存储），moveout 机制不适用，但 delete vector、PURGE、TRUNCATE、DROP PARTITION、
HistoryRetentionTime、PurgeMergeoutPercent等核心机制在两模式下通用。
Vertica 删除数据的特殊性¶
Vertica 是一款高性能列存分析型数据库。在数据删除方面，Vertica 与传统数据库有两个关键区别：
- DELETE 并不真正从磁盘删除数据 — 它只是将行标记为已删除（mark as deleted），使这些行仍能被历史查询（historical queries）检索到
- UPDATE 执行两步操作 — 先写入新数据，再将旧数据标记为删除
具体来说，DELETE 语句并不从物理存储中移除数据，而是创建一条 delete vector，其中记录了：
- 被删除记录在 ROS 容器中的位置
- 删除提交时的 epoch 号
常见删除场景¶
- 需要定期清理历史数据
- 需要更新或删除误加载的数据
- 需要清除 staging / 临时表
删除类型对比¶
原文列出了五种删除方式，各有适用场景。
版本说明： WOS / Direct ROS 的加载方式区分仅适用于 Enterprise Mode。Eon Mode 无 WOS，数据直接写入 depot 后持久化到公共存储。
| 删除类型 | 推荐加载方式 | 可回滚 | 性能 | 使用建议 |
|---|---|---|---|---|
| 单行删除 | WOS | 是 | 取决于 projection 设计 | 应在 WOS 中执行，这样 moveout 时多个已删除行会被合并为一个 delete vector |
| Trickle Load（高频小批量） | WOS | 是 | 取决于 projection 设计 | 适用于频繁间隔的小批量操作。在 WOS 中执行，moveout 时合并为一个 delete vector |
| Bulk Delete（批量删除） | Direct ROS | 是 | 取决于 projection 设计 | 推荐方案 — 对每个含有待删除数据的 ROS 容器只创建一个 delete vector |
| Drop Partition（删除分区） | N/A | 否 | 快（catalog 操作，存储后台清理） | 推荐用于清理历史数据。执行前会强制执行 moveout，确保属于待删除分区的 ROS 数据先被移入 |
| Truncate（截断表） | N/A | 否 | 快（catalog 对象变更，存储后台清理） | 移除表的所有存储，但保留表定义和 projection 定义。适合清空表内容 |
永久删除数据¶
为了让磁盘空间可重用，Vertica 允许将已删除数据从数据库物理存储中永久移除。已删除数据会从 ROS 容器中被永久清除。你可以通过以下方式控制删除：
- 让 Tuple Mover 自动清理
- 手动清理
AHM 的作用¶
AHM（Ancient History Mark） 决定数据何时被永久删除。AHM 是一个 epoch，代表历史数据被保留到的时间点。任何早于 AHM 的历史数据都可以被永久移除。
Purge 策略配置¶
设置 purge 策略来决定哪些删除数据可以被 Tuple Mover 自动清理：
方法 1（推荐）：使用 HistoryRetentionTime
指定已删除数据的保留时长（秒）。这是确定哪些删除数据可以被 purge 的首选方法。设为 -1 禁用：
方法 2：使用 HistoryRetentionEpochs
先禁用 HistoryRetentionTime，再设置保留的历史 epoch 数量：
=> SELECT SET_CONFIG_PARAMETER('HistoryRetentionTime', '-1');
=> SELECT SET_CONFIG_PARAMETER('HistoryRetentionEpochs', '{ <num_epochs> }');
方法 3：使用 PurgeMergeoutPercent
指定某 ROS 容器中已删除行占比达到该百分比后才触发永久删除。以下示例设为 20%：
Mergeout 的行为取决于是否分区：
- 非分区表： Mergeout 会永久删除所有 ROS 容器中满足
PurgeMergeoutPercent阈值的已删除数据 - 分区表： Mergeout 仅清理满足
PurgeMergeoutPercent阈值的非活跃分区（inactive partitions），不处理活跃分区
管理 Delete Vector 的策略¶
考虑到永久删除数据涉及的开销，应主动管理 delete vector 的数量。两条思路：用替代方案避免 DELETE，或执行批量删除。
替代 DELETE 的方法¶
1. TRUNCATE TABLE
删除表中所有数据，同时保留表定义和 projection 定义。TRUNCATE TABLE 移除 catalog 中的依赖关系，并在后台移除存储容器，不影响 Vertica 事务：
2. DROP PARTITION
将数据按日期字段分区，然后直接删除分区。DROP PARTITION 是 catalog 操作，移除 catalog 中的依赖关系，在后台移除存储容器——不会阻塞正在运行的事务。Vertica 在 DROP PARTITION 之前会先执行一次 Moveout。
如果只需删除分区中的部分数据，步骤如下：
- 创建与原表结构相同的 staging 表
- 将分区中需要保留的数据移到 staging 表
- 使用
SWAP_PARTITIONS_BETWEEN_TABLES函数交换分区（纯 catalog 操作，无数据移动） - 清理 staging 表
3. SWAP_PARTITIONS_BETWEEN_TABLES 示例
假设一个按月分区的表，需要因数据错误而重新加载上周数据并删除上周旧数据：
-- 1. 创建结构相同的 staging 表（包含 projection）
=> CREATE TABLE store.staging_store_orders_fact
LIKE store.store_orders_fact INCLUDING PROJECTIONS;
-- 2. 将需要保留的数据移到 staging 表（排除上周 2005-11-20 到 2005-11-27）
=> INSERT /*+ direct */ INTO store.staging_store_orders_fact
SELECT * FROM store.store_orders_fact
WHERE date_ordered BETWEEN '2005-11-01' AND '2005-11-19'
OR date_ordered BETWEEN '2005-11-28' AND '2005-11-30';
-- 3. 交换分区（catalog 操作，无数据移动）
=> SELECT SWAP_PARTITIONS_BETWEEN_TABLES(
'store.staging_store_orders_fact', 200511, 200511,
'store.store_orders_fact');
-- 4. 清理 staging 表
DROP TABLE store.staging_store_orders_fact;
执行 Bulk Delete¶
如果无法避免使用 DELETE，应尽量执行批量删除，而非多次单行删除。
关键原则： 在 WOS 中提交多行删除的数据。如果每次单独提交，每条语句都会创建独立的 delete vector，产生大量小型标记容器，每个容器都消耗资源并影响性能。
批量删除的做法：将删除谓词加载到临时表，一条 DELETE 语句完成。这样每个含删除数据的 ROS 容器只产生一个 delete vector。
-- 将待删除的 Employee ID 加载到临时表
=> CREATE LOCAL TEMP TABLE data_to_delete (emp_id INT);
CREATE TABLE
=> COPY data_to_delete FROM '/tmp/employee_to_delete.txt';
Rows Loaded
-------------
15
(1 row)
-- 一条语句批量删除（使用 DIRECT hint）
=> DELETE /*+ direct */ FROM store.store_orders_fact
WHERE employee_key IN (SELECT * FROM data_to_delete);
OUTPUT
--------
1740
(1 row)
-- 清理临时表
=> DROP TABLE data_to_delete;
DROP TABLE
手动管理 Delete Vector¶
监控 Delete Vector¶
Tuple Mover mergeout 在以下条件满足时会清除 delete vector：
- 删除发生在 AHM 之前
- 每个 ROS 容器的已删除行占比大于
PurgeMergeoutPercent（默认 20%） - 对于分区表，仅处理非活跃分区，不处理活跃分区
如果自动 mergeout 没有永久删除数据，则需要手动管理。以下查询用于检查 delete vector 和已删除行数（查询本地节点数据，避免对系统表造成负载）：
按 projection 查看：
=> SELECT schema_name
, projection_name
, count(*) num_ros -- ROS 容器数
, sum(total_row_count) num_rows -- 总行数
, sum(deleted_row_count) num_deld_rows -- 已删除行数
, sum(delete_vector_count) num_dv -- delete vector 数量
, (sum(deleted_row_count) / sum(total_row_count) * 100)::INT por_del_rows -- 已删除行占比
FROM storage_containers
WHERE node_name = (SELECT local_node_name())
GROUP BY 1, 2
HAVING sum(deleted_row_count) > 0
ORDER BY 5 DESC;
schema_name | projection_name | num_ros | num_rows | num_deld_rows | num_dv | por_del_rows
-------------+------------------------+---------+----------+---------------+--------+-------------
store | store_orders_fact_b1 | 60 | 200044 | 5636 | 62 | 3
store | store_orders_fact_b0 | 60 | 200210 | 5618 | 62 | 3
按分区查看：
=> SELECT p.node_name
, p.table_schema
, p.projection_name
, p.partition_key
, count(DISTINCT p.ros_id) num_ros -- 该分区 ROS 容器数
, sum(p.ros_size_bytes) used_bytes -- 该分区占用字节
, sum(p.ros_row_count) num_rows -- 该分区总行数
, sum(p.deleted_row_count) num_del -- 该分区已删除行数
, sum(delete_vector_count) cdv -- 该分区 delete vector 数
, (sum(sc.deleted_row_count)/sum(p.ros_row_count)*100)::int por_del_rows -- 已删除行占比
FROM partitions p
INNER JOIN storage_containers sc ON ros_id = storage_oid
WHERE p.node_name = (SELECT local_node_name())
AND sc.node_name = (SELECT local_node_name())
GROUP BY 1,2,3,4
HAVING sum(delete_vector_count) > 0
ORDER BY 10 DESC,2,3,4;
node_name | table_schema | projection_name | partition_key | num_ros | used_bytes | num_rows | num_del | cdv | por_del_rows
------------------------+--------------+------------------------+---------------+---------+------------+----------+---------+-----+-------------
v_utn_demo_node0001 | store | store_orders_fact_b0 | 200511 | 1 | 244467 | 9978 | 1664 | 9 | 50
v_utn_demo_node0001 | store | store_orders_fact_b1 | 200511 | 1 | 243471 | 9936 | 1650 | 9 | 50
v_utn_demo_node0001 | store | store_orders_fact_b0 | 200311 | 1 | 82143 | 3354 | 84 | 1 | 3
v_utn_demo_node0001 | store | store_orders_fact_b0 | 200407 | 1 | 82799 | 3380 | 90 | 1 | 3
...
三种处理方式¶
根据上述查询结果，从轻到重有三种处理策略：
策略 1：合并 Delete Vector（dvmergeout）
适用场景： delete vector 数量多（num_dv 高），但已删除行占比低（por_del_rows 低），且表无分区或删除均匀分布。
为什么不 purge 整表？因为 purge 整表意味着重写所有 ROS 容器（去除已删除行之后的数据集）。如果已删除行占比很小，重写整表的代价远大于收益。此时应减少 delete vector 数量以避免 ROS pushback。
Tuple Mover 的自动 mergeout 会合并两个或更多 ROS 容器为一个不含已删除行的容器。但是，如果每个 ROS 容器的 delete vector 占比小于 PurgeMergeoutPercent（默认 20%），mergeout 操作不会清理已删除记录。
使用 DO_TM_TASK 将多个 delete vector 合并为一个：
版本说明：
DO_TM_TASK('dvmergeout')适用于 Enterprise Mode。Eon Mode 的 Tuple Mover 行为有所不同（mergeout 在公共存储层面执行，无需手动触发 dvmergeout）。大量 DELETE 语句会创建许多小容器存放删除标记。每个容器消耗资源并影响性能。在 mergeout 周期中，Tuple Mover 会将这些删除标记容器合并为一个大容器。
策略 2：清理特定分区（PURGE_PARTITION）
适用场景： 某分区的 cdv 和 por_del_rows 显著高于其他分区。只清理该高删除占比的分区，而不是整表——因为 purging 整表会连那些只有少量删除行的 ROS 容器也一并重写，浪费 I/O。
-- 步骤 1：推进 AHM，使 delete marker 早于 AHM 从而可以被移除
=> SELECT MAKE_AHM_NOW();
-- 步骤 2：清理指定分区
=> SELECT PURGE_PARTITION('store.store_orders_fact', 200511);
策略 3：清理整表（PURGE / PURGE_PROJECTION）
适用场景： 已删除数据均匀分布在各分区，或是非分区表且已删除行过多。这是最后的选择。
-- 步骤 1：推进 AHM，使 delete marker 早于 AHM
=> SELECT MAKE_AHM_NOW();
-- 步骤 2：清理整表
=> SELECT PURGE('store.store_orders_fact');
-- 或者清理单个 projection
=> SELECT PURGE_PROJECTION('store_orders_fact_b0');
删除操作优化建议¶
- 优化 Projection 设计 — 在排序顺序末尾使用高基数列，或者确保删除谓词列包含在所有 projection 中
- 批量删除 — 将待删除数据插入临时表，用一条 DELETE 完成
- 达到阈值再 purge — 表中已删除行达到 20% 以上时再执行 purge
- 使用分区 — 将数据按时间等维度分块，可直接 DROP PARTITION 成批删除
- 清空表用 TRUNCATE — 而非 DELETE，避免产生 delete vector
- WOS 中提交多行删除 — 单行逐个提交会产生大量 delete vector，应在 WOS 中累积后统一提交
扩展阅读¶
- ROS Pushback 故障排查 — Delete Vector 过多导致 ROS pushback
- Tuple Mover 最佳实践完全指南 — Mergeout 中对删除数据的处理
- Vertica DELETE 相关问题 — DELETE 操作常见问题解答
- Vertica 数据库 Replay Delete 算法 — DELETE 操作的底层算法
- Vertica Epoch 机制详解 — AHM 与删除向量的清理
- 理解 Vertica 的分区 — 分区表与 DROP PARTITION 的原理