PostgreSQL实战指南：多领域数据库应用与实践

第五部分：PostgreSQL与分布式数据库

虽然 PostgreSQL 是一个单机数据库的典范，但其强大的生态系统提供了多种构建分布式解决方案的途径。本部分将探讨如何通过逻辑复制、外部数据封装器（FDW）以及 CitusData、Greenplum 等专业扩展，将 PostgreSQL 从单机扩展到分布式集群，以应对海量数据和高并发的挑战。

第23章：Greenplum：基于PostgreSQL的MPP数据仓库

Greenplum 是一个基于 PostgreSQL 的、开源的大规模并行处理（Massively Parallel Processing, MPP）数据仓库平台。与旨在通过分片扩展 OLTP（在线事务处理）能力的 Citus 不同，Greenplum 专门为 OLAP（在线分析处理）和商业智能（BI）工作负载进行了优化。它能够对 PB（Petabyte）级别的海量数据执行复杂的分析型 SQL 查询。

23.1 Greenplum MPP 架构详解

Greenplum 采用无共享（Shared-Nothing）的 MPP 架构。

Master Node: 类似于 Citus 的协调节点，是系统的唯一入口。客户端连接到 Master 节点，提交 SQL 查询。Master 节点负责解析查询、生成并优化查询计划，然后将查询计划分发到所有的 Segment 节点上。它不存储用户数据。
Segment Nodes: 类似于 Citus 的工作节点，是存储数据和执行查询的主力。每个 Segment 节点上运行着一个或多个 PostgreSQL 数据库实例，这些实例被称为 Segment Instance。每个 Segment Instance 存储着数据的一部分。
Interconnect: 这是 Greenplum 内部的高速网络层，负责在 Master 和 Segments 之间、以及不同 Segments 之间传输数据。查询的中间结果（例如 JOIN 或 GROUP BY 产生的数据）通过 Interconnect 在节点间进行交换（称为 Motion）。

核心区别: 在 MPP 架构中，一个复杂的查询（如多表 JOIN 和聚合）会被分解成多个步骤，每个步骤都在所有的 Segment 节点上并行执行。这种架构使得 Greenplum 在处理需要扫描和聚合海量数据的分析型查询时，性能可以随节点数量的增加而线性提升。

23.2 数据分布策略

与 Citus 类似，数据如何分布在各个 Segment 上是 Greenplum 性能的关键。当你创建一个表时，必须指定一个分布策略。

哈希分布 (Hashed Distribution): 这是最常用的分布方式。你指定一个或多个列作为分布键（Distribution Key）。Greenplum 会计算分布键的哈希值，并根据哈希值将数据行分配到不同的 Segment Instance 上。
- 如何选择分布键？ 一个好的分布键应该能让数据均匀地分布在所有 Segment 上，避免数据倾斜（某些 Segment 的数据量远大于其他）。同时，如果经常需要 JOIN 的两个大表使用相同的分布键，那么 JOIN 操作就可以在 Segment 本地完成（Co-located JOIN），从而避免昂贵的数据重分布。
随机分布 (Randomly Distributed): 如果找不到一个合适的分布键，可以选择随机分布。数据会被循环地（Round-robin）分配到各个 Segment 上。这能保证数据分布的均匀性，但任何 JOIN 操作都可能需要数据重分布。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
-- 创建一个哈希分布的表
CREATE TABLE sales (
    sale_id BIGINT,
    product_id INTEGER,
    customer_id INTEGER,
    amount DECIMAL(10, 2)
) DISTRIBUTED BY (customer_id); -- 使用 customer_id 作为分布键

-- 创建一个随机分布的表
CREATE TABLE products (
    product_id INTEGER,
    name TEXT
) DISTRIBUTED RANDOMLY;

23.3 面向分析的核心特性

Greenplum 除了 MPP 架构外，还具备一系列为数据分析优化的特性。

