Greenplum 列存储加字段现象 - AO列存储未使用相对偏移
背景
Greenplum的Append only table支持更新、删除。通过什么支持呢?bitmap文件,标记被删除的行。
因此在更新,删除后,数据可能膨胀。
另一方面,列存储每列一个文件,同一行通过偏移对应起来。例如INT8的两个字段,通过偏移很快能找到某一行的A列对应的B列。
接下来谈谈加字段,在加字段时,AO表示不会REWRITE TABLE的。如果AO表以及有一些垃圾(被删除的数据)记录后,加的字段会使用相对偏移吗(换句话说,需不需要填充已删除的记录),答案是要填充。
例子
1、创建3张AO列存表。
postgres=# create table tbl1 (id int, info text) with (appendonly=true, blocksize=8192, compresstype=none, orientation=column);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
postgres=# create table tbl2 (id int, info text) with (appendonly=true, blocksize=8192, compresstype=none, orientation=column);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
postgres=# create table tbl3 (id int, info text) with (appendonly=true, blocksize=8192, compresstype=none, orientation=column);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
2、前两张分别插入1000万记录,最后一张插入2000万记录。
postgres=# insertinto tbl1 select generate_series(1,10000000),'test';
INSERT 0 10000000
postgres=# insert into tbl2 select generate_series(1,10000000),'test';
INSERT 0 10000000
postgres=# insert into tbl3 select generate_series(1,20000000),'test';
INSERT 0 20000000
3、分析表,并记录它们的大小
postgres=# analyze tbl1;
ANALYZE
postgres=# analyze tbl2;
ANALYZE
postgres=# analyze tbl3;
ANALYZE
postgres=# select pg_size_pretty(pg_relation_size('tbl1'));
pg_size_pretty
----------------
88 MB
(1 row)
postgres=# select pg_size_pretty(pg_relation_size('tbl2'));
pg_size_pretty
----------------
88 MB
(1 row)
postgres=# select pg_size_pretty(pg_relation_size('tbl3'));
pg_size_pretty
----------------
173 MB
(1 row)
4、更新第一张表,全表更新。并记录更新后的大小,翻了一倍。
postgres=# update tbl1 set info='test';
UPDATE 10000000
postgres=# analyze tbl1;
ANALYZE
postgres=# select pg_size_pretty(pg_relation_size('tbl1'));
pg_size_pretty
----------------
173 MB
(1 row)
5、对三个表添加字段,设置默认值。
postgres=# alter table tbl1 add column c1 int8 default 1;
ALTER TABLE
postgres=# alter table tbl2 add column c1 int8 default 1;
ALTER TABLE
postgres=# alter table tbl3 add column c1 int8 default 1;
ALTER TABLE
6、分析表,查看表的大小。
postgres=# analyze tbl1;
ANALYZE
postgres=# analyze tbl2;
ANALYZE
postgres=# analyze tbl3;
ANALYZE
postgres=# select pg_size_pretty(pg_relation_size('tbl1'));
pg_size_pretty
----------------
325 MB
(1 row)
postgres=# select pg_size_pretty(pg_relation_size('tbl2'));
pg_size_pretty
----------------
163 MB
(1 row)
postgres=# select pg_size_pretty(pg_relation_size('tbl3'));
pg_size_pretty
----------------
325 MB
(1 row)
很显然,AO表在添加字段时,以已有文件的记录数为准(即使全部都删除了,也需要在新增字段上初始化这个值),。
如果有相对偏移量的概念,至少tbl2可以忽略前1000万行的偏移。
堆表不存在这个问题,因为堆表加字段会REWRITE全表。
表膨胀的处理
《如何检测、清理Greenplum垃圾 - 阿里云HybridDB for PG最佳实践》