使用PostgreSQL作为数据仓库
在叙述者，我们支持许多数据仓库，包括Postgres。虽然它是为生产系统设计的，但有一点调整Postgres可以非常适合数据仓库。
典型的生产数据库查询从潜在的大型数据集中选择几个行。它们＆＃39;重新设计，旨在快速回答这些类型的问题。
为了支持这一点，大多数数据库，包括的postgres，通过行存储数据 - 这允许高效地加载来自磁盘的整行。它们经常使用索引来快速找到相对少量的行。
查询将从宽（多列）表中的少量列中进行选择
由于这种专用数据仓库（如红移，BigQuery和雪花）使用面向列的存储和Don＆＃39; t有索引。
Postgres虽然导向，但也可以轻松地使用分析查询。它只是需要一些调整和一些测量。虽然Postgres是一个很好的选择，但请记住，像雪花一样的云仓库（从长远来看）更易于管理和维护。
小心单词：请勿使用您的生产Postgres实例进行数据报告/指标。一些查询很好，但分析的工作负载从典型的生产工作量不同，它们将对生产系统产生相当强烈的性能影响。
常见表表达式（CTE）也称为＆＃39;与＃39;查询。他们＆＃39;重新避免深深嵌套的子查询。
不幸的是Postgres＆＃39;查询计划者（在版本12之前）将CTES视为黑匣子。 Postgres将自身有效地计算CTE，实现结果，然后使用时扫描结果。在许多情况下，这可以大大减慢查询。
在叙述者中，从我们的一些常见查询中删除3个CTE将它们增长为4倍。
它＆＃39;较长较长的CTE，但对于分析工作负载，性能差异是值得的。
对于传统的生产查询，索引对分析工作负载实际上不太重要。事实上，私人仓库如红逛和雪花唐＆＃39;它根本拥有它们。
虽然索引对于快速返回少量记录是有用的，但它并不是在表中需要大多数行时提供帮助。例如，叙述者的常见查询是这样的
获取每个客户打开所有电子邮件，并计算转换速率以查看按月分组的主页。
如果没有写出SQL＆＃39;很清楚这个查询可以覆盖很多行。它必须考虑所有客户，所有电子邮件打开，以及所有页面视图（其中页面=＆＃39; /＆＃39;）。
即使我们在此查询中有一个索引，Postgres也不会使用它 - 它 - 它＆＃39;在加载许多行时执行表扫描更快（磁盘上更简单的布局）。
对于许多分析查询，Postgres更快地查询它比索引扫描更快地进行表扫描
索引增加了表的大小。表格越小，内存越好。
一些查询将比索引快得多，值得的是空间。对我们来说，我们经常在客户第一次找到某些东西时查询。我们有一个列（Activity_occurrence），所以我们建立一个部分索引。
分区表可以是提高表扫描性能的重要方法，而无需支付索引的存储成本。
概念上，它将一个更大的桌子分成多个块。理想情况下，大多数查询只需要从一个（或少数）读取，这可以急剧速度速度。
最常见的情景是按时间打破令人措施（范围分区）。如果您＆＃39;重新查询上个月的数据，将大表分解为每月分区，允许所有查询忽略所有旧行。
在叙述者，我们通常会在所有时间看待数据，所以范围为ISN＆＃39; t有用。但是，我们确实有一个非常大的表格，存储客户活动（查看了一个页面，提交了支持请求等）。我们一次很少查询多个或两项活动，因此列表分区实际上很好。
这些福利是两倍：我们的大多数由活动的查询无论如何都是完整的表扫描，所以现在他们扫描一个较小的分区，我们不再需要大量的活动（主要用于它少频繁活动）。
分区的主要警告是它们略微更多的工作来管理和逃离＆＃39; t始终是一种表现提升 - 制作太多的分区或大量不等大小的分区＆＃39; 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状态=＆＃39;活跃＆＃39; （现在（） - pg_stat_activity.query_start）＆gt;间隔＆＃39; 1分钟＆＃39 ;;
Wait_Event_type和Wait_Event列将显示IO和Datafileread，如果查询从磁盘读取。上面的查询也非常有用，可以看到可能阻塞的其他任何东西，如锁。
吸尘表是保持Postgres顺利运行的重要方法 - 它可以节省空间，并且当作为真空分析运行时，它将计算统计数据以确保查询计划员估计正常的一切。
默认情况下的Postgres运行自动真空过程以照顾此功能。通常它是最好的吧。
也就是说，在插入或移除的一堆数据后，真空分析最好运行。如果您＆＃39;重新运行一份工作以定期插入数据，请在您完成插入所有内容后立即运行真空分析有意义。这将确保新数据将立即有高效查询。一旦你＆＃39; ve运行它，自动真空过程会知道不再真空吸尘。
Postgres，它可以，将并行运行疑问的部分。这是仓储应用的理想选择。并行查询添加了一些延迟（必须生成工人，然后它们的结果带回了一系列），但它通常对分析工作负载通常无关紧要，其中查询多秒钟。
在实践中，并行查询加快表格或索引扫描相当一点，这是我们的查询往往花费大量时间的地方。
如预期运行的最佳方式是使用解释。您应该看到聚集，后跟一些并行工作（加入，排序，索引扫描，SEQ扫描等）
- ＆gt;收集合并（成本= 2512346.78..2518277.16 Rows = 40206宽度= 60）工人计划：2名工人推出：2 ... - ＆gt; 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）块中读取，因此它可以＆＃39; t amply＆＃39; t刚选择性地从磁盘读取一行的几列。
相比之下，大多数专用数据仓库都是柱状商店，它能够只读所需的列。
注意：Don＆＃39; t替换一个宽表，其中包含需要在每个查询上加入的多个表。它＆＃39; ll可能会慢（虽然总是衡量）。
这个更像是一项拇指 - 所有等于较少的列。 实践中的性能增加＆＃39; t通常是重要的。 Postgres和基于云的数据仓库之间的最后一个主要区别是极端规模。 与postgres不同，它们与分布式系统从地上架构。 这允许它们在数据大小生长时相对线性地添加更多处理能力。 当数据库已经太大并且应该移动到分布式系统时，我有一个很好的拇指规则。 但是，当你到达那里时，你可能会有专业知识来处理迁移并理解权衡。
