Use the sequential-uuids extension to generate sequential UUIDs on PostgreSQL - ApsaraDB RDS

Random UUIDs (such as UUID v4) scatter inserts across the entire B-tree index, causing frequent page splits and index fragmentation. The sequential-uuids extension generates UUIDs whose leading bits are derived from a sequence counter or a timestamp, concentrating new inserts near the end of the index and reducing fragmentation.

Prerequisites

Before you begin, ensure that you have:

An RDS instance running PostgreSQL 10 or later, with a minor engine version of 20220228 or later. If the instance runs PostgreSQL 17, the minor engine version must be 20241030 or later. For more information, see Update the minor engine version
A privileged account to connect to the RDS instance. Check the account type on the Accounts page in the ApsaraDB RDS console. If the account is a standard account, create a privileged account first. For more information, see Create an account

Enable or disable the extension

Run the following statement to enable the extension:

CREATE EXTENSION sequential_uuids;

Run the following statement to disable the extension:

DROP EXTENSION sequential_uuids;

Choose a function

The extension provides two functions. Choose based on how you want UUIDs to be ordered:

Function	Ordering basis	Best for
`uuid_sequence_nextval`	PostgreSQL sequence counter	Tables where insert order must follow a predictable, monotonic counter
`uuid_time_nextval`	Current timestamp	Tables where insert order roughly follows wall-clock time

Generate sequential UUIDs

Note For full parameter details and advanced usage, see Sequential UUID generators.

uuid_sequence_nextval

Generates a UUID whose leading bits are derived from a sequence value, making UUIDs sequential within each block.

Syntax:

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

Parameters:

Parameter	Type	Default	Description
`sequence`	`regclass`	—	The sequence object that provides the counter value
`block_size`	`int`	65536	Number of UUIDs per block. UUIDs within the same block share the same leading bits and are clustered together in the index. A larger value keeps inserts more clustered but reduces how often the leading bits rotate.
`block_count`	`int`	65536	Total number of blocks before the leading-bit pattern wraps around. A larger value reduces wrap-around frequency.

Example:

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

Expected output:

      uuid_sequence_nextval
--------------------------------------
 00cf26f7-ef7a-4746-8871-08b9c475713e
(1 row)

UUIDs generated in subsequent inserts share the same leading bits (00cf26f7-ef7a-4746) within the same block, keeping inserts clustered in the index.

uuid_time_nextval

Generates a UUID whose leading bits are derived from the current timestamp, making UUIDs sequential within each time interval.

Syntax:

uuid_time_nextval(interval_length int default 60, interval_count int default 65536) RETURNS uuid

Parameters:

Parameter	Type	Default	Description
`interval_length`	`int`	60	Length of each time interval in seconds. UUIDs generated within the same interval share the same leading bits and are clustered together in the index. A shorter interval produces more fine-grained ordering.
`interval_count`	`int`	65536	Total number of intervals before the leading-bit pattern wraps around. A larger value reduces wrap-around frequency.

Example:

SELECT uuid_time_nextval(1, 256);

Expected output:

        uuid_time_nextval
--------------------------------------
 179f542c-978c-454c-9dee-5f4ba75288e1
(1 row)

UUIDs generated within the same 1-second interval share the same leading bits (179f542c-978c-454c), keeping inserts clustered in the index.

What's next

To update the minor engine version of your RDS instance, see Update the minor engine version.
For full parameter details and advanced usage patterns, see Sequential UUID generators.