This page in other versions: 0.9 / 1.0 / 1.0.3

10.1. Purpose of global sequences

Many applications require unique values be assigned to database entries. Some applications use GUIDs generated by external programs, some use database-supplied values. This is important with optimistic conflict resolution schemes (like that in BDR) because uniqueness violations are "divergent errors" and are not easily resolvable.

The SQL standard requires SEQUENCE objects which generate unique values. These can then be used to supply default values using DEFAULT nextval('mysequence'), as with PostgreSQL's SERIAL pseudo-type. PostgreSQL doesn't provide any facilities to synchronise or replicate sequences, so they're purely node-local.

A typical approach for sharded or multi-node applications is to use split-step or partitioned sequences, where all nodes increment the sequence by the same fixed value and each node has a fixed offset within the sequence. So node 1 generates IDs 1, 101, 201, 301, ...; node 2 generates IDs 2, 102, 202, 302, ...; etc. This is easily done with PostgreSQL's existing sequences, but becomes a major problem if you don't allow enough room for growth - in the above, if you have 101 nodes you're in serious trouble. It's also awkward, requiring node-specific DDL and setup. See Section 10.7.

To help avoid Multi-master conflicts on concurrent inserts BDR provides global sequences. A global sequence looks to applications just like a normal PostgreSQL sequence and values are allocated from it using nextval like normal. Unlike a normal PostgreSQL sequence, which is not synchronized between nodes in any way, global sequences are guaranteed to produce unique values across all members of a bdr node group.