Greenplum 存储及使用场景

Greenplum支持行存和列存，对应的是堆表和AO表

行存和列存的原理
1、行存，以行为形式组织存储，

查询的时候需要全表扫描要扫描更多的数据块；压缩比较低；读取任意列的成本不一样，越靠后的列，成本越高。

2、列存，以列为形式组织存储，每列对应一个或一批文件。读取任一列的成本是一样的，但是如果要读取多列，需要访问多个文件，访问的列越多，开销越大

压缩比高；读取任意列的成本是一样的；非常适合向量计算、JIT架构。对大批量数据的访问和统计，效率更高；读取很多列时，由于需要访问更多的文件，成本更高

存储格式介绍
Greenplum（以下简称GP）有2种存储格式，Heap表和AO表（AORO表，AOCO表）。

Heap表：这种存储格式是从PostgreSQL继承而来的，目前是GP默认的表存储格式，只支持行存储。
AO表： AO表初设计是只支持append的（就是只能insert），因此全称是Append-Only，在4.3之后进行了优化，目前已经可以update和delete了，全称也改为Append-Optimized。AO支持行存储（AORO）和列存储（AOCO）。
Heap表

Heap表是从PostgreSQL继承而来，使用MVCC来实现一致性。如果你在创建表的时候没有指定任何存储格式，那么GP就会使用Heap表。

Heap表支持分区表，只支持行存，不支持列存和压缩。需要注意的是在处理update和delete的时候，Heap表并没有真正删除数据，而只是依靠version信息屏蔽老的数据，因此如果你的表有大量的update或者delete，表占用的物理空间会不断增大，这个时候需要依靠vacuum来清理老数据。

Heap表不支持逻辑增量备份，因此如果要对Heap表做快照，每次都需要导出全量数据。

建表语句

CREATE TABLE heap( a int, b varchar(32) ) DISTRIBUTED BY (a);

-佳实践：

如果该表是一张小表，比如数仓中的维度表，或者数据量在百万以下，推荐使用Heap表。
如果该表的使用场景是OLTP的，比如有较多的update和delete，查询多是带索引的点查询等，推荐使用Heap表。
AO表

AO表是GP特有的，设计的目的就是为了数仓中大型的事实表。AO表支持行存和列存，并且也支持对数据进行压缩。

AO表无论是在表的逻辑结构还是物理结构上，都与Heap表有很大的不同。比如上文所述Heap表使用MVCC控制update和delete之后数据的可见性，而AO表则使用一个附加的bitmap表来实现，这个表的的内容就是表示AO表中哪些数据是可见的。

对于有大量update和delete的AO表，同样需要vacuum进行维护，不过在AO表中，vacuum需要对bitmap进行重置并压缩物理文件，因此通常比Heap的vacuum要慢。

AORO表

AORO就是行存的AO表，同时行存也是AO表的默认存储方式。

AORO支持表级别的压缩，不支持列级别的压缩。

建表语句如下，重点是with后的appendonly=true，由于AO表默认是行存，因此orientation=row也可以不要，后面的compresstype=zlib, compresslevel=4都是压缩相关选项。

CREATE TABLE aoro( a int, b int, c varchar(32), d varchar(32) ) WITH (appendonly=true, orientation=row, compresstype=zlib, compresslevel=4) DISTRIBUTED BY (a)

压缩选项：

compresstype ：压缩格式，开源版本的AORO表只支持zlib。
compresslevel ：压缩级别，从1-9，简单说来，级别越低（1低），压缩比越低，但是压缩与解压消耗的cpu资源就越少。默认压缩级别是1。
-佳实践______：

AO表主要是针对大表，比如数仓中的事实表。
AO表支持逻辑增量备份，对于比较大的表，如果需要定期做快照，建议使用AO表，否则每次都要导出全量数据。
如果该表是大表，使用场景偏OLTP并且update和delete频率不高，可以考虑使用AORO表。
如果该表是大表，并且查询通常都需要扫描大多数列比如查询明细（典型的就是SELECT * FROM），可以考虑使用AORO表。
在设置压缩级别的时候，通常对于Snova用户，设置到4或者5是比较折中的一个选择。
AOCO表

AOCO表就是列存的AO表。

AOCO不仅支持表级别的压缩，同时也支持列级别的压缩。

CREATE TABLE aoco( a int ENCODING (compresstype=zlib, compresslevel=5), b int ENCODING (compresstype=none), c varchar(32) ENCODING (compresstype=RLE_TYPE, blocksize=32768), d varchar(32), fdate date ) WITH (appendonly=true, orientation=column, compresstype=zlib, compresslevel=6, blocksize=65536) DISTRIBUTED BY (a) PARTITION BY RANGE(fdate) ( PARTITION pn START ('2018-11-01'::date) END ('2018-11-10'::date) EVERY ('1 day'::interval), DEFAULT PARTITION pdefault );

压缩选项：

compresstype：支持2种压缩格式，zlib和RLE_TYPE，其中RLE_TYPE（Run-length Encoding）对于有较多重复值的列压缩比很高，因为它会将多个重复值存储为一个值，从而大大降低存储量，比如日期，性别，年龄等字段。
compresslevel：compresstype如果是zlib，compresslevel在1-9，compresstype如果是RLE_TYPE，compresslevel在1-4。
列压缩与表压缩：AOCO表除了支持表级别的压缩外，还支持列级别的压缩，列级别的压缩配置会覆盖表级别的压缩配置，比如上述语法中4个字段，每个字段都采用了不用的压缩方式，d列没有定义，则会默认使用表级别的压缩方式。
分区压缩：在使用分区表的时候，每个分区表也可以设置不同的压缩配置，这个常用于对数据进行冷热分离，比如对于非常老的数据，由于访问频率较低，可以考虑采用较大的压缩比，减少存储量。
BLOCKSIZE：

