Usage and Limitations of Distributed Sequence

Updated at:

In database design, generating a unique identifier for each row in a table is a common requirement, similar to AUTO_INCREMENT in MySQL. In PolarDB for PostgreSQL Distributed Edition, this functionality is implemented through sequence objects, which you can conveniently use via types such as SERIAL or BIGSERIAL. However, in a distributed environment, ensuring that IDs generated across all nodes are globally unique is a challenge. PolarDB provides different implementation mechanisms for this purpose, with behavior and limitations that vary depending on the sequence type you choose, primarily its integer size.

Differences Between Sequence Types

Before choosing a sequence type for distributed tables, first understand their core differences through the following table to make the right technical decision.

Feature

64-bit sequence (BIGSERIAL)

16-bit/32-bit sequence (SERIAL,SMALLSERIAL)

Recommended usage

Highly recommended for all distributed tables.

Not recommended for distributed tables unless you can accept the limitations.

Implementation

Distributed generation: the ID consists of node ID (16 bits) + sequence value (48 bits).

Centralized generation: ID generation requests must be routed to the primary compute node (primary CN).

ID generation

- Can be generated on any compute node (CN).

- Globally unique, but not guaranteed to be consecutive.

- Can only be generated on the primary CN.

- Consecutive when generated on the primary CN.

Core limitation

Generated ID values may have large gaps depending on the source node.

Attempting to generate an ID on any node other than the primary CN results in an error.

Note

Distributed tables include distributed tables, replicated tables, local tables added to metadata management, and all tables under a distributed schema. If you need to connect to all compute nodes and perform writes that auto-generate column values, make sure all auto-increment columns are of type bigserial before performing any distribution operation (such as create_distributed_table).

Detailed Behavior and Examples

64-bit Sequence

This is the recommended approach for generating unique IDs for distributed tables.

How It Works

To safely generate IDs on all nodes, PolarDB for PostgreSQL Distributed Edition partitions the 64-bit integer space: the high 16 bits are used to embed the current node's unique ID, and the low 48 bits are used for the incrementing sequence value on that node. This structure fundamentally guarantees that IDs generated by different nodes will never conflict.

Behavior Analysis

  • Advantage: You can perform INSERT operations on any compute node (primary CN or secondary CN), providing high availability and high throughput.

  • Note: Because the ID includes node information, inserting data from different nodes causes significant jumps in ID values, and they are not numerically consecutive.

Example

-- Create a distributed table using BIGSERIAL
CREATE TABLE student (id BIGSERIAL, name TEXT);
SELECT create_distributed_table('student', 'id');

-- (Connect to the primary CN) Insert two rows
INSERT INTO student (name) VALUES ('Alice'); -- id: 1
INSERT INTO student (name) VALUES ('Bob');   -- id: 2 (consecutive)

-- (Switch to a secondary CN) Insert another row
INSERT INTO student (name) VALUES ('Charlie'); -- id: 281474976710657 (large jump)

-- View the results
=> SELECT * FROM student ORDER BY id;
        id         |  name
-------------------+---------
                 1 | Alice
                 2 | Bob
 281474976710657   | Charlie
(3 rows)

The ID 281474976710657 looks like a huge number, but its binary representation reveals its structure: 0000000000000001 (node ID) + 000...0001 (sequence value), meaning the first ID generated on node 1.

16-bit/32-bit Sequence

Due to the limited integer space, these types cannot embed a node ID to enable distributed generation. Therefore, they use a centralized generation strategy.

How It Works

To ensure global uniqueness, all ID generation requests for 32-bit/16-bit sequences must be routed to and processed by the primary compute node (primary CN).

Behavior Analysis

  • Advantage: IDs generated on the primary CN are consecutive, consistent with the behavior of a standalone database.

  • Limitation: Performing an INSERT that triggers ID generation on any node other than the primary CN will result in the error operation is not allowed on this node.

Example

-- Create a distributed table using SERIAL
CREATE TABLE animal (id SERIAL, name TEXT);
SELECT create_distributed_table('animal', 'id');

-- (Connect to the primary CN) Insert data — succeeds
INSERT INTO animal (name) VALUES ('cat'); -- id: 1
SELECT * FROM animal;
 id | name
----+------
  1 | cat
(1 row)

-- (Switch to a secondary CN) Insert data — fails
INSERT INTO animal (name) VALUES ('dog');
ERROR:  nextval(sequence) calls in worker nodes are not supported for column defaults of type int or smallint
HINT:  If the command was issued from a worker node, try issuing it from the coordinator node instead.

The error message clearly indicates that calling nextval (i.e., generating a sequence value) for int or smallint column defaults is not supported on worker nodes (in this case, the secondary CN).

Summary and Best Practices

  1. Prefer BIGSERIAL: When designing auto-incrementing IDs for distributed tables, always prefer BIGSERIAL or a BIGINT-based IDENTITY column.

  2. Accept non-consecutive IDs: When using BIGSERIAL, business logic should not rely on strict ID consecutiveness. ID uniqueness and monotonically increasing values (within a single node) are guaranteed.

  3. Use SERIAL with caution: Only consider using SERIAL or SMALLSERIAL when your business scenario can fully tolerate all write operations going through the primary CN and you do not require write availability from secondary CNs.