database - 海量 postgres 表的最佳实践

Question

我有一个表，其中包含 3 个字段(用户名、目标值、分数)，由用户名 (~400,000) 和目标值 (~4000) 的完整交叉在外部生成，并计算出分数，导致总行数约为 16 亿.

我在这个表上的所有查询都将采用

的格式

SELECT *
FROM _table
WHERE target_values IN (123, 456)

我的初始版本包括 target_values 上的 BTREE 索引，但我最终花了 45 分钟对索引进行 BITMAP HEAP SCAN。我也一直在研究 BRIN 索引、分区和表集群，但由于将每种方法应用于表需要数小时，我无法完全强制每个选项并测试性能。

在 Postgres 10 中处理具有非常“ block 状”数据的单个大表有哪些建议？

Answer 1

如果表是两个数据集的交叉连接，为什么不存储单独的表并根据需要计算连接？数据库擅长于此。

根据您的描述，如果您在表上运行 CLUSTER 以按索引顺序物理重写它，我希望能获得性能提升。那么您将不得不访问更少的表 block 。

不幸的是，CLUSTER 会花费很长时间，使表不可用并且必须定期重复。<​​/p>

可能更好的替代方法是按 target_value 对表进行分区。 4000个分区有点多，所以可以使用列表分区将多个分区捆绑在一起。

这将允许您的查询仅在几个分区上执行快速顺序扫描。它还将使 autovacuum 的工作更容易。

然而，最重要的是，如果您从表格中选择很多行，则总是需要很长时间。