This page in other versions: 0.9 / 1.0 / 1.0.3

10.7. Traditional approaches to sequences in distributed DBs

Global sequences provide an application-transparent alternative to using offset-step sequences or UUID/GUID keys.

In offset-step sequences a normal PostgreSQL sequence is used on each node. Each sequence increments by the same amount and starts at differing offsets. For example with step 1000 node1's sequence generates 1001, 2001, 3001, and so on, node 2's generates 1002, 2002, 3002, etc. This scheme works well even if the nodes cannot communicate for extended periods, but requires that the designer specify a maximum number of nodes when establishing the schema and requires per-node configuration. Mistakes can easily lead to overlapping sequences.

UUID keys instead eschew sequences entirely and use 128-bit universal unique identifiers. These are large random or pseudorandom values that are large enough that it's nearly impossible for the same value to be generated twice. There is no need for nodes to have continuous communication when using UUID keys. In the incredibly unlikely event of a collision, manual operator intervention is required to repair the database before it can continue to replicate. The main downside of UUID keys is that they're space- and network-inefficient, consuming lots of space not only as a primary key, but also where referenced in foreign keys and when transmitted on the wire. Additionally, not all applications cope well with UUID keys.

BDR users may use either of these approaches. It is not necessary to use global sequences, and both the above key generation approaches continue to work normally. However, global sequences offer a zero-configuration, easy to deploy alternative that doesn't require application changes.