使用PostgreSQL作为一个数据仓库

吴欣
1,155 阅读11分钟

数据仓库和关系型数据库之间的区别

生产查询

典型的生产型数据库查询从一个潜在的大数据集中选择一些行。它们被设计用来快速回答很多这类问题。

想象一下一个网络应用程序--成千上万的用户可能正在查询

select * from users where id = 1234

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

为了支持这一点，大多数数据库，包括Postgres，都是按行存储数据的--这允许从磁盘上有效地加载整个行。他们经常使用索引来快速查找相对较少的行。

分析性查询

分析性查询通常是相反的：

  • 一个查询将处理许多行（通常是整个表的很大一部分）。
  • 查询可能需要几秒钟到几分钟的时间来完成
  • 一个查询将从一个广泛的（多列）表中选择少量的列。

正因为如此，专用数据仓库（如Redshift、BigQuery和Snowflake）使用面向列的存储，没有索引。

Credit:James Cheng

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

这对Postgres意味着什么

Postgres，虽然是面向行的，但也可以很容易地与分析性查询一起工作。它只是需要一些调整和一些测量。虽然Postgres是一个伟大的选择，但请记住，像Snowflake这样基于云的仓库将（从长远来看）更容易管理和维护。

配置Postgres作为一个数据仓库

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

避免通用表表达式

普通表表达式（CTE）也被称为 "WITH "查询。它们是一种避免深度嵌套子查询的好方法：

WITH my_expression AS (
    SELECT customer as name FROM my_table
)
SELECT name FROM my_expression

不幸的是，Postgres的查询计划器（在12版之前）将CTE视为一个黑盒子。Postgres将有效地自己计算CTE，将结果具体化，然后在使用时扫描结果。在许多情况下，这可能会大大降低查询速度。

在Narrator中，从我们一些常见的查询中删除3个CTE，使其速度提高了4倍。

简单的解决办法是将CTE重写为子查询（或升级为12）：

SELECT name FROM (
    SELECT customer as name FROM my_table
)

对于较长的CTE来说，它的可读性要差一些，但是对于分析工作负载来说，性能上的差异是值得的。

少用索引

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

虽然索引对快速返回少量记录很有用，但如果一个查询需要表内的大多数行，它就没有帮助了。例如，一个常见的查询在Narrator是这样的

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

在不写出SQL的情况下，很明显这个查询可以涵盖很多行。它必须考虑所有的客户，所有的电子邮件打开次数，以及所有的页面浏览次数（其中页面='/'）。

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

不使用索引的原因

  1. 对于许多分析查询来说，Postgres做表扫描比做索引扫描更快。
  2. 索引增加了表的大小。表越小，内存中的容量就越大。
  3. 索引会在每次插入/更新时增加额外的成本

什么时候也要使用索引

有些查询用索引会快得多，值得花钱买。对于我们来说，我们经常查询一个客户第一次做的事情。我们有一个这样的列（activity_occurrence ），所以我们建立一个部分索引。

create index first_occurrence_idx on activity_stream(activity) where activity_occurrence = 1;

分区

分区表是提高表扫描性能的一个好方法，而不需要支付索引的存储成本。

从概念上讲，它将一个较大的表分成多个块。理想情况下，大多数查询只需要从其中一个（或一小部分）中读取，这可以极大地提高速度。

最常见的情况是按时间划分(range partitioning)。如果你只查询上个月的数据，把一个大表分成每月的分区，让所有的查询都忽略所有的旧行。

在Narrator公司，我们通常会看所有时间的数据，所以范围并不有用。然而，我们确实有一个非常大的表，用来存储客户的活动（浏览了一个页面，提交了一个支持请求，等等）。我们很少一次查询超过一个或两个活动，所以list partitioning ，效果非常好。

这样做的好处是双重的：无论如何，我们大多数按活动进行的查询都是全表扫描，所以现在他们是在扫描一个较小的分区，而且我们不再需要一个关于活动的大索引（该索引主要用于不太频繁的活动）。

分区的主要注意事项是，它们的管理工作量略大，而且并不总是能提高性能--做太多的分区或大小不等的分区并不总是有帮助。

最大限度地减少磁盘和I/O

由于表扫描比较常见（见上面的索引），磁盘I/O可能变得相当重要。按照性能影响的顺序

  1. 确保Postgres有足够的可用内存来缓存最常访问的表--或者把表变小
  2. 选择SSD而不是硬盘（虽然这取决于成本/数据大小）。
  3. 看看有多少I/O可用--如果数据库对磁盘的读取太多，一些云主机供应商会节制I/O。

检查一个长期运行的查询是否占用磁盘的一个好方法是pg_stat_activity 表：

SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  usename,
  query,
  state,
  wait_event_type,
  wait_event
