PostgreSQL

PostgreSQL:

PostgreSQL是一种对象-关系型数据库管理系统（Oracle也属于此类），有plpgsql结构化语言，任意精度的数值类型、无限长度的文本类型，对象及继承概念等。

Greenplum:

Greenplum是一种MPP(Massively Parallel Processing大规模并行处理)架构的关系型数据库集群，是由数个独立的数据库服务组合成的逻辑数据库（单个数据库服务以PostgreSQL为内核）。

Greenplum采用Shared-Nothing架构，整个集群由很多个数据节点（SegmentHost）和控制节点（master Host）组成，其中每个数据节点上可以运行多个数据库。简单来说，Shared-Nothing是 一个分部式的架构，每个节点相对独立。在典型的Shared-Nothing中，每一个节点上所有的资源（CPU，内存，磁盘）都是独立的，每个节点都只有全部数据的一部分，也只能使用本节点的资源。

在Greenplum中，需要存储的数据在进入数据库时，将先进行数据分布的处理工作，将一个表中的数据平均分布到每个节点上，并为每个表指定一个分布列（distribute Column），之后便根据Hash来分布数据。基于Shared-Nothing的原则，Greenplum这样处理可以充分发挥每个节点处I/O的处理能力。

Greenplum主要定位在OLAP领域。

数据表管理：

Greenplum表中的行被分布在不同Segment上，表的分布策略决定了在不同Segment上面的分布情况。

创建表时可以定义以下内容：

表的列以及数据类型、表约束的定义、表分布定义、表存储格式、分区表定义

