PolarDB:Overview

A PolarDB-X globally unique numeric sequence generates globally unique, ordered, and increasing numbers. This sequence, a 64-bit number corresponding to the MySQL Signed BIGINT data type, is commonly used to populate primary key columns and unique index columns.

Key concepts

Understanding the following concepts helps you choose the right type of sequence:

• Consecutive: A sequence is consecutive if the next value is always n + 1, where n is the current value. If this is not guaranteed, the sequence is non-consecutive.

• Monotonically increasing: A sequence is monotonically increasing if the next value is always greater than the current value.

• Macro-level monotonic increase: An example is a sequence like 1, 3, 2, 4, 5, 7, 6, 8, ... This sequence increases overall, but it is not strictly monotonic at each step.

• Unitization capability: The ability to allocate globally unique numeric sequences across instances or databases.

Usage

PolarDB-X provides two categories of sequences:

• Explicit sequence: You create and manage them with DDL statements, and you can use them independently. To retrieve the next value, run select seq.nextval, where seq is the sequence name.

• Implicit sequence: You define AUTO_INCREMENT for a primary key. PolarDB-X then automatically populates and maintains the primary key value.

Sequence types and features

PolarDB-X supports the following three types of sequences:

New Sequence (NEW)

A New Sequence generates globally unique, consecutive, and monotonically increasing values. By default, it produces a sequence of natural numbers starting from 1.

• Advantages: Provides globally unique, consecutive, and monotonically increasing values with high performance. It also supports custom steps, maximum value limits, and cyclic allocation.

• Disadvantages: Relies on the Global Meta Service (GMS), and consumes more GMS resources than other sequence types.

Example:

100000, 100001, 100002, 100003, 100004, ..., 199999, 200000, 200001, 200002, 200003, ...

Group Sequence (GROUP)

A Group Sequence generates globally unique values from a sequence of natural numbers. By default, values start from 100001, but they are not guaranteed to be consecutive or monotonically increasing. If you do not specify any unitization parameters, PolarDB-X creates a Group Sequence with a single unit, which is sufficient for most use cases.

How it works: To ensure high availability, a Group Sequence uses multiple nodes to generate values. It pre-fetches a block of values at a time. If this block is not fully used, for example, due to a dropped connection, gaps appear in the sequence.

• Advantages: Guarantees global uniqueness and avoids single points of failure, delivering excellent performance. It also provides unitization capability to work across instances or databases.

• Disadvantages: The generated sequence is not consecutive and may have gaps. It does not necessarily start at the configured start value.

Example:

200001, 200002, 200003, 200004, 100001, 100002, 100003, 200005, 200006, ...

The actual initial value allocated by a Group Sequence does not necessarily start at the configured start value, but is always greater than it.

A Group Sequence guarantees global uniqueness but can have gaps. For instance, if a node that generates the Group Sequence values fails, or if a connection retrieves only a portion of its allocated values before closing, gaps occur. In the preceding example, gaps appear between 200004 and 100001, and between 100003 and 200005.

Time-based Sequence (TIME)

A Time-based Sequence generates values by combining a timestamp, a node ID, and a serial number. This method guarantees global uniqueness and macro-level monotonic increase. Updates to this type of sequence do not depend on data nodes and are not persisted to the database. The system stores only the name and type information, resulting in excellent performance. A Time-based Sequence produces values similar to 776668092129345536, 776668098018148352, 776668111578333184, 776668114812141568, ....

• Advantages: Guarantees global uniqueness and delivers excellent performance.

• Disadvantages: The generated sequence is not consecutive. Parameters such as start value, step, maximum value, and cycle have no effect on a Time-based Sequence.

Use cases

All three sequence types guarantee globally unique values and can be used for primary key columns and unique index columns.

• For AUTO-mode databases in version 5.4.14 or later, we recommend that you use only New Sequence.

• DRDS-mode databases use Group Sequence by default.

• Use Group Sequence only for unitization scenarios and for compatibility with applications migrated from PolarDB-X 1.0.

• Use Time-based Sequence only for compatibility with applications migrated from PolarDB-X 1.0.