九道门|如何配置PostgreSQL为数据仓库

生产查询

典型的生产数据库查询从潜在的大型数据集中选择少量行。它们旨在快速回答许多此类问题。

想象一个web应用程序——成千上万的用户可能同时在查询

select * from users where id = 1234

数据库将被调优快速（在几毫秒内）处理大量这些请求。

为了支持这一点，包括 Postgres 在内的大多数数据库都按行存储数据——这允许从磁盘高效加载整行。他们频繁使用索引来快速找到相对较少的行。

分析查询

分析查询通常是相反的：

• 一个查询将处理许多行（通常是整个表的很大一部分）

• 查询可能需要几秒钟到几分钟才能完成

• 查询将从宽（多列）表中的少量列中进行选择

由于这种专用数据仓库（如 Redshift、BigQuery 和 Snowflake）使用面向列的存储并且没有索引 。

Postgres 虽然是面向行的，但也可以轻松地处理分析查询。它只需要一些调整和一些测量。尽管 Postgres 是一个不错的选择，但请记住，像 Snowflake 这样的基于云的仓库（从长远来看）将更易于管理和维护。

将 Postgres 配置为数据仓库

警告：不要将您的生产 Postgres 实例用于数据报告/指标。一些查询没问题，但分析的工作负载与典型的生产工作负载差别很大，它们会对生产系统产生相当大的性能影响。

避免使用公共表表达式

公共表表达式(CTE)也被称为“WITH”查询。它们是避免深度嵌套子查询的好方法。

Postgres 的查询规划器（版本12之前）将 CTEs视为一个黑匣子。Postgres 将自己有效地计算 CTE，将结果物化，然后在使用时扫描结果。在许多情况下，这会大大减慢查询速度。

在讲述人中，从我们的一些常见查询中删除 3 个 CTEs将它们的速度提高了 4 倍。

简单的解决方法是将CTEs重写为子查询（或升级到 12）。

较长的 CTEs可读性稍差，但对于分析工作负载而言，性能差异是值得的。

谨慎使用索引

与传统的生产查询相比，索引对于分析工作负载实际上没有那么重要。事实上，像 Redshift 和 Snowflake 这样的专用仓库根本没有它们。

虽然索引对于快速返回少量记录很有用，但如果查询需要表中的大多数行，它就无济于事。例如，常见查询是这样的

获取每个客户打开的所有电子邮件，并计算查看按月分组的主页的转化率。

如果不写出 SQL，很明显这个查询可以覆盖很多行。它必须考虑所有客户、所有打开的电子邮件和所有页面浏览量（其中 page = '/'）。

即使我们为这个查询建立了索引，Postgres 也不会使用它——在加载多行时进行表扫描会更快（磁盘上的更简单的布局）。

不使用索引的原因

1. 对于许多分析查询，Postgres 执行表扫描比索引扫描更快

2. 索引会增加表的大小。表越小，就越适合内存。

3. 索引会在每次插入/更新时增加额外成本

   
   

何时使用索引

使用索引，某些查询会快得多，并且值得占用空间。对我们来说，我们经常询问客户第一次做某事。我们有一个用于 (activity_occurrence)的列，因此我们构建了一个部分索引。

分区

对表进行分区是一种改善表扫描性能的好方法，而无需支付索引的存储成本。

从概念上讲，它将一个更大的表分成多个块。理想情况下，大多数查询只需要从一个（或少数几个）中读取，这可以显着加快速度。

最常见的场景是按时间进行划分（range partitioning）。如果只查询上个月的数据，将大表分解为每月分区可以让所有查询忽略所有较旧的行。

我们通常会查看所有时间的数据，因此范围没有用。但是，我们确实有一个非常大的表来存储客户活动（查看页面、提交支持请求等）。我们很少一次查询超过一两个活动，因此list partitioning效果非常好。

好处有两个：我们的大多数活动查询无论如何都会进行全表扫描，所以现在他们正在扫描一个较小的分区，我们不再需要活动的大索引（这主要用于不太频繁的活动）活动）。

对分区的主要警告是，它们需要管理更多的工作，并且并不总是能提升性能——创建过多的分区或大小极不相同的分区并不总是有帮助。

最小化磁盘和 I/O

由于表扫描更为常见（请参阅上面的索引），因此磁盘 I/O 可能变得相当重要。按性能影响排序