FROM pg_stat_activity
WHERE state = 'active' and (now() - pg_stat_activity.query_start) > interval '1 minute';

如果查询正在从磁盘读取，wait_event_type 和wait_event 列将显示IO 和DataFileRead 。上面的查询对于查看其他可能被阻塞的东西也非常有用，比如锁。

批量插入后的真空

真空表是保持Postgres平稳运行的一个重要方法--它可以节省空间，当以vacuum analyze ，它将计算统计数据，以确保查询计划器正确估计一切。

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

也就是说，vacuum analyze ，最好是在插入或删除一堆数据后再运行。如果你正在运行一个定期插入数据的工作，那么在你完成插入所有数据后立即运行vacuum analyze ，是有意义的。这将确保新的数据将立即有统计数据，以便有效地查询。一旦你运行了它，自动真空程序就会知道不再对该表进行真空处理。

查看并行查询

Postgres，当它可以时，会并行运行部分查询。这是对仓储应用的理想选择。并行查询会增加一些延迟（工作者必须被生成，然后把他们的结果带回来），但对于分析工作负载来说，这通常是不重要的，因为查询需要多秒。

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

看这是否按预期运行的最好方法是使用explain 。你应该看到一个Gather ，然后是一些并行工作（一个连接、一个排序、一个索引扫描、一个seq扫描，等等）

->  Gather Merge  (cost=2512346.78..2518277.16 rows=40206 width=60)
    Workers Planned: 2
    Workers Launched: 2
        ...
      ->  Parallel Seq Scan on activity_stream s_1

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

show max_parallel_workers;            -- total number of workers allowed
show max_parallel_workers_per_gather; -- num workers at a time on the query

如果你使用explain(analyze, verbose) ，你可以看到每个工作者花了多少时间，以及它处理了多少行。如果这些数字大致相当，那么以并行方式进行工作可能会有帮助。

 Worker 0: actual time=13093.096..13093.096 rows=8405229 loops=1
 Worker 1: actual time=13093.096..13093.096 rows=8315234 loops=1

值得尝试不同的查询和调整max_parallel_workers_per_gather 的数量，以了解其影响。作为一个经验法则，Postgres在作为仓库使用时，可以从更多的工人中获益，而不是作为一个生产系统。

增加统计数据的取样

Postgres收集表的统计数据，以告知查询计划器。它通过对表进行抽样并存储（除其他外）最常见的值来做到这一点。采样越多，查询计划器就越准确。对于分析性工作负载来说，有较少的、运行时间较长的查询，这有助于增加Postgres收集的数量。

这可以在每一列的基础上进行

ALTER TABLE table_name ALTER COLUMN column_name set statistics 500;

增加一个列的统计数据

或者为整个数据库增加统计数据

ALTER DATABASE mydb SET default_statistics_target = 500;

增加一个数据库的统计数据

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

使用更少的列

这只是一个需要注意的问题。Postgres使用基于行的存储，这意味着行是按顺序排列在磁盘上的。它实际上是存储整个第一行（包括其所有的列），然后是整个第二行，等等。

这意味着，当你从一个有很多列的表中选择相对较少的列时，Postgres将加载很多它不打算使用的数据。所有的表数据都是以固定大小（通常是4KB）的块来读取的，所以它不能只是有选择地从磁盘上读取某一行的几列。

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

注意：不要用多个需要在每次查询时进行连接的表来代替一个宽表。这很可能会更慢（尽管总是在衡量）。

这个更像是一个经验法则--在所有条件相同的情况下，更倾向于选择更少的列。在实践中，性能的提高通常不会很明显。

添加一个日期函数

像Redshift、Snowflake和BigQuery这样的数据仓库都支持datediff--一种简单的方法来计算两个时间戳之间按年、日、小时等的差异。

Postgres没有这个功能，所以如果你做大量的分析性查询，它可能是有用的。这篇文章太长了，我有另一篇文章描述了Postgres缺失的日期差函数以及它的实现。

考虑规模化的数据仓库

Postgres和基于云的数据仓库之间的最后一个主要区别是极端规模。与Postgres不同的是，它们从一开始就被设计成分布式系统。这使得它们可以随着数据规模的增长而相对线性地增加处理能力。

我没有一个好的经验法则来说明什么时候一个数据库变得太大，应该转移到一个分布式系统。但是，当你到了那个时候，你可能会有处理迁移的专业知识，并理解其中的权衡。

在我的非正式测试中，如果表的行数在50-100万之间，Postgres的表现非常好--通常与Redshift这样的东西一致。但性能取决于很多因素--磁盘与SSD、CPU、数据结构、查询类型等，如果不做一些头对头的测试，真的无法概括。

如果你要将Postgres扩展到数十亿行，Citus是值得考虑的。

avatar
文章
阅读
粉丝