PolarDB:Sequences

A sequence in PolarDB-X 1.0 generates a globally unique numeric sequence. It is a 64-bit number, which is equivalent to the Signed BIGINT type in MySQL. A sequence is commonly used to generate values for primary key and unique index columns.

Key concepts

These concepts will help you choose the right sequence type:

• Consecutive: A sequence is consecutive if the next value is always n + 1, where n is the current value. If the next value is not guaranteed to be n + 1, the sequence is non-consecutive.
• Monotonically increasing: A sequence is monotonically increasing if the next value is always greater than the current value n.
• Single point: The sequence has a single point of failure (SPOF).
• Monotonically increasing at a macro level, but not at a micro level: This term refers to a sequence like 1, 3, 2, 4, 5, 7, 6, 8, .... This sequence is generally increasing over the long term but is not strictly increasing between adjacent values.
• Unitization capability: The ability to distribute a globally unique numeric sequence across multiple instances or databases.

Usage

In PolarDB-X 1.0, sequences have two primary uses:

• Explicit sequence: Created and managed by using Sequence DDL syntax and can be used independently. You can retrieve the next value by using select seq.nextval, where seq is the name of the sequence.
• Implicit sequence: Automatically populates a primary key when you define an AUTO_INCREMENT attribute for the primary key column. PolarDB-X 1.0 manages this sequence automatically.

Supported sequence types

PolarDB-X 1.0 supports the following four sequence types:

Group sequence (GROUP, default)

This sequence is globally unique and generates natural numbers, but it does not guarantee that the values are consecutive or monotonically increasing. If you do not specify a sequence type, PolarDB-X 1.0 uses a group sequence by default.

It works by using multiple nodes to generate values, which ensures high availability. The system retrieves a block of values at a time. If this block is not fully used, for example, if a connection is dropped, a sequence gap occurs.

• Advantages: Globally unique, no single point of failure, and excellent performance.
• Disadvantages: The sequence is not consecutive and may have sequence gaps. It does not strictly start from the specified start value and does not support cycles.

Unit group sequence (GROUP)

A unit group sequence extends the group sequence with unitization capability, enabling it to generate globally unique values across instances or databases, but it also does not guarantee consecutive or monotonically increasing values. When configured with a single unit, a unit group sequence is equivalent to a regular group sequence.

• Advantages: Has all the advantages of a group sequence and adds unitization capability.
• Disadvantages: The sequence is not consecutive and may have sequence gaps. It does not strictly start from the specified start value and does not support cycles.

It operates on the same principle as a group sequence. It supports custom unit counts and unit indexes through extended parameter options:

• The number of units determines the allocation space for the globally unique numeric sequence.
• Each unit, specified by a unit index, occupies a subset of this allocation space.
• The subsets for different units (with different unit indexes) do not overlap, which means they will not generate the same sequence values.
• All unit group sequences that share the same allocation space must be configured with the same number of units and different unit indexes.

Time-based sequence (TIME)

A time-based sequence generates values by combining a timestamp, a node ID, and a serial number. This ensures that the sequence is globally unique and increasing on a macro scale. The generated values are not persisted to the database; only the name and type information are retained in the database. This design provides excellent performance. It produces values similar to 776668092129345536, 776668098018148352, 776668111578333184, 776668114812141568, ....

• Advantages: Globally unique and excellent performance.
• Disadvantages: The sequence is not consecutive. Parameters such as start value, step size, maximum value, and cycle are not applicable to a time-based sequence.

A simple sequence is the only type that allows you to configure the step size, maximum value, and cycle.

• Advantages: Globally unique, consecutive, and monotonically increasing. It supports features like setting a maximum value and enabling a cycle.
• Disadvantages: It introduces a single point of failure, has poor performance, and can become a bottleneck. Use this type with caution.

Each generated value requires a persistence operation.

Use cases

All sequence types guarantee global uniqueness and can be used for primary key columns and unique index columns.

• For most use cases, we recommend using a group sequence.
• If you need to distribute a globally unique numeric sequence across instances or databases, use a unit group sequence.
• If your application can accept values that are only increasing on a macro scale and you want to avoid a database-dependent allocation mechanism, a time-based sequence is a good option.
• If your application strictly requires consecutive sequence values, you must use a simple sequence. Be aware of its performance limitations.

For example, consider creating a sequence with a start value of 100000 and a step size of 1.

• If you use a simple sequence, it strictly generates a globally unique, consecutive, and monotonically increasing sequence, such as 100000, 100001, 100002, 100003, 100004, ..., 200000, 200001, 200002, 200003, .... A simple sequence ensures persistence. Even if a single point of failure occurs, the service resumes generating values from the breakpoint, without creating a sequence gap. This mechanism requires a persistence operation for every generated value, which results in poor performance.
• If you use a group sequence or a unit group sequence, the generated sequence might look like this: 200001, 200002, 200003, 200004, 100001, 100002, 100003, ....