时间戳

首页 > 解决方案 > Postgres从基于非索引列的大表中提取数据

sql - Postgres从基于非索引列的大表中提取数据

问题描述

我们有一个生产表已经存在了很长一段时间，并且该表的容量很大（接近 3 TB），因为该表中的大部分数据都是陈旧且未使用的，我们计划删除历史数据没有任何参考。有一个布尔类型的“活动”列，我们可以使用它来删除这些数据，但是该列没有被索引。考虑到表的体积，我不太确定创建新索引是否会有所帮助，我尝试一次增量删除 100K 的非活动行，但体积仍然很大，需要几个月才能清除向上。表的主键是 UUID 类型，我想创建一个新表并只插入带有 active="true" 的值作为

insert
    into
    mytable_active
        select
            *
        from
            mytable
        where
            is_active = true;

但正如预期的那样，这种方法也因为体积而失败，并且会一直运行下去。

任何建议方法都将受到欢迎。

标签： sqlpostgresqlamazon-rdsrdbms

解决方案

当您需要快速删除大量行时，分区很棒......当表已经分区时。

如果您需要的列上没有索引，则至少需要一次全表扫描，除非您可以使用另一个索引（如“日期”）或其他索引来缩小范围。

我的意思是，你可以创建一个索引“WHERE active”，但这也需要你试图避免的全表扫描，所以......嗯。

首先，删除。只是不要，即使是有限的小部分。它不仅会写入大部分表（3TB 写入），还会将其写入 WAL（再增加 3 TB），还会更新索引，并将其也写入 WAL。这将花费很长时间，并且来自索引更新的随机 IO 会影响您的性能。如果它完成了，你仍然会有一个 3TB 的文件，其中大部分是未分配的。加索引。

所以，没有删除。呃，等等。

  • 使用 DELETE 的场景：

用视图“SELECT * FROM humongous WHERE active=true”交换表，并在视图上添加触发器或规则以将更新/插入/删除重定向到基础表。确保触发器将所有新行设置为 active=true。

重新创建除主键之外的每个索引（同时），添加“WHERE active=true”。这将需要对第一个索引进行全表扫描，即使您在“活动”上创建索引也是如此，因为当指定 WHERE 时，CREATE INDEX WHERE 似乎无法使用另一个索引来加速。

删除旧索引

请注意，视图的目的只是确保绝对所有查询在 WHERE 中都具有“active=true”，否则它们将无法使用我们刚刚创建的条件索引，因此每个查询都将是全表扫描，这是不可取的。

现在，您可以使用您的 DELETE 一点一点地删除delete from mytable where id in ( select id from mytable where active = false limit 100000);

这是一个权衡，您将进行大量表扫描来重新创建索引，但您将避免由于大量删除而导致索引更新的随机 IO，这就是您说这需要几个月的真正原因。

  • 使用 INSERT INTO new_table SELECT 的场景...

如果你在这个巨大的表上运行了插入和更新，那么你就有问题了，因为在操作过程中这些不会被转移到新表中。所以一个解决方案是：

  • 关闭所有运行长查询的脚本和服务
  • 锁定一切
  • 创建新表
  • 将 huge_table 重命名为 huge_old
  • 创建一个由 huge_table 和 huge_old 组成的 UNION ALL 视图。从应用程序的角度来看，这个视图取代了huge_table。它必须处理优先级，即如果新表中存在一行，则应忽略旧表中存在的具有相同 id 的行......所以它必须有一个 JOIN。此步骤应事先仔细测试。
  • 开锁

然后，让它运行一段时间，看看视图是否不会破坏你的性能。此时，如果它坏了，您可以通过删除视图并将表重命名回原来的自己来轻松返回。我说过要关闭所有运行长查询的脚本和服务，因为这些可能会在视图中失败，并且您不想在运行长查询时获得大锁，因为这将停止一切直到它完成。

  • 在视图上添加插入/更新/删除触发器以将写入重定向到 new_table。插入直接进入新表，更新必须传输行，删除必须同时命中两个表，并且 UNIQUE 约束将......有趣。这会有点复杂。

现在传输数据。

即使需要一段时间，谁在乎？它最终会完成。我想如果你有一个 3TB 的表，你必须有一些不错的存储空间，即使那是我们用来放置数据的这些旧的旋转设备，如果 IO 不是随机的，它应该不会超过几个小时。所以想法是只使用线性IO。

