performance - 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 中的一些参数到甚至移动到另一个数据库的任何东西(我想非关系数据库会更适合这个特定的表，但我没有太多经验)。

Answer 1

我会查看表格的 FILLFACTOR。默认情况下它设置为 100，您可以将它降低到 70(开始)。在此之后，您必须执行 VACUUM FULL 来重建表。

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

这使 UPDATE 有机会将行的更新副本放置在与原始页面相同的页面上，这比将其放置在不同的页面上效率更高。或者，如果您的数据库已经从许多以前的更新中变得有些碎片化，那么它可能已经足够备用了。现在您的数据库也可以选择执行 HOT updates ，假设您正在更新的列不涉及任何索引。