Conflict prevention with an MMR-ready sequence v7

To prevent uniqueness conflicts in a multi-master replication system, you can use an MMR-ready sequence to generate unique identifiers for each row of publication tables that doesn't have an inherent, unique identifier.

An MMR-ready sequence incorporates a function and a sequence to return BIGINT data type, integer values. These values combine a user-assigned, unique database identifier for each primary node with a sequence generated within that primary node.

You can modify a publication table requiring an MMR-ready sequence to include a BIGINT NOT NULL column with a default value returned by the function.

An MMR-ready sequence satisfies the following characteristics:

  • Uniqueness. The combination of the unique database identifier with the sequence ensures that each row in a given table has a unique value across all primary nodes.
  • Clustered index support. An MMR-ready sequence doesn't impair the use of a clustered index to provide retrieval efficiency. MMR-ready sequence values are returned in a typical, ordered sequence, not as random values such as if the universally unique identifier (UUID) were used.
  • Effective migration support. Yuo can modify tables already using a sequence to use an MMR-ready sequence with minimal impact on existing primary keys and foreign keys.
  • Reliability and maintainability. An MMR-ready sequence provides a reliable and maintainable method to avoid uniqueness conflicts.

create_mmr_ready_sequence mmr_ready_example convert_to_mmr_ready convert_to_mmr_ready_eg