PolarDB:sequential_uuid

The random UUID problem

A random UUID generator distributes values evenly across its entire range. Because each insert can land on any index page with equal probability, all index pages must stay in memory at once. For small indexes this is manageable, but as the index grows beyond the shared buffer size (or available RAM), the cache hit rate drops sharply and I/O spikes.

Sequential values — from sequences or timestamps — avoid this: new data almost always goes to the rightmost index page, which improves the cache hit rate significantly. The challenge with a naive prefix approach (prepend a sequence or timestamp, pad with random bytes to 16 bytes) is twofold:

• Reduced randomness. Prefixing with a bigint sequence cuts randomness from 16 bytes to 8 bytes, increasing collision probability and making UUIDs predictable.

• Index bloat. Ever-increasing prefixes leave gaps after old rows are deleted — a common problem in log tables.

Block-based design

To preserve randomness while keeping values sequential, sequential_uuid generates UUIDs in blocks that wrap around after a fixed number of UUIDs or a fixed time interval. Each UUID has the form:

(block ID; random data)

The block ID size is fixed at two bytes for the default 64 KB block size. The block ID increments with each new block and wraps around when the limit is reached, so indexes never grow unboundedly.

Sequence-based blocks

block ID = (nextval('s') / 256) % 65536

• Block size is measured in UUID count (default: 256 UUIDs per block).

• Wraps around after 16 M UUIDs (256 x 65,536).

Timestamp-based blocks

block ID = (timestamp / 60) % 65536

• Block size is a time interval (default: 60 seconds).

• Wraps around every 45 days.

uuid_sequence_nextval

Generates a sequential UUID using a PostgreSQL sequence to determine the block ID.

Syntax

uuid_sequence_nextval(seq regclass, block_size integer DEFAULT 65536, block_count integer DEFAULT 65536)

Example: use as a primary key default

CREATE SEQUENCE events_uuid_seq;

CREATE TABLE events (
    event_id  UUID PRIMARY KEY DEFAULT uuid_sequence_nextval('events_uuid_seq'::regclass, 256, 65536),
    event_type TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO events (event_type) VALUES ('user_login'), ('page_view'), ('checkout');

SELECT event_id, event_type FROM events;

Sample output:

uuid_sequence_nextval
--------------------------------------
 00005547-8a67-452d-bdf7-b390f1edc49b
(1 row)

The leading bytes of successive UUIDs in the same block are identical, which causes new rows to cluster on the same index page.

uuid_time_nextval

Generates a sequential UUID using the current timestamp to determine the block ID.

Syntax

uuid_time_nextval(interval_length integer DEFAULT 60, block_count integer DEFAULT 65536)

Example

SELECT uuid_time_nextval(1, 256);

Sample output:

uuid_time_nextval
--------------------------------------
 08dac705-8776-4ce3-a45c-123fd65e11e8
(1 row)