PostgreSQL在具有数组和大量更新的大表上运行缓慢

Question

我有一张相当大的表格（20M条记录），其中有3列索引和一个数组列。每天都会对所有行进行数组列的更新（追加新值）。也会有插入操作，但更新操作比插入操作多。

数组中的数据表示与三个键对应的每日测量值，类似于这样：[[date_id_1, my_value_for_date_1], [date_id_2, my_value_for_date_2]]。它用于绘制这些每日值的图形。比如说我想要可视化关键字（a,b,c）随时间的变化情况，我会执行SELECT values FROM t WHERE a = my_a AND b = my_b AND c = my_c。然后我使用values数组来绘制图形。

随着时间推移，更新操作（每天批量执行一次）的性能明显恶化。

使用的是PostgreSQL 8.3.8。

你能给我一些提示去寻找解决方案吗？这可能包括调整postgres中的某些参数，甚至转移到另一个数据库（我猜非关系型数据库更适合这个特定的表格，但我没有太多的经验）。

- ibz

---

http://archives.postgresql.org/pgsql-performance/ - Milen A. Radev

@Milen 感谢你的提示。我是 StackOverflow 的忠实粉丝，相比专门的邮件列表或论坛，我更喜欢它。虽然我同意那些仍然有它们的位置，如果我在 SO 上找不到帮助，我肯定会去那里。 - ibz

请参见 https://dev59.com/TXA75IYBdhLWcg3wUHSQ。 - rogerdpack

Answer 1

我建议检查表的FILLFACTOR参数。默认情况下，它设置为100，您可以将其降低到70（首先）。然后，您需要执行VACUUM FULL命令以重建表。

ALTER TABLE tablename SET (FILLFACTOR = 70);
VACUUM FULL tablename;
REINDEX TABLE tablename;

这样可以让 UPDATE 有机会将更新后的行与原始行放在同一页上，这比放在不同页面上更有效率。或者，如果您的数据库已经因为许多先前的更新而有点碎片化，那么它可能已经足够稀疏了。现在，假设您正在更新的列不涉及任何索引，您的数据库还可以选择执行HOT 更新。

- Frank Heikens

---

性能下降可能会发生，因为记录的新版本（由于更新）将放置在不同的页面上。当您有大量记录时，您也将拥有许多页面。将新版本远离原始版本，将影响查询计划。请使用EXPLAIN查看发生了什么。还要考虑使用CLUSTER，将记录存储在与索引存储其信息相同的顺序中。您必须调整填充因子，更新的记录必须保持靠近原始记录。 - Frank Heikens

此外，随着数组的增大，单个页面上可以拥有的元组数量将会减少。它将开始使用 TOAST 存储来处理大型数组，然后您将再次处于将数据存储在外部表中的位置，但添加元素的过程将变得更加昂贵。 - araqnid

+1 对于“填充因子”，定期进行“集群”操作。升级到8.4版本也可能有所帮助。 - Tometzky

我明白了，但是我希望能得到您的确认。 :) 因此Vacuum实际上会清除死行。问题出现是因为我进行了批量更新，这不给Vacuum执行它的工作的机会。因此，填充因子（比如50）可以让批量更新在同一页上放置更新，然后Vacuum在更新之后清理它们。 - ibz

解决了问题，将我每天从9点到1点所需的全部4M更新批次带来了。 \ o / 我做了类似于聚簇的操作，但是是手动完成的。 聚簇不仅锁定表，而且使用了很多资源。 因此，我简单地创建了另一个具有相同结构的表，并按我想要的顺序插入记录（INSERT INTO ... SELECT FROM ... ORDER BY a、b、c），并确保我的更新按照物理磁盘上现在的顺序（a、b、c）进行。 在解决这个问题的过程中学到了很多东西。谢谢大家！ - ibz

Answer 2

不确定数组是否适用于这里。

为什么不将它们存储在单独的表中（每行一个值加上键），然后您的批量更新将是纯插入操作。

- James Anderson

Answer 3

问题出在更新操作上。将架构从基于数组改为每天多行，性能问题就会消失。

你可以稍后使用某种cronjob向数组中添加汇总数据，但要避免更新操作。

- user80168

---

如果我需要从头开始构建数组（对于每个a、b、c的组合，遍历每一天），那么将rollups转换为数组会非常慢。 - ibz

Answer 4

一个3列索引并不需要担心，这并不一定会使它变得更慢。但是，数组列确实可能是问题所在。您说您每天都向该数组列追加值。通过追加，您是指向表中的所有2000万条记录追加值吗？还是只有一些记录？

情况对我来说并不完全清楚，但我建议考虑摆脱那个数组列的方法。例如，将其作为单独的表。但是，这取决于您的情况，可能不是一个选项。 也许只有我会这样想，但我总觉得在我的某个表中有这样的列很“不干净”。大多数时候，使用其他解决该数组列所解决问题的更好的方法。话虽如此，在某些情况下，这样的列是有效的，但目前我想不出任何情况。特别是在具有2000万条记录的表中。

- pyrocumulus

---

我正在将元素附加到某些数组中，而不是全部的2000万个。我曾经有一个不同的表格，但那会使它变得非常庞大，性能也更差。决定去规范化并将数据存储在这些数组中，这对于选择操作有了很大的改进，并且一开始并没有恶化更新（尽管似乎随着时间的推移更新变得更糟）。 - ibz

你能否详细解释一下表格和数组中数据的性质以及它们之间的关系？也许还有更好的解决方案 :) - pyrocumulus

添加了更多细节。请查看第二段。不确定它是否真的很重要。 - ibz