时间戳

首页 > 解决方案 > 如何归档一个巨大的 postgres 表？

sql - 如何归档一个巨大的 postgres 表？

问题描述

我们有一个名为 history 的 postgres 表，它几乎是 900GB，并且每天持续增加 10GB。微服务（购物车）正在访问此表。我们有 postgres 复制设置（一个 Master 和一个 Slave）。2 个微服务实例在生产中运行，其中 1 个实例使用主 postgres 连接为某些端点写入和​​读取数据，1 个另一个实例使用 postgres 从连接仅读取数据。

表定义：

id - uuid
data - jsonb column
internal - jsonb column
context - jsonb column
created_date - date
modified_date - date

现在在上表data和internal列中为每一行加载了大 json。我们已经得出结论，将dataandinternal 列清空将减少该表的总空间消耗。

问题：

  1. 如何归档这个巨大的桌子？（仅表示仅清理data和internal列）。
  2. 如何在没有零停机时间/性能下降的情况下实现这一目标？

截至目前测试的方法。

  1. 使用 pg_repack（这是迄今为止最好的主意，但这里的问题是一旦 pg_repack 完成，整个新表需要与从属实例同步，这会导致 WAL 开销）。
  2. 只需单独取消dataandinternal列 - 这种方法的问题只是增加了表大小，因为 postgres 遵循 MVCC 模式。
  3. 使用临时表并克隆数据
    • 创建一个 UNLOGGED 表 - historyv2
    • 将原始表中的数据复制到 historyv2 表中，不带dataandinternal
    • 然后将表切换到 LOGGED。（我想这也会导致 WAL 开销）
    • 然后重命名表。

你们能给我一些关于如何实现这一目标的指示吗？

Postgres 版本：9.5

标签： sqlpostgresql

解决方案

我总觉得像这样的问题将一些不同的想法混为一谈，这使得它们看起来比应该的更复杂。最小的性能影响是什么意思？生成大量 WAL 可能会增加文件 i/o 和 cpu 以及网络使用率，但在大多数情况下，它对系统的影响不足以产生面向客户端的影响。如果没有停机时间是最重要的事情，您应该专注于优化，而不是担心到达那里的过程是什么（在合理范围内）。

也就是说，如果我在你的鞋子里醒来，我会首先使用表继承为数据设置分区，以便将来可以更轻松地对数据进行分段和处理。（这不是完全必要的，但可能会使您将来的生活更轻松）。之后，我会编写一个脚本来慢慢地遍历旧数据并使用“已清空”数据创建新分区，交叉创建分区、删除数据和对主表进行清理。一旦它被自动化，你可以让它慢慢地或在下班时间搅拌，直到它完成。移动所有数据后，您可能需要对父级进行最后的重新打包或吸尘，但即使没有它也可能没问题。同样，这不是最简单的想法，可能不是最快的方法（如果你可以有停机时间），但最后，

推荐阅读