Use the sequential_uuid extension to generate sequential UUIDs and reduce random I/O - PolarDB

The sequential_uuid extension provides two UUID generators that produce sequential patterns, reducing the random I/O overhead caused by fully random UUIDs in B-tree indexes.

Random UUID generators (such as UUID v4) distribute values uniformly across the entire UUID space. Every insert lands on a different index leaf page, forcing the entire index into memory once its size exceeds the shared buffer or RAM. Sequential UUIDs solve this by ensuring new values are almost always appended to the rightmost edge of the index, keeping the active working set small and improving the cache hit ratio.

Sequential patterns increase UUID predictability and the probability of cross-machine collisions. For details on the design trade-offs, see Sequential UUID Generators.

Prerequisites

Before you begin, make sure that your PolarDB for PostgreSQL cluster is running one of the supported minor engine versions listed below.

To check the current version, run SHOW polardb_version; or view it in the console. If the version requirement is not met, upgrade the minor engine version.

PostgreSQL version	Minimum minor engine version
PostgreSQL 18	2.0.18.1.2.0
PostgreSQL 17	2.0.17.6.4.0
PostgreSQL 16	2.0.16.9.9.0
PostgreSQL 15	2.0.15.14.6.0
PostgreSQL 14	2.0.14.5.1.0
PostgreSQL 11	2.0.11.9.28.0

How it works

The extension generates UUIDs in blocks, where each UUID has the format (block ID, random data). The block ID advances with each new block and eventually loops back to the beginning, balancing sequentiality against randomness.

A naive approach—prefixing UUIDs with a raw sequence or timestamp—creates two problems:

Reduced randomness. Using a bigint sequence as a prefix reduces the random portion from 16 bytes to 8 bytes, increasing collision probability and making it easier to infer when UUIDs were generated.
Index bloat. Monotonically increasing values cause the index to grow one-directionally; deleting historical rows leaves dead pages that are never reused.

The block-based design limits both effects. With the default of 65,536 blocks, the block ID occupies only 2 bytes, leaving the remaining 14 bytes as random data. The block ID loops back periodically, so the index is written to in a ring pattern rather than in a straight line.

Sequence-based generator — block ID formula:

(nextval('s') / 256) % 65536

With a block size of 256 UUIDs (as used in the example call), the generator wraps around every 16,777,216 UUIDs (256 x 65,536).

Timestamp-based generator — block ID formula:

(timestamp / 60) % 65536

With the default interval of 60 seconds and 65,536 blocks, the generator wraps around approximately every 45 days.

Enable the extension

CREATE EXTENSION sequential_uuids;

Generate sequential UUIDs

The extension provides two functions. The default parameter values work well for most workloads.

uuid_sequence_nextval

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

uuid_sequence_nextval(sequence regclass, block_size int DEFAULT 65536, block_count int DEFAULT 65536)

Parameter	Type	Default	Description
`sequence`	`regclass`	—	The sequence object used to track position
`block_size`	`int`	`65536`	Number of UUIDs per block; the block ID advances after this many UUIDs
`block_count`	`int`	`65536`	Total number of blocks; the generator wraps around after `block_size x block_count` UUIDs

Example — standalone call:

CREATE SEQUENCE s;
SELECT uuid_sequence_nextval('s'::regclass, 256, 65536);

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

Example — use as a column default:

CREATE SEQUENCE events_id_seq;

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

uuid_time_nextval

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

uuid_time_nextval(interval_length int DEFAULT 60, interval_count int DEFAULT 65536)

Parameter	Type	Default	Description
`interval_length`	`int`	`60`	Block duration in seconds
`interval_count`	`int`	`65536`	Total number of blocks; the generator wraps around approximately every `interval_length x interval_count` seconds (~45 days with defaults)

Example — standalone call:

SELECT uuid_time_nextval(1, 256);

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

Example — use as a column default:

CREATE TABLE logs (
    log_id UUID PRIMARY KEY DEFAULT uuid_time_nextval(),
    message TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

Choose a generator

	`uuid_sequence_nextval`	`uuid_time_nextval`
Block boundary	Every N UUIDs (configurable)	Every N seconds (configurable)
Wrap-around	After `block_size x block_count` UUIDs	After ~`interval_length x interval_count` seconds
Best for	High-throughput insert workloads	Time-correlated data; wraps on a predictable calendar schedule
Extra dependency	Requires a sequence object	No extra objects needed

Usage notes

The default parameter values (block_size = 65536, block_count = 65536, interval_length = 60) are suitable for most workloads. Adjust them only if you have specific throughput or wrap-around requirements.
Sequential UUIDs increase predictability compared to fully random UUIDs. Avoid them in contexts where UUID unpredictability is a security requirement.
Index bloat from deleted historical rows is reduced but not eliminated; periodic VACUUM or REINDEX operations are still recommended for tables with high delete rates.

What's next

Version management for PolarDB for PostgreSQL