使用PostgreSQL作为数据仓库

2021-05-11 04:51:42

在叙述者,我们支持许多数据仓库,包括Postgres。虽然它是为生产系统设计的,但有一点调整Postgres可以非常适合数据仓库。

典型的生产数据库查询从潜在的大型数据集中选择几个行。它们'重新设计,旨在快速回答这些类型的问题。

为了支持这一点,大多数数据库,包括的postgres,通过行存储数据 - 这允许高效地加载来自磁盘的整行。它们经常使用索引来快速找到相对少量的行。

查询将从宽(多列)表中的少量列中进行选择

由于这种专用数据仓库(如红移,BigQuery和雪花)使用面向列的存储和Don' t有索引。

Postgres虽然导向,但也可以轻松地使用分析查询。它只是需要一些调整和一些测量。虽然Postgres是一个很好的选择,但请记住,像雪花一样的云仓库(从长远来看)更易于管理和维护。

小心单词:请勿使用您的生产Postgres实例进行数据报告/指标。一些查询很好,但分析的工作负载从典型的生产工作量不同,它们将对生产系统产生相当强烈的性能影响。

常见表表达式(CTE)也称为'与#39;查询。他们'重新避免深深嵌套的子查询。

不幸的是Postgres'查询计划者(在版本12之前)将CTES视为黑匣子。 Postgres将自身有效地计算CTE,实现结果,然后使用时扫描结果。在许多情况下,这可以大大减慢查询。

在叙述者中,从我们的一些常见查询中删除3个CTE将它们增长为4倍。

它'较长较长的CTE,但对于分析工作负载,性能差异是值得的。

对于传统的生产查询,索引对分析工作负载实际上不太重要。事实上,私人仓库如红逛和雪花唐'它根本拥有它们。

虽然索引对于快速返回少量记录是有用的,但它并不是在表中需要大多数行时提供帮助。例如,叙述者的常见查询是这样的

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

如果没有写出SQL'很清楚这个查询可以覆盖很多行。它必须考虑所有客户,所有电子邮件打开,以及所有页面视图(其中页面=' /')。

即使我们在此查询中有一个索引,Postgres也不会使用它 - 它 - 它'在加载许多行时执行表扫描更快(磁盘上更简单的布局)。

对于许多分析查询,Postgres更快地查询它比索引扫描更快地进行表扫描

索引增加了表的大小。表格越小,内存越好。

一些查询将比索引快得多,值得的是空间。对我们来说,我们经常在客户第一次找到某些东西时查询。我们有一个列(Activity_occurrence),所以我们建立一个部分索引。

分区表可以是提高表扫描性能的重要方法,而无需支付索引的存储成本。

概念上,它将一个更大的桌子分成多个块。理想情况下,大多数查询只需要从一个(或少数)读取,这可以急剧速度速度。

最常见的情景是按时间打破令人措施(范围分区)。如果您'重新查询上个月的数据,将大表分解为每月分区,允许所有查询忽略所有旧行。

在叙述者,我们通常会在所有时间看待数据,所以范围为ISN' t有用。但是,我们确实有一个非常大的表格,存储客户活动(查看了一个页面,提交了支持请求等)。我们一次很少查询多个或两项活动,因此列表分区实际上很好。

这些福利是两倍:我们的大多数由活动的查询无论如何都是完整的表扫描,所以现在他们扫描一个较小的分区,我们不再需要大量的活动(主要用于它少频繁活动)。

分区的主要警告是它们略微更多的工作来管理和逃离' t始终是一种表现提升 - 制作太多的分区或大量不等大小的分区' t始终帮助。

因为表扫描更常见(请参阅上面的索引),磁盘I / O可能变得相当重要。按性能影响的顺序

确保Postgres有足够的可用内存以缓存最常见的表格 - 或使表格更小

选择硬盘上的SSD(虽然这取决于成本/数据大小)

调查I / O可用程度如何 - 如果数据库读取到磁盘,则某些云托管提供程序将节气门I / O。

