PostgreSQL表分区性能调优

Question

我正在解决一个基于PostgreSQL 9.6 dbo的系统的性能问题。简介：

这是一个类似银行系统的12年历史系统，其中最常查询的主表为transactions。

CREATE TABLE jrn.transactions (
     ID BIGSERIAL,
     type_id VARCHAR(200),
     account_id INT NOT NULL,
     date_issued DATE,
     date_accounted DATE,
     amount NUMERIC,
     ..
)

在表transactions中，我们存储了银行账户中的所有交易。字段type_id确定交易类型，也作为C# EntityFramework鉴别器列。值如下：card_payment，cash_withdrawl，cash_in等。已知14种交易类型。

通常有4种查询类型（第3和第4种查询最频繁）：

1.选择单个交易，例如：SELECT * FROM jrn.transactions WHERE id = 3748734 2.选择与其他交易联接的单个交易，例如：SELECT * FROM jrn.transactions AS m INNER JOIN jrn.transactions AS r ON m.refund_id = r.id WHERE m.id = 3748734 3.选择给定类型的0-100、100-200等交易，例如：SELECT * FROM jrn.transactions WHERE account_id = 43784 AND type_id = 'card_payment' LIMIT 100 4.多个聚合查询，例如：SELECT SUM(amount), MIN(date_issued), MAX(date_issued) FROM jrn.transactions WHERE account_id = 3748734 AND date_issued >= '2017-01-01' 在过去几个月中，我们的数据行数意外增长到了120M。

我们正在考虑对表进行分区，遵循PostgreSQL文档：https://www.postgresql.org/docs/10/static/ddl-partitioning.html 选项：

1.按type_id对表进行14个分区。

2.添加列year，并按year（或year_month）将表分为12（或144）个分区。

我现在正在将数据恢复到测试环境中，我将测试两个选项。

您认为对于这种情况最适合的分区规则是什么？还有其他选择吗？

感谢任何反馈/建议等。

- Luke1988

---

请注意，您正在参考Postgresql 10的文档，而您的服务器版本为9.6。 - EagleRainbow

在选项2中：由于查询4不包含“year”（或“year_month”），因此这也没有太大帮助。无论如何，查询3并未限制“date_issued”，因此这也不是最佳解决方案。 - EagleRainbow

我目前正在考虑使用物化视图（例如用于处理聚合查询4）。@Luke1988：jrn.transactions的更新/插入频率是多少，查询4中的sum(amount)准确性有多重要？ - EagleRainbow

抱歉，我只是凭记忆编写了该查询，就像伪代码一样。最大长度为49，因此列设置为varchar（50）。 - Luke1988

是的，因为它们是最重要的外键。结合其中一个日期字段，它们几乎构成了该表的自然键。 - wildplasser

Answer 1

分区对这些查询并没有帮助，除非您忘记了索引，因为它们不会执行顺序扫描。

我唯一看到分区的好处是如果您想要有效地删除旧行，则按日期进行分区最佳。

根据您的查询，除了主键索引之外，您应该有这些索引。

CREATE INDEX ON jrn.transactions (account_id, date_issued);
CREATE INDEX ON jrn.transactions (refund_id);

如果您可以牺牲一些插入性能以使第三个查询尽可能快（建议测试），则以下索引可能是一个好主意：

CREATE INDEX ON jrn.transactions (account_id, type_id);

- Laurenz Albe

---

谢谢！据我了解，由于账户ID，分区可能不会有所帮助？如果没有账户ID列（现在只是理论上），那么按type_id进行分区就有意义了吗？我可能可以直接将查询3重定向到这个分区 - SELECT * FROM jrn.transactions_cash_withdrawl LIMIT 100 - Luke1988

是的，如果您按 type_id 进行分区，它只会扫描该一个分区。但是，如果您有适当的索引并使用索引扫描，则与大表上的扫描相比，这并不会更便宜。分区仅有助于使用顺序扫描和大规模删除的计划。 - Laurenz Albe

Answer 2

你所面对的问题几乎是列存储的完美案例，可以使用SAP HANA数据库来实现。然而，由于你明确要求用Postgres来回答，而且我怀疑HANA数据库不会在预算范围内，我们必须坚持使用Postgres。

