This topic introduces the concepts related to sequences. This topic also describes the supported types of sequences.

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.

Concepts

This section introduces the concepts that are related to sequences. The knowledge of these concepts can help you select a sequence type that best suits 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 monotonically 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 a sequence that is similar to 1, 3, 2, 4, 5, 7, 6, 8,…, this sequence is increasing at the macro level and non-monotonically increasing at the micro level.
  • Unitization capability: The unitization capability allows you to generate sequence values that are globally unique among multiple instances or databases.

Use sequences

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

  • Explicit sequence: Use the DDL syntax to create and maintain explicit sequences. An explicit sequence can be used in an independent manner. 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 automatically maintains implicit sequences.

Supported types and features of sequences

PolarDB-X supports the following four types of sequences.

Type (abbreviation) Globally unique Consecutive Monotonically increasing Monotonically increasing within the same session Non-single point Data type Readability Unitization capability
Group sequence (GROUP) Yes No No Yes Yes All integer data types High No
Unit group sequence (GROUP) Yes No No Yes Yes All integer data types High Yes
Time-based sequence (TIME) Yes No Monotonically increasing at the macro level and non-monotonically increasing at the micro level Yes Yes BIGINT only Low No
Simple sequence (SIMPLE) Yes Yes Yes Yes No All integer data types High No

Group sequence (GROUP, the default sequence type)

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

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

  • Advantages: Group sequences generate globally unique values and can prevent SPOF risks. Group sequences deliver excellent performance.
  • Disadvantages: Group sequences may contain nonconsecutive values and may not start from the specified start value. The values in a group sequence 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 generate values that are unique among multiple instances or multiple databases. The values in a unit group sequence can be nonconsecutive or non-monotonically increasing. If only one unit is configured for a unit group sequence, the unit group sequence is equivalent to a standard 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 in a unit group sequence cannot be cyclical.

The implementation mechanism of unit group sequences is the same as that of group sequences. You can use the extension parameters to customize unit indexes and the number of units for a unit group sequence.

  • The number of units determines the sequence space that is assigned to the unit group sequence.
  • Units are identified by unit indexes. Each unit occupies a subset of the sequence space.
  • If you specify multiple unit indexes, multiple units are specified. The subsets for different units do not overlap. This means that PolarDB-X does not generate the same sequence values 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.
Note
The following versions of PolarDB-X provide unit group sequences:
  • V5.2: V5.2.7-1606682 and later. PolarDB-X V5.2.7-1606682 was released on April 27, 2018.
  • V5.3: V5.3.3-1670435 and later. PolarDB-X V5.3.3-1670435 was released on August 15, 2018.

Time-based sequence (TIME)

A time-based sequence value consists of a timestamp, a node ID, and a serial number. Time-based sequences generate values that are globally unique and auto-incremental at the macro level. When the values in 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 associated 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 generate globally unique values and deliver excellent performance.
  • Disadvantages: The values in a time-based sequence are nonconsecutive. The START WITH, INCREMENT BY, MAXVALUE, CYCLE, and NOCYCLE parameters are invalid for time-based sequences.
Note
  • If a time-based sequence is used for an auto-increment column of a table, the values of the auto-increment column must be of the BIGINT data type.
  • The following versions of PolarDB-X provide time-based sequences:
    • V5.2: V5.2.8-15432885 and later. PolarDB-X V5.2.8-15432885 was released on November 27, 2018.
    • V5.3: V5.3.6-15439241 and later. PolarDB-X V5.3.6-15439241 was released on November 29, 2018.
Simple sequence (SIMPLE)

Only simple sequences support the INCREMENT BY, MAXVALUE, CYCLE, and 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 SPOF risks, low performance, and bottlenecks. Exercise caution when you use simple sequences.

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

Scenarios

Sequences of all types can generate globally unique values for primary key columns and unique index key 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 sequence values to be auto-incremental only at the macro level in the overall trend. The sequence values do not need to be auto-incremental at the micro level. You may also not want to use the allocation mechanism of a database to generate the sequence values. In these cases, you can use time-based sequences.
  • Use only simple sequences for services that have high requirements for consecutive sequence values. Make sure that you are fully aware of the impact of the low performance of simple sequences on your business.

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 deliver low performance because the system persistently stores a value each time the value is generated.
  • A group sequence or a unit group sequence may generate values such as 200001, 200002, 200003, 200004, 100001, 100002, 100003...
Note
  • The start value of a group sequence is not necessarily the same as the value that is specified by START WITH. A group sequence always starts from a value that is greater than the specified start value. In this example, the specified start value is 100000, but the actual start value of the group sequence is 200001.
  • A group sequence generates globally unique values and may contain nonconsecutive values. For example, if a node fails or only some of the values in a sequence are used when the connection is closed, nonconsecutive values are generated. In this example, the values are nonconsecutive. The values between 200004 and 100001 are missing.
  • You must specify the same number of units and different unit indexes for unit group sequences that belong to the same sequence space. This ensures that the sequence values are unique among multiple instances or multiple databases.