把 PostgreSQL 用作数据仓库（译）

技术数据库

Word count: 2.8kReading time: 10 min

 2021/05/12 

原文链接：Using PostgreSQL as a Data Warehouse

把 PostgreSQL 用作数据仓库

Narrator 为多种数据仓库提供技术支持，其中就包括 PostgreSQL。

尽管 PostgreSQL 是为生产系统而设计的，但只需要稍作调整就可以很好地作为数据仓库来使用：

不要把该服务器同时用作生产系统。
升级到 pg 12+（或者在查询中避免使用 CTE）。
有节制地使用索引（舍，得）。
考虑对长表进行分区。
确保 I/O 不受限制。
在批量插入后执行 vacuum 分析（更新统计信息，使优化器能选择更好的方案执行 SQL）。
探索并行查询。
增加统计抽样。
对于频繁查询的表，设置更少的列。
对于大规模操作，考虑使用专门的仓库。

数据仓库与关系型数据库的区别

生产型查询

在生产数据库中，为了满足快速响应，典型的查询需求是在大型数据集中选取几行。

可以想象在一个 Web 应用中，在同一时刻有成千上万的用户执行查询：

1	SELECT * FROM users WHERE id = 1234

这种数据库可以快速（毫秒内）响应大量的类似请求。而为了支撑这种查询，包括 PostgreSQL 在内的多数的数据库是按行存储数据的 —— 可有效地从磁盘中加载整行的数据。而经常使用索引来快速找到相对较少的行。

分析型查询

分析型查询通常与之相反：

查询大量的行（通常占整表的大部分）。
查询可能需要数秒甚至数分钟才能完成。
查询将选取宽（多列）表的少数列。

因此专用的数据仓库（像 Redshift，BigQuery 和 Snowflake）使用列式存储实现，而且没有索引。

            row-store 
    date  store  product Cutsomer price
{
    [(...), (...), (...), (...), (...)],
    [(...), (...), (...), (...), (...)]
}

            col-store 
     date   store  product Cutsomer price
{
    [(...), [(...), [(...), [(...), [(...),
     (...),  (...),  (...),  (...),  (...),
     (...)], (...)], (...)]  (...)]  (...)] 
}

Holistics.io 有一份不错的指南详细介绍了这一点。

这对于 Postgres 来说意味着什么？

Postgres 是行式存储，但它也可以轻松支持分析查询，而这只需要做出一些调整和估量。

不过尽管 Postgres 是很好的选择，但从长远来说像 Snowflake 这种基于云的数据库更易于管理和维护。

把 Postgres 配置为数据仓库

温馨提示：请不要将 Postgres 生产库用作数据报表或指标采集。可以进行一些查询，但分析请求的工作负载和生产请求的工作负载差距甚远，以至于它们会对生产系统产生巨大的性能影响。

避免使用通用表表达式

通用表表达式（common table expressions, CTEs）又称为 “WITH” 查询，可避免嵌套子查询。

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

然而 Postgres（12-）的查询执行计划会将 CTEs 视为一个黑盒。Postgres 将自行有效地计算 CTEs 执行结果并进行具体化，然后再扫描时用上这些结果，在很多时候这会大大影响查询速度。

在 Narrator，从一些常用的查询中移除三个 CTEs 可提高四倍速度。

一个简单的解决方法是将 CTEs 改写为子查询（或升级为 12+）。

1
2
3

SELECT name FROM (
    SELECT customer as name FROM my_table
)

对于较长的 CTEs 来说这种做法可读性较差，但考虑到在分析型查询中可以提高性能，这是值得的。

有节制地使用索引

相比起传统生产型查询，索引对于分析型查询不太重要，像 Redshift 和 Snowflake 等专用数据仓库根本没有索引。

虽然索引对于快速返回少量记录的场景很有用，但在查询表中的大多数行时就不起作用了。

比如在 Narrator ，常见的查询像是这样的：

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

不需要写出 SQL 也能知道这种查询会覆盖大量的行，它要考虑所有的用户、打开的电子邮件以及所有页面视图（即 page = '/'）。即使为了这种查询建立了索引，Postgres 却不会利用上 —— 加载大量的行时执行全表扫描会更快（更简单的磁盘布局）。

不使用索引的原因

对于分析型查询，全表扫描比通过索引来扫描更快。
索引会增加表的大小，越小的表，对内存访问而言越友好。
索引会为每个插入或更新操作引入额外的开销。

什么时候使用索引？

在一些查询中使用索引可提高效率，也值得为此付出空间上的开销。比如经常需要查询一名客户首次执行的操作：用一个列 activity_occurrence 来记录，并因此建立部分索引。

1	CREATE INDEX first_occurrence_idx ON activity_stream(activity) WHERE activity_occurrence = 1;