列式存储 (Columnar Storage): Greenplum 支持行式存储和列式存储。对于分析型查询，通常只关心表中的少数几列。使用列式存储，查询时只需读取所需的列，可以极大地减少 I/O，提升性能。
数据压缩 (Compression): 列式存储与压缩技术是天作之合。Greenplum 支持多种压缩算法（如 Zlib, Zstandard），可以大幅降低存储成本。
外部表 (External Tables): 类似于 FDW，Greenplum 的外部表可以直接查询存储在外部系统（如 HDFS, S3, Web服务器）中的数据，无需预先加载。

23.4 场景实战：构建企业级销售数据仓库

业务场景描述:

一家大型零售企业需要构建一个数据仓库，用于分析其海量的历史销售数据。分析师需要能够快速地执行各种复杂的聚合查询，例如“按季度和区域统计各类产品的总销售额”。

数据建模与查询:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-- 1. 创建表，并选择合适的分布策略和存储方式
-- 事实表 (Fact Table): sales (非常大)
-- 使用列存和压缩
CREATE TABLE sales (
    sale_id BIGINT,
    date_id INTEGER,
    product_id INTEGER,
    region_id INTEGER,
    amount DECIMAL(10, 2)
)
WITH (appendoptimized=true, orientation=column, compresstype=zstd, compresslevel=5)
DISTRIBUTED BY (product_id); -- 按 product_id 分布，便于与 products 表 JOIN

-- 维度表 (Dimension Tables): 相对较小
CREATE TABLE dates (date_id INTEGER, full_date DATE, quarter INTEGER) DISTRIBUTED RANDOMLY;
CREATE TABLE products (product_id INTEGER, name TEXT, category TEXT) DISTRIBUTED BY (product_id);
CREATE TABLE regions (region_id INTEGER, name TEXT) DISTRIBUTED REPLICATED; -- Greenplum 6+ 支持复制表

-- 2. 加载数据 (通常使用 Greenplum 的并行加载工具 gpfdist)
-- COPY sales FROM ...

-- 3. 执行复杂的分析型查询
-- 查询会被 Greenplum 的查询优化器 (Orca) 分解，并在所有 Segment 上并行执行
SELECT
    d.quarter,
    r.name AS region_name,
    p.category,
    SUM(s.amount) AS total_sales
FROM
    sales s
JOIN
    dates d ON s.date_id = d.date_id
JOIN
    products p ON s.product_id = p.product_id
JOIN
    regions r ON s.region_id = r.region_id
WHERE
    d.full_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY
    d.quarter,
    r.name,
    p.category
ORDER BY
    d.quarter,
    total_sales DESC;

代码解释与思考:

DISTRIBUTED BY (product_id): 我们选择 product_id 作为 sales 和 products 两个大表的分布键。这样，当它们进行 JOIN 时，具有相同 product_id 的行保证在同一个 Segment 上，JOIN 可以本地高效完成。
DISTRIBUTED REPLICATED: 对于非常小且经常被 JOIN 的维度表（如 regions），可以将其定义为复制表（类似于 Citus 的参考表）。这样，每个 Segment 都会有这张表的完整副本，JOIN 效率最高。
WITH (...): 这个子句用于指定表的存储选项。appendoptimized=true 表示这是一个追加优化的表，orientation=column 指定了列式存储，compresstype 和 compresslevel 定义了压缩算法和级别。
查询并行化: 上面的 GROUP BY 查询在 Greenplum 中会被高度并行化。每个 Segment 会先在本地对它所拥有的数据进行 JOIN 和初步的聚合（Partial Aggregation），然后通过 Interconnect 网络将这些中间结果进行重分布和最终的聚合，从而得出最终结果。

23.5 总结

本章我们深入探讨了 Greenplum 这一基于 PostgreSQL 的 MPP 数据仓库。我们学习了其无共享的 MPP 架构、哈希和随机数据分布策略，以及列式存储、压缩等面向分析的核心特性。通过构建一个销售数据仓库的实例，我们看到了 Greenplum 在处理海量数据、执行复杂分析型查询方面的卓越性能。它与 Citus 互为补充，共同构成了 PostgreSQL 在分布式领域的完整解决方案。

PostgreSQL实战指南：多领域数据库应用与实践 - 第23章：Greenplum：基于PostgreSQL的MPP数据仓库

文章目录