手指交叉希望该表没有存储在单独的 TOAST 表中的大文本列，该表将需要每行一次随机访问。你检查过了吗？

现在，您实际上可能希望它运行更长时间，以便它使用更少的 IO 带宽，用于读取和写入，尤其是 WAL 写入。只要不降低其他用户的性能，查询运行多长时间都没有关系。

Postgres 可能会进行并行表扫描以使用盒子中的所有内核和所有 IO，因此可能首先禁用它。

然后我认为你应该尽量避免这种滑稽的（对于旁观者）场景，它从表中读取半天，没有找到任何匹配的行，所以磁盘处理读取就好了，然后它找到所有匹配的行最后并继续将 300GB 写入 WAL 和目标表，导致巨大的写入争用，当你知道时你必须 Ctrl-C 它，你只是在你的直觉中知道它已经接近完成。

所以：

 create bogus_table just like mytable but without indices;
 insert into bogus_table select * from mytable;

10% 的“活动”行仍然是 300GB，因此更好地检查服务器是否可以处理写入 300GB 表而不会减慢速度。观察 vmstat 并检查 iowait 是否发疯，观察每秒事务数、查询延迟、Web 服务器响应能力，以及通常的数据库健康状况。如果电话响了，请按 Ctrl-C 并说“已修复！”

完成几个检查点后，Ctrl-C。是时候做真正的事情了。

现在要使此查询花费更长的时间（因此破坏更少的 IO 带宽），您可以将其添加到您选择的列中：

pg_sleep((random()<0.000001)::INTEGER * 0.1)

这将使它平均每百万行休眠 0.1 秒。在查看 vmstat 时进行调整以适应口味。

您还可以使用hacks监控查询进度。

它应该可以正常工作。

一旦从被诅咒的表中提取了有趣的行，您就可以将旧数据移动到数据仓库或其他东西，或冷存储，或者如果您想运行一些分析，可以将其加载到 clickhouse 中。

也许在新表恢复到 3TB 之前对新表进行分区也是一个好主意。或定期移动旧行。

现在，我想知道你如何备份这个东西......

- 编辑

好的，我有另一个想法，也许更简单，但你需要一个盒子。

获取具有快速存储和设置逻辑复制的第二台服务器。在这个副本服务器上，创建一个巨大表的空 UNLOGGED 副本，主键上只有一个索引。逻辑复制会复制整个表，所以需要一段时间。原始服务器中的第二个网卡或一些 QoS 调整将有助于不破坏您实际用于服务查询的以太网连接。

逻辑复制是基于行的，并通过主键标识行，因此您绝对需要在从属设备上手动创建该 PK 索引。

我现在已经在我的家用盒子上对其进行了测试，效果很好。最初的数据传输有点慢，但那可能是我的网络。暂停然后恢复复制传输在暂停期间在主服务器上插入或更新的行。但是，重命名表似乎会破坏它，因此您将无法执行 INSERT INTO SELECT，您必须在副本上删除。对于 SSD，只有一个 PK 索引，表设置为 UNLOGGED，它不应该永远存在。也许使用 btrfs 会将随机索引写入 IO 转换为线性 IO，因为它具有写入时复制的性质。或者，如果 PK 索引适合 shared_buffers，只需 YOLO 并将 checkpoint_timeout 设置为“7 天”，这样它实际上不会写入任何内容。您可能需要分块进行删除，以便复制的更新跟上。

当我删除 PK 索引以加快删除速度，然后在重新启用复制之前重新创建它时，它没有赶上更新。所以你不能删除索引。

但是有没有办法只传输您想要保留的行而不是传输所有内容并删除，同时还让副本跟上主服务器的更新？...可以为插入执行此操作（只需禁用初始数据复制) 但不幸的是，不适用于更新。您需要一个整数主键，这样您就可以在副本上生成虚假行，然后在复制期间更新这些行......但是您不能使用 UUID PK 来做到这一点。

反正。完成此操作后，将要保留在主服务器上的 WAL 段数设置为非常高的值，以便稍后恢复复制而不会丢失更新。

现在您可以在副本上运行您的大删除。完成后，清理，可能是 CLUSTER，重新创建所有索引等，并将表设置为 LOGGED。

然后您可以故障转移到新服务器。或者，如果您喜欢冒险，您可以将副本的表复制回主服务器，因为它应该在另一个模式中具有相同的名称。

这应该允许非常少的停机时间，因为所有更新都被复制，副本将始终是最新的。

推荐阅读