如何配置PostgreSQL为数据仓库

如何配置PostgreSQL为数据仓库

数据仓库和关系数据库之间的差异

生产查询

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

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

select * from users where id = 1234

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

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

分析查询

分析查询通常是相反的:

  • 一个查询将处理许多行(通常是整个表的很大一部分)
  • 查询可能需要几秒钟到几分钟才能完成
  • 查询将从宽(多列)表中的少量列中进行选择

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

图片来源:James Cheng

Holistics.io 有一个很好的指南,更详细地解释了这一点。

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_typewait_event列会显示IODataFileRead如果查询从磁盘读取。上面的查询对于查看可能阻塞的任何其他内容也非常有用。

批量插入后的清理

清除表是保持 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室值得考虑的。

作者:Cedric Dussud

免责声明:凡未注明来源或者来源为网络的信息均转自其它平台,是出于传递更多信息之目的,并不意味着赞同其观点或证实其内容的真实性。网站只负责对文章进行整理、排版、编辑,不承担任何法律责任。若有侵权或异议请联系我们删除,谢谢。

发表评论

您的电子邮箱地址不会被公开。