PolarDB:Overview

A sequence provided by Distributed Relational Database Service (PolarDB-X 1.0) generates globally unique numeric values. DRDS sequence values are of the MySQL BIGINT data type that stores signed 64-bit integers. The term DRDS sequence is referred to as sequence in the following description. Sequences are often used to generate globally unique and sequentially incremental numeric values, such as values of primary key columns and values of unique index columns.

Terms

After you understand the following terms, you can select a sequence type that is suitable for your business:

• Consecutive: If the current value in a consecutive sequence is n, the next value must be n + 1. If the next value is not n + 1, the sequence is a nonconsecutive sequence.
• Monotonically increasing: If the current value in a monotonic increasing sequence is n, the next value must be a number greater than n.
• Single point: A single point of failure (SPOF) risk exists.
• Monotonically increasing at the macro level and non-monotonically increasing at the micro level: For example, the values of a sequence can be 1, 3, 2, 4, 5, 7, 6, 8, ...Such a sequence is monotonically increasing at the macro level and non-monotonically increasing at the micro level.
• Unitization capability: The unitization capability can help you generate numeric sequences that are unique among multiple instances or multiple databases.

Usage

PolarDB-X 1.0 sequences are divided into the following two types:

• Explicit sequence: Use the DDL syntax to create and maintain an explicit sequence. An explicit sequence can be independently used. For example, you can directly modify and query an explicit sequence. You can use select seq.nextval to obtain the values in an explicit sequence. seq specifies the name of the sequence.
• Implicit sequence: If you specify the AUTO_INCREMENT attribute for a primary key column, an implicit sequence can be used to automatically generate primary key values. PolarDB-X 1.0 automatically maintains the sequence.

Supported types and features of sequences

PolarDB-X 1.0 supports the following four types of sequences.

Group sequence (GROUP, default sequence type)

A group sequence is a globally unique sequence that provides natural numeric values. Values in a group sequence do not need to be consecutive or monotonically increasing. If you do not specify a sequence type, PolarDB-X 1.0 uses the group sequence type by default.

Implementation mechanism: DRDS uses multiple nodes to generate sequence values. The multi-node model ensures high availability. The system retrieves a segment of values from a database at a time. In scenarios such as network disconnections, not all the values in a segment are used. Therefore, the sequence values are nonconsecutive.

• Advantages: Group sequences are globally unique and prevent SPOFs. Group sequences deliver excellent performance.
• Disadvantages: Group sequences may contain nonconsecutive values and may not start from the specified start value. The values of group sequences cannot be cyclical.

Unit group sequence (GROUP)

Unit group sequences extend the capabilities of group sequences. A unit group sequence has unitization capabilities and can provide values that are unique among multiple instances or multiple databases. Values in unit group sequences may not be consecutive or monotonically increasing. If only one unit is configured for a unit group sequence, the unit group sequence is equivalent to a common group sequence.

• Advantages: Unit group sequences have all the advantages of group sequences and have unitization capabilities.
• Disadvantages: Unit group sequences may contain nonconsecutive values and may not start from the specified start value. The values of unit group sequences cannot be cyclical.

The way how unit group sequences work is the same as the way how group sequences work. You can use the extension parameters to customize unit indexes and the number of units.

• The number of units determines the global unique sequence space assigned to the unit group sequence.
• A unit index identifies a unit. Each unit occupies a subset of the global unique sequence space.
• If you specify multiple unit indexes, multiple units are specified. The sequence subsets for different units do not overlap. This means that DRDS does not generate the same sequence value for different units.
• You must specify the same number of units and different unit indexes for all the unit group sequences that belong to the same sequence space.

Time-based sequence (TIME)

A time-based sequence value consists of a timestamp, node ID, and serial number. Such a sequence is globally unique and auto-incremental at the macro level. When the values of a time-based sequence are updated, the system does not retrieve values from a database. The sequence values are also not stored as persistent data in the related database. Only the sequence names and types are stored in the database. Time-based sequences deliver excellent performance. For example, the values of a time-based sequence can be 776668092129345536, 776668098018148352, 776668111578333184, 776668114812141568…

• Advantages: Time-based sequences are globally unique and deliver excellent performance.
• Disadvantages: The values of time-based sequences are nonconsecutive. The START WITH, INCREMENT BY, MAXVALUE, and CYCLE or NOCYCLE parameters are invalid for time-based sequences.

Only simple sequences support the INCREMENT BY, MAXVALUE, and CYCLE or NOCYCLE parameters.

• Advantages: Simple sequence values are globally unique, consecutive, and monotonically increasing. Simple sequences provide multiple features. For example, a simple sequence can have a maximum value and the values of a simple sequence can be cyclical.
• Disadvantages: Simple sequences are prone to SPOFs, low performance, and bottlenecks. Use simple sequences with caution.

Each time a simple sequence generates a value, the system stores the value as persistent data in the related database.

Scenarios

The four types of sequences are globally unique and can be used for primary key columns and unique index columns.

• In most scenarios, we recommend that you use group sequences.
• If you need sequence values that are globally unique among multiple instances or multiple databases, you can use unit group sequences.
• In some cases, your business may require the sequence values to be auto-incremental only at the macro level in the overall trend. The values are not necessarily auto-incremental at the micro level. You may also not want the sequence values to be allocated by using the allocation mechanism of a database. In such scenarios, you can use time-based sequences.
• Use only simple sequences for services that have high requirements for consecutive sequence values. Make sure that you understand the low performance of simple sequences.

For example, you can create a sequence that starts from 100000 and has a step size of 1.

• A simple sequence generates globally unique, consecutive, and monotonically increasing values, such as 100000, 100001, 100002, 100003, 100004, ..., 200000, 200001, 200002, 200003... Simple sequence values are persistently stored. Even after services are restarted upon an SPOF, values are still consecutively generated from the breakpoint. However, simple sequences have poor performance because each time a value is generated the system persistently stores the value.
• A group sequence or a unit group sequence may generate values such as 200001, 200002, 200003, 200004, 100001, 100002, 100003...