表的存储块大小，通常表数据对应的物理文件就是按blocksize的粒度增加，也就是初始就是blocksize大，并且保持blocksize的倍数。
blocksize大小在8192和2097152之间，必须是8192的倍数，默认是32768。
在AOCO表中，每一列也可以设置自己的blocksize，列的配置会覆盖表的配置。
物理文件：

AOCO表之所以能够按照列来设置压缩等参数，本质原因在于AOCO表中每一列的数据都会单独存储在一个文件中。因此不同文件之间可以按不同的参数进行存储，互不影响。
对于AOCO表，如果使用了分区，那么对于每一个分区的每一列都会有一个文件，如果一个表的分区很多，又是一张大宽表，那么产生的文件就会很多，也会对性能有一些影响。
-佳实践：

AOCO表通常用于数仓中的核心事实表，这种表字段多，数据量大，主要是用于OLAP场景，也就是查询的过程不会SELECT * FROM，而是对其中部分字段进行读取和聚合。
由于AOCO表一般用于大表，因此经常搭配压缩和分区，以减少表的实际存储量来提升性能。
一般情况下，压缩格式选择zlib，压缩级别可以采用折中的4或者5，但是对于有大量重复值的字段，记得要采用RLE_TYPE压缩格式。
blocksize不要设置过大，特别是对于分区表，GP对于每个分区的每个字段都会维护一个buffer，blocksize过大，会导致消耗的内存过大，通常就采用默认值32768即可。
修改表结构

单独列出这一节是因为Heap，AORO和AOCO这3种表在修改表结构时表现是不一样的，这也是大家容易忽视的地方。

对于不同的表类型，同样的修改语法耗时可能会差异很多，主要原因在于对于有些修改操作会导致表重写，而表重写的时间就取决于表本身的数据量。

混合存储

一张表是否可以同时使用多种存储方式呢？对于分区表，是可以的。

混合存储一般用于这样的场景，对于一张按时间分区的表，通常对于不同时间点的数据行为是不一样的，比如对于近的数据，会有较多的明细查询，而对于比较老的数据，则是以分析为主。同时由于业务可能要保存较长时间的数据，为了节约成本，较老的数据会考虑使用压缩比较大的存储方式。

混合存储的关键就是使用到了GP的交换分区语法，也就是将一张独立的表与自己的一个分区表进行交换，当然这里前提是新表的结构是一样，并且交换的过程没有新数据进入。

流程如下：

1.创建一张分区表(1到5月份，每月一张表)，采用Heap存储

CREATE TABLE hyper_storage ( a int, b varchar(32), fdate date ) DISTRIBUTED BY (a) PARTITION BY RANGE(fdate) ( PARTITION pn START ('2018-01-01'::date) END ('2018-06-01'::date) EVERY ('1 month'::interval), DEFAULT PARTITION pdefault ); storage=# \d List of relations Schema | Name | Type | Owner | Storage --------+------------------------------+-------+--------------+--------- public | hyper_storage | table | test | heap public | hyper_storage_1_prt_pdefault | table | test | heap public | hyper_storage_1_prt_pn_1 | table | test | heap public | hyper_storage_1_prt_pn_2 | table | test | heap public | hyper_storage_1_prt_pn_3 | table | test | heap public | hyper_storage_1_prt_pn_4 | table | test | heap public | hyper_storage_1_prt_pn_5 | table | test | heap

2.现在要对1月份的表修改存储格式，因此创建一张新的AOCO表

CREATE TABLE exchange_table( a int, b varchar(32), fdate date ) WITH (appendonly=true, ORIENTATION=column, compresstype=zlib, compresslevel=6) DISTRIBUTED BY (a)

3.将1月份的数据导入新表

INSERT INTO exchange_table SELECT * FROM hyper_storage_1_prt_pn_1;

4.交换分区

ALTER TABLE hyper_storage EXCHANGE PARTITION pn_1 WITH TABLE exchange_table;

注：pn_1是1月份的分区表的partitionname，可以从pg_partitions中查询得到

5.查看结果

storage=# \d List of relations Schema | Name | Type | Owner | Storage --------+------------------------------+-------+--------------+---------------------- public | hyper_storage | table | test | heap public | hyper_storage_1_prt_pdefault | table | test | heap public | hyper_storage_1_prt_pn_1 | table | test | append only columnar public | hyper_storage_1_prt_pn_2 | table | test | heap public | hyper_storage_1_prt_pn_3 | table | test | heap public | hyper_storage_1_prt_pn_4 | table | test | heap public | hyper_storage_1_prt_pn_5 | table | test | heap public | exchange_table | table | test | heap

这样1月份的分区表就变成了AOCO表，而其他分区表仍然是Heap表

选取Heap，AORO，AOCO三种表，分别采用压缩和不压缩2种方式（Heap表不支持压缩，AO表压缩采用zlib格式，压缩级别设置为6），插入5亿条随机数据，然后使用

select pg_size_pretty(pg_relation_size('{tablename}'));

Heap表占用空间更大，即使AO表不采用压缩。AOCO表由于是按列进行存储，所以相比行存的AORO表压缩比更大。当然这三者的差距取决于数据的实际情况，一般生产环境中Heap表不会和AO表有如此大的差距。



使用AOCO表，zlib压缩格式，选取不同的压缩级别，比较数据写入时间和表所占大小，由于zlib支持9个级别，这里选取1,6,9 三个级别进行比较，体现出趋势即可。实际生产环境中不同压缩级别的数据，压缩比的差距可能会更大。但可以看出，越高的压缩级别，在插入的时候越耗时，其它SQL，类似SELECT，UPDATE等也都是一样。


本文来源：https://blog.csdn.net/czq850114000/article/details/103953654