1. 确保 Postgres 有足够的可用内存来缓存最常访问的表 - 或者使表更小

2. 选择硬盘驱动器上的 SSD（尽管这取决于成本/数据大小）

3. 查看有多少 I/O 可用——如果数据库读取磁盘过多，一些云托管提供商会限制 I/O。

检查长时间运行的查询是否命中磁盘的一种好方法是pg_stat_activity表。

在wait_event_type与wait_event列会显示IO和DataFileRead如果查询从磁盘读取。上面的查询对于查看可能阻塞的任何其他内容也非常有用。

批量插入后的清理

清除表是保持 Postgres 平稳运行的重要方法——它节省空间，并且在运行vacuum analyze时会计算统计信息以确保查询计划器正确估计所有内容。

默认情况下，Postgres 运行一个自动清理过程来处理这个问题。通常，最好不要管它。

也就是说，vacuum analyze最好在插入或删除一堆数据后运行。如果您正在运行一项定期插入数据的作业，那么vacuum analyze在您完成插入所有内容后立即运行是有意义的。这将确保新数据将立即具有用于高效查询的统计信息。一旦你运行了它，自动吸尘过程就会知道不再吸尘那个桌子。

查看并行查询

如果可以，Postgres将并行运行部分查询。这是仓储应用的理想选择。并行查询会增加一些延迟（必须产生工作人员，然后将他们的结果重新组合在一起），但对于分析工作负载来说通常无关紧要，因为查询需要几秒钟。

在实践中，并行查询大大加快了表或索引扫描的速度，这是我们的查询往往会花费大量时间的地方。

查看它是否按预期运行的最佳方法是使用explain. 您应该看到Gather后面是一些并行工作（连接、排序、索引扫描、seq 扫描等）

工作人员是并行执行工作的进程数。worker 的数量由两个设置控制：max_parallel_workers和max_parallel_workers_per_gather

如果您使用，explain(analyze, verbose)可以看到每个工人花费了多少时间以及它处理了多少行。如果数字大致相等，那么并行工作可能会有所帮助。

有必要尝试不同的查询并调整数量max_parallel_workers_per_gather以查看影响。根据经验，Postgres 在用作仓库然后用作生产系统时可以从更多的工人中受益。

增加统计抽样

Postgres 收集有关表的统计信息以通知查询计划器。它通过对表进行采样并存储（除其他外）最常见的值来实现这一点。需要的样本越多，查询规划器就越准确。对于分析工作负载，其中有更少、更长时间运行的查询，它有助于增加 Postgres 收集的数量。

这可以在每列的基础上完成

或者对于整个数据库

默认值为 100；任何高于 100 到 1000 的值都是好的。注意，这是应该测量的设置之一。EXPLAIN ANALYZE在一些常见查询上使用以查看查询计划程序错误估计的程度。

使用更少的列

这只是需要注意的事情。Postgres 使用基于行的存储，这意味着行在磁盘上按顺序排列。它从字面上存储整个第一行（及其所有列），然后是整个第二行，等等。

这意味着当你从包含大量列的表中选择相对较少的列时，Postgres 将加载大量不会使用的数据。所有表数据都是在固定大小（通常为 4KB）的块中读取的，因此它不能只是有选择地从磁盘读取一行的几列。

相比之下，大多数专用数据仓库是列式存储，它们只能读取所需的列。

注意：不要将单个宽表替换为需要对每个查询进行连接的多个表。它可能会更慢

这更像是一条经验法则——在所有条件相同的情况下，更喜欢更少的列。实践中的性能提升通常不会很显着。

考虑大规模的数据仓库

Postgres 和基于云的数据仓库之间的最后一个主要区别是极大的规模。与 Postgres 不同，它们从头开始构建为分布式系统。这允许它们随着数据大小的增长而相对线性地增加更多的处理能力。

当数据库变得太大并且应该移至分布式系统时，我没有好的经验法则。但是当您到达那里时，您可能会拥有处理迁移和了解权衡的专业知识。

在我的非正式测试中，使用 50-100M 行之间的表，Postgres 表现得非常好——通常与 Redshift 之类的东西一致。但是性能取决于很多因素——磁盘与 SSD、CPU、数据结构、查询类型等，如果不进行一些正面测试，真的不可能概括。

如果您将 Postgres 扩展到数十亿行，Citus室值得考虑的。

！