检查磁盘的长时间查询是否符合磁盘的一个好方法是pg_stat_activity表。

选择PID,现在() - pg_stat_actity.query_start作为持续时间,usename,查询,状态,wait_event_type,wait_eventfrom pg_stat_activitywhere状态='活跃' (现在() - pg_stat_activity.query_start)>间隔' 1分钟&#39 ;;

Wait_Event_type和Wait_Event列将显示IO和Datafileread,如果查询从磁盘读取。上面的查询也非常有用,可以看到可能阻塞的其他任何东西,如锁。

吸尘表是保持Postgres顺利运行的重要方法 - 它可以节省空间,并且当作为真空分析运行时,它将计算统计数据以确保查询计划员估计正常的一切。

默认情况下的Postgres运行自动真空过程以照顾此功能。通常它是最好的吧。

也就是说,在插入或移除的一堆数据后,真空分析最好运行。如果您'重新运行一份工作以定期插入数据,请在您完成插入所有内容后立即运行真空分析有意义。这将确保新数据将立即有高效查询。一旦你' ve运行它,自动真空过程会知道不再真空吸尘。

Postgres,它可以,将并行运行疑问的部分。这是仓储应用的理想选择。并行查询添加了一些延迟(必须生成工人,然后它们的结果带回了一系列),但它通常对分析工作负载通常无关紧要,其中查询多秒钟。

在实践中,并行查询加快表格或索引扫描相当一点,这是我们的查询往往花费大量时间的地方。

如预期运行的最佳方式是使用解释。您应该看到聚集,后跟一些并行工作(加入,排序,索引扫描,SEQ扫描等)

- >收集合并(成本= 2512346.78..2518277.16 Rows = 40206宽度= 60)工人计划:2名工人推出:2 ... - > STARTIVE_STREAM S_1上的并行SEQ扫描

工人是并行执行工作的流程数。工人的数量由两个设置控制:max_parallel_workers和max_paraller_workers_per_gather

显示max_parallel_workers; - 工作人员总数max_parallel_workers_per_gather; - 在查询的时间作业

如果您使用解释(分析,冗长),您可以看到每个工作人员所花费的时间以及它处理了多少行。如果数字大致等同,那么并行地执行工作可能有所帮助。

它值得尝试尝试不同的查询并调整max_parallel_workers_per_gather以查看影响的数量。作为一项经验考拇指,Postgres可以作为仓库用作更多工人作为生产系统。

Postgres收集表上的统计信息以通知查询计划者。它通过对表进行采样并存储(以及其他内容)来实现这一点。所需的样本越多,查询计划者的准确性越准确。对于分析工作负载,在较少,更长的查询中,它有助于增加Postgres收集多少。

默认值为100;任何高于100到1000的值都很好。请注意,这是应该测量的设置中的一个。使用一些常见查询的解释分析,以查看查询计划员的萎缩程度。

这个只是要意识的事情。 Postgres使用基于行的存储,这意味着行在磁盘上顺序布置。它实际上存储了整个第一行(用其所有列),然后是整个第二行等。

这意味着当您从具有大量列的表中选择相对较少的列时,Postgres将加载它的批次数据,它不会使用。所有表数据都在固定大小(通常为4KB)块中读取,因此它可以' t amply' t刚选择性地从磁盘读取一行的几列。

相比之下,大多数专用数据仓库都是柱状商店,它能够只读所需的列。

注意:Don' t替换一个宽表,其中包含需要在每个查询上加入的多个表。它' ll可能会慢(虽然总是衡量)。

这个更像是一项拇指 - 所有等于较少的列。 实践中的性能增加' t通常是重要的。 Postgres和基于云的数据仓库之间的最后一个主要区别是极端规模。 与postgres不同,它们与分布式系统从地上架构。 这允许它们在数据大小生长时相对线性地添加更多处理能力。 当数据库已经太大并且应该移动到分布式系统时,我有一个很好的拇指规则。 但是,当你到达那里时,你可能会有专业知识来处理迁移并理解权衡。