postgresql - 数组的重复 Postgres 更新导致膨胀？

Question

我正在运行一个 Python 脚本，该脚本处理许多不同指标的时间序列数据，然后将结果写入 Postgres 数据库。

时间序列假设有 40 个时期，在数据库中存储为 real[40] 数组列。

当一次将所有 40 个时期的输出写入表格时(对所有行进行批量更新)，一切似乎都运行良好。即

UPDATE my_table SET
  arr_col_1 = {1, 2, 3, ... 40},
  arr_col_2 = {1, 2, 3, ...40},
  ...
  arr_col_90 = {1, 2, 3, ...40};

然而，将各个时期的结果迭代写入数组中的每个位置似乎会占用硬盘驱动器上的所有可用空间，例如

UPDATE my_table SET
  arr_col_1[1] = 1,
  arr_col_2[1] = 1,
  ...
  arr_col_90[1] = 1;

UPDATE my_table SET
  arr_col_1[2] = 2,
  arr_col_2[2] = 2,
  ...
  arr_col_90[2] = 2;

-- repeat x 38 more times

迭代策略的原因是为了容纳更大数量的行，为此 40 个 epoch 的结果不能同时放入内存。

据我所知，UPDATE 查询在某些情况下会删除和重写行数据，但我不清楚这种情况何时发生以及这可能与数组有何关系。有没有办法在不导致数据库膨胀的情况下迭代更新大量行的数组？

Answer 1

正如其他人正确提到的，这种方法不太适合 PostgreSQL 的操作模式。

但是，您可以使用称为 HOT 的优化:

• 使用小于 100 的 fillfactor 声明您的表，以便 INSERT 在每个 block 中留出可用空间:

  ALTER TABLE my_table SET (fillfactor = 50);
  

  此设置仅影响 future 的事件，您必须重新组织表才能影响现有数据。如果您更新表中的每一行，您可能需要低至 30 的设置才能生效。

• 确保更新的列上没有有索引。

然后 PostgreSQL 可以使用“热更新”并动态回收死表条目，这避免了对 autovacuum 的需要，这显然无法跟上您的表。

检查表的 pg_stat_user_tables 行中的 n_tup_hot_upd 列，看它是否正常工作。