PolarDB:Use and limits of distributed sequences

Choose a sequence type

How 64-bit sequences work

BIGSERIAL uses distributed ID generation — each compute node generates IDs independently without coordinating with other nodes.

The 64-bit integer space is split into two parts: the high-order 16 bits embed the unique ID of the current node, and the low-order 48 bits hold the incrementing sequence value on that node. Because each node's IDs occupy a distinct region of the integer space, IDs from different nodes never conflict.

What this means for your application:

• INSERT operations succeed on any compute node, including primary and secondary CNs, giving you high availability and high write throughput.

• IDs are not numerically consecutive across nodes. When inserts switch from one node to another, the ID values jump significantly. Design your application to treat IDs as opaque unique identifiers, not as a sequence with predictable gaps.

Example:

-- Create a distributed table that uses 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 on the same node)

-- (Switch connection 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 large, but its structure is intentional: the binary representation is a 16-bit node ID (0000000000000001) followed by a 48-bit sequence value (000...0001). This is the first ID generated on node 1.

How 16-bit/32-bit sequences work

SERIAL and SMALLSERIAL use centralized ID generation — all ID generation requests are routed to and processed by the primary CN.

Because the integer space for 16-bit and 32-bit types is too small to embed a node ID, distributed generation is not possible. Every call to nextval must go through the primary CN to guarantee global uniqueness.

What this means for your application:

• IDs are consecutive, matching the behavior of a standalone database.

• Any INSERT that triggers ID generation on a node other than the primary CN — such as a secondary CN or a data node (DN) — fails immediately. There is no automatic fallback to the primary CN. To avoid this error, route all writes to the primary CN, or switch the column type to BIGSERIAL.

Example:

-- Create a distributed table that uses 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 connection 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 confirms that nextval for int or smallint column defaults is not supported on worker nodes. To resolve this, route all writes to the primary CN, or migrate the column to BIGSERIAL.

Best practices

1. Use `BIGSERIAL` for all distributed tables. When designing auto-incrementing IDs, use BIGSERIAL or an IDENTITY column based on BIGINT. This is the only sequence type that supports writes on any compute node.

2. Treat IDs as opaque identifiers. With BIGSERIAL, ID values jump when inserts cross node boundaries. IDs are globally unique and monotonically increasing on a single node, but not numerically consecutive across nodes. Business logic that assumes consecutive IDs — such as detecting missing rows by ID gaps — will not work correctly.

3. Use `SERIAL` only with explicit write routing. Use SERIAL or SMALLSERIAL only if your application requires consecutive IDs and all write operations can be routed exclusively through the primary CN, with no writes from secondary CNs or data nodes.