你的第三个和第四个查询方向有很大区别，因此你的问题不会有“单一答案”，你总是要在这两种用例之间做出平衡。然而，我会尝试使用两种不同的技术来分别处理它们。

在我看来，最大的问题是第四个查询，它在你的postgres服务器上产生了相当大的负载，只是因为它正在汇总值。此外，你只是一遍又一遍地汇总值，这很可能不会经常改变（甚至根本不会改变），因为你已经说过UPDATE几乎根本不会发生。我还假设两件事：

• transactions 仅支持 INSERT 操作，即几乎不会发生DELETE语句（除非在某些特殊管理干预的情况下）。
• 当INSERT时，date_issued列的值通常会接近于"今天" - 因此您通常不会将数据插入到很久以前。

基于这个原因，为了防止重复聚合计算，我将介绍另一个表：transaction_aggr，它是这样构建的：

create table transactions_aggr (
   account_id INT NOT NULL,
   date_issued DATE,
   sumamount NUMERIC,
   primary key (account_id, date_issued)
)

这将为您提供每日预先聚合值的表格。

要确定哪些值已经预聚合，我会在 transactions 中添加另一个布尔类型的列，该列告诉我哪些行包含在 transactions_aggr 中，哪些尚未包含。然后，查询 no. 4 必须更改为仅从 transactions 读取未预聚合的行，而其余部分可以来自 transactions_aggr。为了方便起见，您可以定义以下视图：

select account_id, date_issued, sum(amount) as sumamount from
    (
    select account_id, date_issued, sumamount as amount from transactions_aggr as aggr
    union all
    select account_id, date_issued, sum(amount) as amount from transactions as t where t.aggregated = false
    )
group by account_id, date_issued

毋庸置疑，在transactions.aggregated上建立索引（也许与account_id一起）可以极大地帮助提高性能。

可以使用多种方法来更新transactions_aggr：

1. 您可以将此作为一次性活动使用，仅预聚合当前约120m行的数据一次。这至少会显著减轻执行聚合操作时机器的负载。但是，随着时间的推移，您将再次遇到同样的问题。然后，您可以重新执行整个过程，只需删除整个transactions_aggr并从头开始重新创建它（所有原始数据仍在transactions中）。

2. 您可能有一个很好的时间段，在一周/月/夜间的某个时间，没有或很少有查询进来。然后，您可以打开一个事务，读取所有transactions WHERE aggregated = false，并使用UPDATE将它们添加到transactions_aggr中。请记住在同一事务中切换aggregated为true。然而，这种方法的棘手之处在于您必须注意读取查询将在该事务中“看到”的内容：根据您在“更新作业”期间精度要求的不同，您可能需要考虑将事务隔离级别切换为“READ_COMMITED”以防止幽灵读取。

关于您的第三个查询问题，您可以尝试基于type_id进行分区的方法。然而，我认为您的查询有点奇怪，因为您正在执行LIMIT/OFFSET而没有排序（例如，没有放置ORDER BY语句），并且没有指定（注意：您没有说您将使用数据库游标）。如果您在表上启用分区，则可能会导致当前使用的隐式顺序发生更改。因此，请注意这可能对您的程序造成的副作用。 还有一件事：在真正进行分区拆分之前，我建议您首先通过发出以下命令来检查关于type_id的数据分布情况。

select type_id, count(*) from transactions group by type_id

并不是所有的数据都与card_payment相关，例如有90%的数据与之相关 - 这将导致您的分区极度不均匀，并且最大的性能问题查询仍然会进入这个单一的“大分区”。

希望这能对你有所帮助 - 祝你好运！

- EagleRainbow

---

谢谢您的努力！我运行了查询。数据分布几乎相等，每种类型大约在5到10%之间。有一种类型很少使用，另外两种类型使用更频繁，但不超过20%。transaction_aggregations的问题在于查询是由应用层生成的，目前，任何应用程序的修改都是有问题的，并且不在我们的掌握之中。关于查询顺序：我们使用隐式排序，因为我们按照交易进入系统的方式列出交易，因此自然主键排序效果很好。 - Luke1988