995
Greenplum Sequence機製
Sequence(序列)是數據庫經常使用自增列屬性，對於單機PostgreSQL實例，數據庫維護一個自增變量即可。但是對於Greenplum的MPP架構，如果每個節點都維護自己的Sequence，那麼Sequence將會出現重複，那麼Greenplum是如何處理的呢？
如何使用Sequence
create table test_sequence(id serial, name text);
postgres=> \d
public | test_sequence | table | postgres
public | test_sequence_id_seq | sequence | postgres
postgres=> insert into test_sequence (name) values(1);
INSERT 0 1
postgres=> insert into test_sequence (name) values(2);
INSERT 0 1
postgres=> insert into test_sequence (name) values(3);
INSERT 0 1
postgres=> select * from test_sequence;
id | name
----+------
3 | 3
1 | 1
2 | 2
(3 rows)
Sequence是誰維護
查看Master的Sequence維護元信息
postgres=> select * from test_sequence_id_seq;
sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called
----------------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
test_sequence_id_seq | 3 | 1 | 9223372036854775807 | 1 | 1 | 30 | f | t
(1 row)
查看Segment的Sequence維護元信息
postgres=> select * from gp_dist_random('test_sequence_id_seq');
sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called
----------------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
test_sequence_id_seq | 1 | 1 | 9223372036854775807 | 1 | 1 | 1 | f | f
test_sequence_id_seq | 1 | 1 | 9223372036854775807 | 1 | 1 | 1 | f | f
test_sequence_id_seq | 1 | 1 | 9223372036854775807 | 1 | 1 | 1 | f | f
test_sequence_id_seq | 1 | 1 | 9223372036854775807 | 1 | 1 | 1 | f | f
(4 rows)
通過Master和Segment的元信息可以看出，隻有Master一直更新元信息，Segment的Sequence元信息一直不變，所以Sequence是由Master維護的。
Sequence分配過程
如何所示，Master上有一個seqserver進程，專門用來維護全局的Sequence信息。所有的Segment獲取最新的Sequence都需要向Master的seqserver請求，然後seqserver更新Sequence雲信息，返回給Segment。為了實現Sequence，Master和Segment多了一次交互，這樣會影響性能，建議應用層生成自增值。
最後更新：2017-08-29 10:03:06