CREATE TABLE table_name(


[{column_namedata_type [DEFAULT default_expr]             -- 表的列定义


   [column_constraint [...]                                 -- 列的约束定义


]


   | table_constraint                                       -- 表级别的约束定义                           


   ])


   [WITH ( storage_parameter=value [, ... ])               -- 表存储格式定义


   [DISTRIBUTED BY (column, [...] ) |DISTRIBUTED RANDOMLY] -- 表的分布键定义


   [partition clause]                                       -- 表的分区定义

可以在列和表上定义约束来限制表中的数据，但是有以下一些限制：

• CHECK约束引用的列只能在其所在的表中。
• UNIQUE和PRIMARY KEY约束必须包含分布键列，UNIQUE和PRIMARY KEY约束不支持追加优化表和列存表。
• 允许FOREIGNKEY 约束，但实际上并不会做外键约束检查。
• 分区表上的约束必须应用到所有的分区表上，不能只应用于部分分区表。

包含主键约束的表必须是哈希分布，并且约束列需要包含分布键列。如果一个表具有主键，这个列（或者这一组列）会被默认选中为该表的分布键

表分布定义：

Greenplum支持三种数据在节点间的分布方式，按指定列的哈希（HASH）分布、随机（RANDOMLY）分布、复制（REPLICATED）分布。

• DISTRIBUTEDBY (column, [ ... ])指定数据按分布列的哈希值在节点（Segment）间分布，根据分布列哈希值将每一行分配给特定节点（Segment）。相同的值将始终散列到同一个节点。选择唯一的分布键（例如Primary Key）将确保较均匀的数据分布。哈希分布是表的默认分布策略， 如果创建表时未提供DISTRIBUTED子句，则将PRIMARY KEY或表的第一个合格列用作分布键。如果表中没有合格的列，则退化为随机分布策略。
• DISTRIBUTEDRANDOMLY指定数据按循环的方式均匀分配在各节点 （Segment） 间，与哈希分布策略不同，具有相同值的数据行不一定位于同一个segment上。虽然随机分布确保了数据的平均分布，但只建议当表没有合适的离散分布的数据列作为哈希分布列时采用随机分布策略。
• DISTRIBUTEDREPLICATED 指定数据为复制分布，即每个节点（Segment）上有该表的全量数据，这种分布策略下表数据将均匀分布，因为每个segment都存储着同样的数据行，当有大表与小表join，把足够小的表指定为replicated也可能提升性能。

表分布键选择原则：

• 选择数据分布均匀的列或者多个列：若选择的分布列数值分布不均匀，则可能导致数据倾斜。某些Segment分区节点存储数据多(查询负载高)。根据木桶原理，时间消耗会卡在数据多的节点上。故不应选择bool类型，时间日期类型数据作为分布键。
• 选择经常需要 JOIN 的列作为分布键，可以实现图一所示本地关联（Collocated JOIN）计算，即当JOIN键和分布键一致时，可以在 Segment分区节点内部完成JOIN。否则需要将一个表进行重分布（Redistribute motion）来实现图二所示重分布关联（Redistributed Join）或者广播其中小表(Broadcastmotion)来实现图三所示广播关联（Broadcast Join），后两种方式都会有较大的网络开销。
• 尽量选择高频率出现的查询条件列作为分布键，从而可能实现按分布键做节点 segment 的裁剪。
• 若未指定分布键，默认表的主键为分布键，若表没有主键，则默认将第一列当做分布键。
• 分布键可以被定义为一个或多个列。例如：create table t1(c1 int, c2 int) distributed by (c1,c2);
• 谨慎选择随机分布DISTRIBUTED RANDOMLY，这将使得上述本地关联，或者节点裁剪不可能实现。

表分布键的约束：

• 分布键的列，不能被更新（UPDATE）。
• 主键和唯一键必须包含分布键。
• Geometry类型和用户自定义数据类型不能作为分布键。

表分区定义：

Greenplum可以将大表定义为分区表，从而将其分成较小的存储单元，根据查询条件，会只扫描满足条件的分区而避免全表扫描，从而显著提升查询性能。

支持的表分区类型：

• 范围（RANGE）分区：基于一个数值型范围划分数据，例如按着日期区间定义。
• 值（LIST）分区：基于一个值列表划分数据，例如按着城市属性定义。
• 多级分区表：上述两种类型的多级组合。

分区定义的粒度：

通常分区表的定义都涉及到粒度问题，比如按时间分区，究竟是按天，按周，按月等。粒度越细，每张表的数据就越少，但是分区的数量就越多，反之亦然。关于分区的数量，没有绝对的标准，一般分区的数量在 200 左右已经算是比较多了。分区表数目过多，会有多方面的影响，比如查询优化器生成执行计划较慢，同时很多维护工作也会变慢，比如VACUUM等。

请对多级分区格外谨慎，因为分区文件的数量可能会增长得非常快。例如，如果一个表被按照月和城市划分并且有24个月以及1,00个城市，那么表分区的总数就是2400。特别对于列存表，会把每一列存在一个物理表中，因此如果这个表有100个列，系统就需要为该表管理十多万个文件。

分区表查询优化：

支持分区表的分区裁剪功能，根据查询条件会只扫描所需的数据分区而避免扫描整个表的全部内容，提升查询性能。例如对于如下查询：

explain 
  select * from sales 
  where year = 2008 
    and  month = 1 
    and  day = 3 
    and region = 'usa';

由于查询条件落在一级分区2008的二级子分区1的三级子分区 'usa' 上，查询只会扫描读取这一个三级子分区数据。如下其查询计划显示，总计468个三级子分区中，只有一个分区被读取。

Gather Motion 4:1 (slice1; segments: 4) (cost=0.00..431.00 rows=1 width=24)
  ->  Sequence  (cost=0.00..431.00 rows=1 width=24)
        ->  Partition Selector for sales (dynamic scan id: 1) (cost=10.00..100.00 rows=25 width=4)
              Filter: year = 2008 AND month = 1 AND region = 'usa'::text
              Partitions selected:  1 (out of 468)
        ->  Dynamic Table Scan on sales (dynamic scan id: 1) (cost=0.00..431.00 rows=1 width=24)
              Filter: year = 2008 AND month = 1 AND day = 3 AND region = 'usa'::text

表存储格式定义：

Greenplum支持多种存储格式。当创建一个表时，可以选择表的存储格式为行存表或者列存表。

• 行存表

默认情况下创建的是行存表（Heap Table），使用的 PostgreSQL 堆存储模型。行存表适合数据更新较频繁的场景，或者采用INSERT方式的实时写入的场景，同时当行存表建有B-Tree索引时，具备更好的点查询数据检索性能。

• 列存表

列存表（Column-Oriented Table）的按列存储格式，数据访问只会读取涉及的列，适合少量列的数据查询、聚集等数据仓库应用场景，在此类场景中，列存表能够提供更高效的I/O。但列存表不适合频繁的更新操作或者大批量的INSERT写入场景，这时其效率较低。列存表的数据写入建议采用COPY等批量加载方式。列存表可以提供平均 3-5倍的较高数据压缩率。

列存表必须是追加优化表。例如，要创建一个列存表，必须指定为"appendonly=true"。

• 压缩

压缩主要用于列存表或者追加写（"appendonly=true"）的行存表，有以下两种类型的压缩可用：

    应用于整个表的表级压缩。

    应用到指定列的列级压缩。用户可以为不同的列应用不同的列级压缩算法。

支持的压缩算法如下：

    4.3 版本支持zlib、rle_type

    6.0 版本支持zstd、zlib、rle_type、lz4

也可以指定QuickLZ压缩算法，但内部会使用zlib算法替换，另外rle_type算法只适用于列存表。

​

关注公众号 soft张三丰