分区

分区表可提高表扫描性能，而无需付出索引的额外成本。

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

在 Narrator 通常会一直查询一些数据，因此范围表示是没有用的。然而用一个非常大的表来存储客户活动（查看页面，提交请求等），由于很少会一次查询一个或两个以上的活动，因此分区的效果非常好：按活动进行的大多数查询无论如何都要执行全表扫描，因此可以扫描较小的分区，并不再需要大索引（该索引主要用于查询不经常使用的记录）。

使用重点注意的是管理分区的工作量比较多，而且也不总是能提高性能 —— 分区数太多，或分区规模不等时将起不了什么作用。

最小化磁盘与 I/O

由于表扫描操作很常见（参考上面的“索引”），因此磁盘 I/O 变得很重要，为了确保性能：

Postgres 要有足够的可用内存以缓存最常访问的表（或把表缩小）。
在磁盘驱动上选择使用 SSD（取决于成本/数据规模）。
检查有多少 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。

这个查询语句对于查看其他可能阻塞的内容（例如锁）也非常有用。

在每个批量插入后执行 Vacuum

Vacuuming 表对于保持 Postgres 平稳运行很重要 —— 可节省空间。在进行 vacuum 分析时将计算统计信息以确保查询计划可以正确估算。

在默认情况下 Postgres 运行自动 vacuum 进程来解决以上问题，所以通常最好不要管它。

这意味着最好在插入或删除数据后再运行 vacuum 分析。如果要定期插入数据，则在完成所有插入后立即运行 vacuum 分析就很有意义了：可确保新数据将立即具有统计信息以便进行有效的查询。而且一旦执行完毕，自动 vacuum 进程将不会再次对该表进行 vacuum 操作。

并行查询

Postgres 可并行执行部分查询，这对于数据仓库应用而言是很理想的执行方式。并行查询会增加一些延迟（派生 workers 线程，然后其执行结果进行聚合），但通常来说对于分析型查询而言这并不重要，因为查询就需要花上几秒。

并行查询在很大程度上加快了表或索引扫描（花费大量时间的地方）的速度。查看程序是否按预期运行的最好方法是使用 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

其中 Workers 是并行执行工作的进程数，由两个配置项控制：max_parallel_workers和max_parallel_workers_per_gather。

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

可尝试不同的查询方式，并通过调整 max_parallel_workers_per_gather 的值来查看其对性能影响。

从经验上来说，Postgres 用作数据仓库而时可以从更多的 Workers 中受益。

增加统计抽样

Postgres 通过收集表上的统计信息来通知查询计划。

它通过对表进行采样并存储最常见的值来实现，抽取的样本越多查询计划将越准确。分析型查询的查询次数少，运行时间长，有助于增加 Postgres 收集的数量。

这可以在每个列上完成。

1	ALTER TABLE table_name ALTER COLUMN column_name set statistics 500;

或者对于整个数据库进行操作：

1	ALTER DATABASE mydb SET default_statistics_target = 500;

其中默认值是 100，可设置的范围是 [100, 1000]。需要注意这是应该估量的设置之一。对一些常见查询使用 EXPLAIN ANALYZE 即可查看查询计划的估量值。

使用更少的列

Postgres 是行式存储，行在磁盘上是顺序排列的：存储第一行及其所有列，然后存储第二行及其所有列，依此类推。

当从多列的表中选择相对较少的列时，Postgres 将加载很多不需要使用的数据。所有表数据都是以固定大小（通常 4KB）的块读取，因此它不能仅从磁盘选择性地读取一行中的几列。

注意：请不要将单个宽表替换为需要对每个查询进行联接的多个表。这可能会比较慢（尽管始终会测量）。

经验法则 —— 更倾向于希望使用较少的列，而实践中性能提升通常不会很明显。

考虑数据数据仓库的规模

与 Postgres 不同，基于云的数据仓库是从一开始就被设计为分布式系统，使之可以随着数据大小的增长而线性地提高处理能力。

一般来说，当数据库规模太大需要迁移为分布式系统的情况没有很好的经验法则，但当面临这种情况时，开发者很可能已具备处理迁移和权衡因素的专业知识。在一次简单的测试中，表行数在 50 到 100M 之间时 Postgres 的表现非常好（通常与 Redshift 等数据仓库表现结果一致）。

但是性能取决于很多因素：磁盘与固态硬盘，CPU，数据结构，查询类型等，因此不进行直接的测试很难一概而论。

如果要将 Postgres 扩展到数十亿行，可以考虑使用 Citus。

Next Post

如何用图论解决 Secret Santa 问题？（译）
Previous Post

Linux 内存原理与分析

CATALOG

1. 把 PostgreSQL 用作数据仓库