PostgreSQL。可以并行运行更新查询吗？

Question

我有一个有 1000 万行的大表。我需要为每一行获取一些统计值。我有生成此值的函数，例如 GetStatistic(uuid)。此函数运行速度非常慢，结果值不经常更改，因此我在表中创建了列 Statistic，并且每天执行一次这样的查询:

UPDATE MyTable SET Statistic = GetStatistic(ID);

在选择查询中，我使用 Statistic 列而不调用 GetStatistic 函数。

问题是，我的生产服务器有 64 个 CPU 和大量内存，所以几乎所有的 DB 都可以缓存到 RAM，但是这个查询只使用一个 CPU，需要 2 或 3 个小时才能执行。

GetStatistic 函数使用表，该表在所有 UPDATE 查询执行期间都是不变的。我可以修改查询以使 postgre 使用所有可用的 CPU 同时为不同的行并行计算 GetStatistic 吗？

Answer 1

早于 10 的 PostgreSQL 版本在单个后端执行每个查询，这是一个具有单个线程的进程。一次查询不能使用多个 CPU。它在单个查询中可以实现的 I/O 并发性也有些受限，实际上只为位图索引扫描执行并发 I/O，否则依赖操作系统和磁盘系统进行并发 I/O。

PostgreSQL 10+ 支持 parallel query .在撰写本文时(PostgreSQL 12 版本)并行查询仅用于只读查询。并行查询支持可为某些类型的查询提供更高的并行度。

Pg 擅长并发加载许多较小的查询，这样很容易使您的系统饱和。它只是不能很好地为一两个非常大的查询充分利用系统资源，尽管随着为更多类型的查询添加并行查询支持，这种情况正在改进。

---

如果您使用的是没有并行查询的旧 PostgreSQL，或者您的查询还没有受益于并行查询支持:

您可以做的是将工作分成小块，然后将它们交给 worker 。你已经提到了这一点:

Can i modify query to get postgre to calculate GetStatistic in paralel for different rows simultaneously, using all avaliable CPUs?

有多种工具，例如 DBlink , PL/Proxy , pgbouncer和 PgPool-II旨在帮助完成此类工作。或者，您可以自己做，开始(比如说)8 个工作人员，每个工作人员连接到数据库并执行 UPDATE ... WHERE id BETWEEN ？ AND ? 具有非重叠 ID 范围的语句。一个更复杂的选择是让队列 Controller 向工作人员分发大约 1000 个 ID 的范围，UPDATE 然后请求一个新的范围。

请注意，64 个 CPU 并不意味着 64 个并发工作器是理想的。当涉及到写入时，您的磁盘 I/O 也是一个因素。如果您将 UPDATE 事务设置为使用 commit_delay 并且(如果您的业务对此数据的安全要求是安全的)synchronous_commit，您可以稍微降低 I/O 成本= 'off' 那么来自同步的负载应该会显着减少。尽管如此，最好的吞吐量很可能会在远低于 64 个并发工作人员的情况下实现。

很可能您的 GetStatistic 函数可以通过将其转换为可内联的 SQL 函数或 View 而变得更快，而不是它所处的循环繁重的过程 PL/pgSQL 函数此时此刻。如果您显示此功能，可能会有所帮助。