扩展PostgreSQL存储过程

我当前的PostgreSQL存储过程实现无法扩展，尽管该问题很容易分解为并行进程/线程。

设置

行为与约会平台非常相似的应用程序，即用户注册，输入几个配置文件详细信息，并根据这些详细信息完成与所有其他用户的匹配。这些详细信息最多可以汇总到60-70个属性（大多数为布尔值），这些属性存储在user_attributes表的用户记录中。因此，有一个大user_attributes表，由用户ID和属性组成（其他配置文件数据存储在单独的表中）。出于性能方面的考虑，选择了逐列方案，即防止进行其他查询以获取一个用户的所有属性。对于每个匹配项，都有一个按用户匹配的表，因此每个用户都有自己的表，该表由user_id，other_user_id，matching_score组成。

我们希望每个数据库实例最多有30万个用户，但是很有趣的是它可以扩展十倍，即最多300万个用户。除此之外，我们可以通过分布到其他数据库实例来进行扩展。但是，我们开始遇到约80k用户的可伸缩性问题。

问题

如前所述，由于性能方面的考虑，所有属性都被放到一个user_attributes表中，每个属性只有一列。我们创建了一个存储过程（create_user），将所有60-70个属性用作参数，在用户表中创建一条记录，然后开始从表中选择所有其他用户，包括其属性，user_attributes并开始计算匹配项得分，最终结果将被插入到新创建的UserXYZ_matches表中。

现在，我们进行测试以查看设置的性能（一次插入一个用户，直到达到300k用户），事实证明，大约有80k用户，我们的CPU成为了瓶颈。尽管测试机带有4核/ 8线程，但实际上只使用了一个。问题在于，每个其他用户的匹配需要花费很长时间（PL / pgSQL在这里执行得很差），但是核心问题是所有这些匹配都发生在一个CPU上。例如，与所有其他用户的匹配可以分为8个不同的操作，每个操作占用user_attributes表记录的1/8 ，执行匹配并将其插入到结果表中。我们可以优化性能不佳的PL / pgSQL，但是我不知道如何在其他CPU内核/线程之间分配工作。

其他资讯

请发表有关整个方法的建议作为评论。我真的很感谢关于总体上如何做得更好的建议，但不能作为对这个特定问题的解答。

所有用户匹配表都存储在一个表空间中，该表空间由跨几个磁盘的XFS和LVM条带支持。用户匹配表的数量（每个用户一个）似乎不是可伸缩性问题（正如我们首先想到的那样）。因此磁盘不是问题，特定的设置似乎可以覆盖大量的表。

对的调用/查询create_user应该是原子的，即基于事务。这是为了我们的测试运行，但对于最终产品并不一定要有严格的要求。

create_user过程基本上如下所示（太长，无法整体发布）：

CREATE OR REPLACE FUNCTION create_user(...)
    -- (1) input_user = INSERT INTO user_attributes VALUES (parameter0, parameter1, ...)
    -- (2) create userXYZ_matching_table
    -- (3) FOR row IN SELECT * FROM "user_attributes" WHERE "id" <> input_user."id" LOOP
    --        -- repeat for every attribute
    --        IF row.this_attribute = input_user.this_attribute THEN
    --           match := match + 1
    --        END IF;       
    --        -- finally
    --        INSERT INTO userXYZ_matching_table VALUES (input.user.id, row.id, match)
    --     END LOOP;
LANGUAGE PLPGSQL;

我知道高CPU使用率来自IF，ELSIF，END IF块的数量（60-70）。同样，这可以进行优化，但是仍然存在关于如何扩展这种存储过程的问题。

当前运行测试的服务器如下所示，它很好地说明了该问题：