PolarDB:Overview

PolarDB-X provides sequences to generate globally unique and sequentially incremental numeric values, such as values of primary key columns and values of unique index key columns. Values that are generated by PolarDB-X sequences are of the MySQL BIGINT data type that stores signed 64-bit integers.

Terms

This section introduces the terms that are related to sequences to help you select a sequence type based on your business requirements.

• 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 monotonically increasing sequence is n, the next value must be a number greater than n.
• Monotonically increasing at the macro level and non-monotonically increasing at the micro level: For example, the sequence that contains 1, 3, 2, 4, 5, 7, 6, 8, ... is monotonically increasing at the macro level and non-monotonically increasing at the micro level.
• Unitization capability: The unitization capability allows you to create a GROUP sequence that contains multiple units across multiple instances or databases. Globally unique sequence values are generated across multiple instances or databases.

Use sequences

PolarDB-X sequences are divided into the following two categories:

• Explicit sequence: You can use DDL statements to create and maintain explicit sequences. An explicit sequence can be used in an independent manner. You can execute the SELECT seq.nextval statement to obtain 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 automatically maintains implicit sequences. For more information see AUTO_INCREMENT.

Supported types and features of sequences

PolarDB-X supports the following types of sequences.

New sequences

A new sequence generates values that are globally unique, consecutive, and monotonically increasing. By default, values of a new sequence are natural numeric values, and the start value of a new sequence is 1.

• Advantages: Global uniqueness, continuity, monotonically increasing, high performance, and support for the INCREMENT BY, MAXVALUE, and CYCLE or NOCYCLE parameters.
• Disadvantages: Dependence on GMS features and occupation of more GMS resources than other types of sequences.

Example:

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

Group sequences

A group sequence generates natural numeric values that are globally unique and start from 100001. Values in a group sequence do not need to be consecutive or monotonically increasing. By default, if you do not specify the UNIT COUNT parameter or INDEX parameter when you create a group sequence, a group sequence of only one unit is created. In most scenarios, only one unit is required.

PolarDB-X uses multiple nodes to generate values for a group sequence. This multi-node model ensures high availability of group sequences. The system retrieves a segment of values from a database at a time. In scenarios such as network disconnections, not all values in a segment are used. As a result, the sequence values are nonconsecutive.

• Advantages: group sequences generate globally unique values, provide high performance, and do not cause single points of failures. group sequences also can provide unique values across instances or databases.
• Disadvantages: group sequences may contain nonconsecutive values and may not start from the specified start value.

Example:

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

The actual start value of a group sequence may not be the start value that is specified. The actual start value can be a value that is greater than the start value that is specified. In this example, the specified start value is 100000. However, the actual start value of the sequence is 200001.

A group sequence generates globally unique values and can contain nonconsecutive values. For example, if a node fails or only some values in a segment of sequence values are used when the connection is closed, the sequence values are nonconsecutive values. In the preceding example, values between 200004 and 100001, and between 100003 and 200005 are not used.

Time sequences

A value of a time sequence consists of a timestamp, node ID, and serial number. A time sequence generates values that are globally unique and auto-increasing at the macro level. Updates of values in time sequences do not rely on databases. You only need to store sequence names and types in databases instead of storing sequence values in a persistent manner. This way, a time sequence can deliver excellent performance. Sample sequence values: 776668092129345536, 776668098018148352, 776668111578333184, 776668114812141568, ....

• Advantages: time sequences generate globally unique values and deliver excellent performance.
• Disadvantages: Values in a time sequence are nonconsecutive. The START WITH, INCREMENT BY, MAXVALUE, CYCLE, and NOCYCLE parameters do not take effect on time sequences.

Common scenarios

The three types of sequences generate globally unique values, and can be used in primary key columns and unique index key columns.

• If the kernel version of your PolarDB-X instance is V5.4.14 or later, we recommend that you use only NEW sequences in databases in AUTO mode.
• By default, group sequences are used in databases in PolarDB-X instances whose kernel versions are V5.4.13 and earlier, and databases in DRDS mode in PolarDB-X instances whose kernel versions are V5.4.14 and later.
• Group sequences can be used only for applications that consist of multiple units or that are migrated from PolarDB-X 1.0.
• Time sequences can be used only for applications that are migrated from PolarDB-X 1.0.