Welcome to the Day 8 of the PG18 Hacktober!!
PostgreSQL 18 introduces support for UUIDv7, a major feature for modern, distributed systems. UUIDs (Universally Unique Identifiers) are increasingly used in applications that require unique identifiers across multiple systems or databases, such as database primary keys, filenames, system or machine names, and event identifiers.
Historically, UUIDs have been preferred over traditional auto-incrementing IDs in distributed environments. Auto-incrementing schemes, while simple, are often unsuitable for systems that span multiple services or geographic regions due to the complexity of maintaining global uniqueness. UUIDs solve this by allowing systems to generate unique identifiers independently, without requiring synchronization.
UUIDs have gained particular popularity as database keys, especially in systems built using microservices or distributed architectures.
Why use UUIDv7 in PostgreSQL?
While older UUID versions (1 to 5) provide uniqueness, but they fall short in areas like sortability and performance. UUIDv7 addresses these limitations by introducing time-based, monotonically increasing values, making it a better fit for modern use cases.
Key Benefits of UUIDv7:
Time-Ordered: Built-in timestamp allows UUIDv7 values to be sorted chronologically, improving query and indexing performance.
Globally Unique: Can be generated independently across systems without collisions.
Efficient for Indexing: Sequential nature reduces index fragmentation compared to UUIDv4.
Ideal for Event streams: Events can be uniquely and temporally identified, useful in logging, analytics, and audit trails.
Comparison of the UUIDv4 & UUIDv7
Compared to UUIDv4, UUIDv7 values are time-based. That means the first 48 bits encode the Unix timestamp, while the remaining bits ensure randomness and uniqueness. This structure makes UUIDv7 sortable by creation time and inserts more index-friendly data, resolving the two most critical issues of UUIDv4.
Unlike UUIDv4, which is entirely random and offers no inherent ordering, UUIDv7 provides a hybrid approach that balances uniqueness with chronological ordering. This makes UUIDv7 particularly well-suited for applications that require both scalability and efficient querying, such as event logging, message queues, and time-series data. With UUIDv7, new entries are naturally ordered by time, which improves cache locality and significantly reduces B-tree index fragmentation in PostgreSQL, a common performance bottleneck when using UUIDv4.
| Aspect | UUIDv4 | UUIDv7 |
|---|---|---|
| Uniqueness | Globally unique | Globally unique |
| Size | 128 bits | 128 bits |
| Generation method | Random-based | Time-based (Unix timestamp + random) |
| Sortability | Not sortable | Sortable by creation time |
| Index locality | Poor (random distribution) | Good (sequential insert order) |
| Performance Impact | Can cause index fragmentation | Reduced fragmentation, better write performance |
| Use case | General-purpose unique IDs | Optimized for databases and distributed systems requiring sortable keys |
Understanding UUIDv7 in PostgreSQL:
UUIDv7 improves upon previous versions by embedding the current Unix timestamp into the UUID itself, making it inherently time-sortable. This is especially useful in distributed systems where ordering and traceability of events matter. Because UUIDv7 combines time-based data with randomness, it maintains global uniqueness while enabling more efficient indexing, faster lookups, and better performance for write-heavy workloads in PostgreSQL.
Test case:
SELECT uuidv7();
uuidv7
--------------------------------------
0199c2c9-9ab8-7b82-8a06-2813a91aa466
(1 row)postgres=# SELECT uuidv7(INTERVAL '1 day');
uuidv7
--------------------------------------
0199c7a8-25a4-7e58-9d7e-04dee12c0fa8
(1 row)PostgreSQL 18 also updates existing functions like uuid_extract_version() and uuid_extract_timestamp() to support UUIDv7.
postgres=# SELECT uuid_extract_version(uuidv7());
uuid_extract_version
----------------------
7
(1 row)postgres=# SELECT uuid_extract_version('018d4e9f-6c44-7a12-8f67-b21cde34ff01'::uuid);
uuid_extract_version
----------------------
7
(1 row)
Using UUIDv7 as a Primary Key
You can directly use uuidv7() as a default primary key generator, making it easy to track the record creation time.
postgres=# CREATE TABLE office (
id uuid DEFAULT uuidv7() PRIMARY KEY,
name text,
age int
);
CREATE TABLEpostgres=# INSERT INTO office (name, age) VALUES
('akhil', 26),
('sameer', 25),
('lokesh', 26);
INSERT 0 3postgres=# SELECT uuid_extract_timestamp(id), name FROM office;
uuid_extract_timestamp | name
----------------------------+--------
2025-10-08 06:33:10.913+00 | akhil
2025-10-08 06:33:10.913+00 | sameer
2025-10-08 06:33:10.913+00 | lokesh
(3 rows)Conclusion:
The addition of UUIDv7 in PostgreSQL 18 represents a significant step forward in how modern systems can manage identifiers at scale. By combining globally unique values with a time-based, sortable structure, UUIDv7 addresses long-standing limitations of previous UUID versions, most notably the lack of index efficiency and chronological ordering.
For developers and architects building distributed, event-driven, or high-throughput applications, UUIDv7 provides a powerful, built-in solution for generating primary keys that are not unique but also meaningful in terms of creation time. This enhances traceability, improves query performance, and reduces index fragmentation, especially in large datasets.
With native support for uuidv7(), along with companion functions like uuid_extract_version() and uuid_extract_timestamp(), PostgreSQL 18 makes it easier than ever to integrate time-aware UUIDs into your schema design, making UUIDv7 a smart default for the next generation of scalable PostgreSQL applications.