This page in other versions: 0.9 / 1.0 / 1.0.3

10.2. When to use global sequences

Using distributed sequences allows you to avoid the problems with insert conflicts. If you define a PRIMARY KEY or UNIQUE column with a DEFAULT nextval(...) expression that refers to a global sequence shared across all nodes in a BDR group it is not possible for any node to ever get the same value as any other node. When BDR synchronizes inserts between the nodes, they can never conflict.

There is no need to use a distributed sequence if:

Global sequences are not suitable for applications that require an INCREMENT other than 1. See Global Sequence Limitations.

Sequence values are handed out in chunks, so if three different nodes all call nextval at the same time they might get values 50, 150 and 250. Thus, at time 't' nextval on one node may return a value higher than a nextval call at time 't+1' on another node. Within a single node the usual rules for nextval still apply. This means that applications that require generated IDs to strictly increase with time will not work well with global sequences - however, such applications will also have problems with all other approaches to generating keys in distributed systems (see Section 10.7).

Global sequences are not suitable for 2-node BDR systems since a quorum majority is required to allocate new sequence chunks. Additionally, global sequences don't work when there is only one node configured.

Because chunk allocation requires voting between nodes, global sequences are not suited for deployments where nodes may be isolated for longer than it takes the application to consume a sequence chunk, since queries trying to allocate a new value will ERROR if there are none available.