Prerequisites and usage of the sequential_uuid extension - PolarDB

The sequential_uuid extension provides two UUID generators that create sequential patterns. This helps reduce the random I/O issues caused by fully random UUIDs.

Applicability

The following versions of PolarDB for PostgreSQL are supported:

PostgreSQL 18 (minor engine version 2.0.18.1.2.0 and later)
PostgreSQL 17 (minor engine version 2.0.17.6.4.0 and later)
PostgreSQL 16 (minor engine version 2.0.16.9.9.0 and later)
PostgreSQL 15 (minor engine version 2.0.15.14.6.0 and later)
PostgreSQL 14 (minor engine version 2.0.14.5.1.0 and later)
PostgreSQL 11 (minor engine version 2.0.11.9.28.0 and later)

Note

You can view the minor engine version number in the console or by running the SHOW polardb_version; statement. If the minor engine version requirement is not met, you must upgrade the minor engine version.

Background information

Standard random UUID generators select values uniformly from a given range. This leads to poor data locality when you insert data into an index because all index leaf pages are equally likely to be hit. This can force the entire index into memory. This is not an issue for small indexes. However, the cache hit ratio drops quickly once the index size exceeds the shared buffer or RAM size.

Compared to random UUIDs, UUIDs that are based on sequences and timestamps have a sequential pattern. This pattern ensures that new data is almost always inserted at the rightmost edge of the index because new sequence values are greater than previous ones and timestamps monotonically increase. This helps improve the cache hit ratio.

Note

UUID generators with sequential patterns increase the predictability of UUIDs and the probability of cross-machine collisions.
For more information about the benefits of sequential UUIDs, see Sequential UUID Generators.

The main goal of sequential_uuid is to generate more sequential UUIDs without significantly reducing randomness. A reduction in randomness can increase the probability of collisions and the predictability of the UUIDs.

Generator design

The simplest way to make UUIDs more sequential is to use a sequential value as a prefix. For example, you can take a value from a sequence or a timestamp and append random data to form a 16 byte UUID. This method produces almost completely sequential UUIDs, but it has two problems:

Reduced randomness: If you use a sequence that generates `bigint` values, the random portion of the UUID is reduced from 16 bytes to 8 bytes. Timestamps reduce randomness in a similar way, depending on their precision. This reduction in randomness increases the probability of collisions and the predictability of the UUIDs. For example, you can determine which UUIDs were generated close together in time and even infer the specific timestamp.
Bloat: If the values increase monotonically, the index can become bloated after historical data is deleted. An index on a timestamp in a log table is a common example.

To solve these problems, the `sequential_uuid` generators are designed to loop back periodically. A loopback can occur after a set number of UUIDs are generated or after a specific time interval. In both cases, UUIDs are generated in blocks with the format (block ID; random data). The size of the block ID is fixed and depends on the number of blocks, which is a generator parameter. For example, if you use the default of 65,536 blocks, the block ID requires 2 bytes. The block ID increases with each block and eventually loops back.

The sequence-based UUID generator can use blocks that contain 256 UUIDs each. The 2 byte block ID is calculated as follows:
```
(nextval('s') / 256) % 65536
```
Note
- The generator loops back after every 16,777,216 (256 × 65,536) UUIDs are generated.
- The block size is defined by the number of UUIDs it contains.
The timestamp-based UUID generator has a default of 65,536 blocks, the same as the sequence-based generator. The block ID is calculated as follows:
```
(timestamp / 60) % 65536
```
Note
- The generator loops back approximately every 45 days.
- The block size is defined as a time interval. The default value is 60 seconds.

UUID generation functions

The `sequential_uuid` extension provides two functions that generate UUIDs with sequential patterns. One function uses a sequence, and the other uses a timestamp.

The uuid_sequence_nextval function accepts the following parameters:
- A sequence object of the `regclass` type.
- Specifies the block size as an integer. The default value is 65536.
- An integer for the number of blocks (default: 65536).
Generate a UUID with a sequential pattern using a sequence:
```
CREATE EXTENSION sequential_uuids;
CREATE SEQUENCE s;
SELECT uuid_sequence_nextval('s'::regclass, 256, 65536);
```
The result is as follows:
```
          uuid_sequence_nextval
--------------------------------------
 00005547-8a67-452d-bdf7-b390f1edc49b
(1 row)
```
The uuid_time_nextval function accepts the following parameters:
- An integer for the time interval in seconds (default: 60).
- An integer for the number of blocks (default: 65536).
Generate a UUID with a sequential pattern using a timestamp:
```
CREATE EXTENSION sequential_uuids;
SELECT uuid_time_nextval(1, 256);
```
The following result is returned:
```
          uuid_time_nextval
--------------------------------------
 08dac705-8776-4ce3-a45c-123fd65e11e8
(1 row)
```

Note

The default values for these parameters are suitable for most scenarios.