UUIDv7 has become a popular choice for modern applications because it combines global uniqueness with time-based ordering. That makes it appealing for inserts, indexing, and debugging, especially when you want IDs that are friendlier to sort by time than random UUIDv4 values.
In an earlier tip, Generate UUIDv7 in YSQL, we learned how to generate UUIDv7-style values directly in YSQL. That approach worked, but there was an important performance drawback: during a multi-row INSERT, the original SQL function triggered a storage flush for each row. In the improved version shown here, the same kind of insert drops to just one storage flush request, which is a much better fit for batched writes in a distributed database.
Why this matters in a distributed database
In YugabyteDB, a write is not just a local in-memory action. It has to move through the distributed storage layer. When a query performs more storage flushes than necessary, it can reduce batching efficiency and add extra distributed overhead.
A storage flush is not inherently “bad.” Flushes are a normal part of getting writes durably processed. But when the database has an opportunity to batch multiple row writes together, that is usually preferable to flushing repeatedly row by row.
That is why this improvement matters: the goal is not to eliminate flushes entirely, but to avoid unnecessary ones.
The original problem
The earlier SQL implementation generated UUIDv7-style values correctly enough for many practical uses, but the execution profile was not ideal for batched inserts.
A simple insert of 9 rows ended up producing 9 storage flush requests. The revised version shown in this tip reduces that same pattern to a single storage flush request.
| Version | Rows Inserted | Storage Write Requests | Storage Flush Requests | Takeaway |
|---|---|---|---|---|
| Original function | 9 | 9 | 9 | Too flush-heavy for batched inserts |
| Improved function | 9 | 9 | 1 | Much better batching behavior |
The improved UUIDv7-style function
Here is the updated function:
CREATE OR REPLACE FUNCTION uuid_v7()
RETURNS uuid
LANGUAGE sql
VOLATILE
AS $$
SELECT encode(
set_bit(
set_bit(
overlay(
uuid_send(gen_random_uuid())
PLACING substring(int8send(floor(extract(epoch FROM clock_timestamp()) * 1000)::bigint) FROM 3)
FROM 1 FOR 6
),
52, 1 -- Set Version 7: Bit 52
),
53, 1 -- Set Version 7: Bit 53 (0111 = 7)
),
'hex'
)::uuid;
$$;
This version overlays the first 6 bytes of a random UUID with the current Unix timestamp in milliseconds, then sets the UUID version bits appropriately. It is much simpler than the earlier approach and, more importantly, behaves much better during multi-row inserts.
Demo setup
Let’s create a simple test table:
DROP TABLE test_uuid7;
CREATE TABLE test_uuid7 (
id uuid NOT NULL DEFAULT uuid_v7(),
payload text,
PRIMARY KEY (id HASH)
);
Now insert a few rows:
EXPLAIN (ANALYZE, DIST)
INSERT INTO test_uuid7(payload) VALUES
('Dasher'),
('Dancer'),
('Prancer'),
('Vixen'),
('Comet'),
('Cupid'),
('Donner'),
('Blitzen'),
('Rudolph');
What the plan shows
Here is the first run:
Insert on test_uuid7 (cost=0.00..0.45 rows=0 width=0) (actual time=1.919..1.920 rows=0 loops=1)
-> Values Scan on "*VALUES*" (cost=0.00..0.45 rows=9 width=48) (actual time=0.056..0.097 rows=9 loops=1)
Storage Table Write Requests: 9
Planning Time: 4.840 ms
Execution Time: 3.168 ms
Storage Read Requests: 0
Storage Read Ops: 0
Storage Rows Scanned: 0
Catalog Read Requests: 14
Catalog Read Execution Time: 7.266 ms
Catalog Read Ops: 14
Catalog Write Requests: 0
Storage Write Requests: 9
Storage Flush Requests: 1
Storage Flush Execution Time: 1.161 ms
Storage Execution Time: 8.427 ms
Peak Memory Usage: 64 kB
And the second run:
Insert on test_uuid7 (cost=0.00..0.45 rows=0 width=0) (actual time=0.305..0.306 rows=0 loops=1)
-> Values Scan on "*VALUES*" (cost=0.00..0.45 rows=9 width=48) (actual time=0.043..0.103 rows=9 loops=1)
Storage Table Write Requests: 9
Planning Time: 0.209 ms
Execution Time: 2.836 ms
Storage Read Requests: 0
Storage Read Ops: 0
Storage Rows Scanned: 0
Catalog Read Requests: 0
Catalog Read Ops: 0
Catalog Write Requests: 0
Storage Write Requests: 9
Storage Flush Requests: 1
Storage Flush Execution Time: 2.300 ms
Storage Execution Time: 2.300 ms
Peak Memory Usage: 64 kB
The key line is this one:
Storage Flush Requests: 1
🚽 What is a storage flush?
A storage flush is when accumulated write work gets pushed down to the storage layer for execution. In practice, you can think of it as the point where buffered write activity is forced out rather than staying grouped.
For batched inserts, you typically want the database to combine as much work as possible into fewer flushes. If every row forces its own flush, batching becomes much less effective.
| Metric | What it tells you |
|---|---|
Storage Write Requests | How many writes were issued to storage |
Storage Flush Requests | How often those writes were flushed out for execution |
Storage Flush Execution Time | How much time was spent doing that flush work |
Why this improvement is useful
The biggest value here is not standards purity. The biggest value is better write behavior.
The earlier version was interesting as a proof of concept, but the improved version is much more practical for a server-side DEFAULT because it behaves in a more batching-friendly way.
That makes it a better fit for:
- ● demos
- ● prototypes
- ● convenience defaults
- ● applications that want server-side UUID generation without paying an unnecessary flush penalty
Checking the embedded timestamp
Just like in the previous tip, we can decode the timestamp portion from the UUID:
SELECT
payload,
id,
to_timestamp(
(
(get_byte(uuid_send(id), 0)::bigint << 40) |
(get_byte(uuid_send(id), 1)::bigint << 32) |
(get_byte(uuid_send(id), 2)::bigint << 24) |
(get_byte(uuid_send(id), 3)::bigint << 16) |
(get_byte(uuid_send(id), 4)::bigint << 8) |
(get_byte(uuid_send(id), 5)::bigint)
) / 1000.0
) AS decoded_ts
FROM test_uuid7
ORDER BY id;
Example output:
.payload | id | decoded_ts
---------+--------------------------------------+----------------------------
Dasher | 019d3aed-2dda-75c1-8313-94ff0d7fcf01 | 2026-03-29 18:48:35.546+00
Dancer | 019d3aed-2dde-7b54-9220-294dc9262f67 | 2026-03-29 18:48:35.55+00
Prancer | 019d3aed-2ddf-77d5-8ea1-a9e4787083a3 | 2026-03-29 18:48:35.551+00
Vixen | 019d3aed-2ddf-792f-8c49-8a7a4da41638 | 2026-03-29 18:48:35.551+00
Comet | 019d3aed-2ddf-7a0a-90a0-3012c528eb96 | 2026-03-29 18:48:35.551+00
Cupid | 019d3aed-2ddf-7dc3-93e2-86319545d76e | 2026-03-29 18:48:35.551+00
Rudolph | 019d3aed-2de0-7598-bd35-4c64eed99537 | 2026-03-29 18:48:35.552+00
Blitzen | 019d3aed-2de0-7769-8cf3-2f847b191dbd | 2026-03-29 18:48:35.552+00
Donner | 019d3aed-2de0-78e9-bca2-ce3c62c869c5 | 2026-03-29 18:48:35.552+00
So even though this is still a workaround, it does preserve the helpful time-ordered structure in the leading bytes.
How this compares to PostgreSQL 18
PostgreSQL 18 introduced a native uuidv7() function.
That implementation:
- ● Uses Unix timestamp + sub-millisecond precision
- ● Includes randomness for uniqueness
- ● Preserves proper ordering semantics within the UUID spec
Our YSQL function is not identical to that behavior.
Key differences:
- ● No true sub-millisecond ordering guarantees
- ● Simpler construction (timestamp overlay + randomness)
- ● Designed as a practical workaround, not a spec-perfect implementation
So think of it this way:
- ● PostgreSQL 18
uuidv7()→ correct, native, spec-aligned - ● This function → pragmatic, efficient, good enough for many workloads
And importantly:
- 👉 This tip is about improving write behavior, not recreating PG18 exactly.
uuidv7() implementation into YugabyteDB.
This would provide a fully spec-aligned, built-in solution with improved ordering semantics.
I’ll update this post as progress is made on that effort.
When should you generate UUIDs in the app vs the database?
Now that we’ve improved the server-side function, a natural question comes up:
- Should UUIDv7 be generated in the database… or in the application?
The answer is: it depends on what you optimize for.
- ● Application-side UUIDv7
- ○ Best for high-throughput workloads
- ○ Allows better batching (UUID is already known before insert)
- ○ Uses mature libraries with proper UUIDv7 semantics
- ● Database-side (this function)
- ○ Extremely simple (
DEFAULT uuid_v7()) - ○ Great for rapid development and demos
- ○ Now much more efficient with reduced storage flushes
- ○ Extremely simple (
In general, if you’re optimizing for maximum performance and control, application-side generation is often the better choice.
If you’re optimizing for simplicity and convenience, a database-side DEFAULT can be a great fit… especially with the improved function shown in this tip.
👉 We’ll dive deeper into this tradeoff in a future tip.
Final takeaway
If you want a convenient server-side UUIDv7-style DEFAULT in YSQL, this revised function is a much better choice than the earlier version because it reduces unnecessary storage flushes and allows batched inserts to stay batched.
That is the practical win.
It is still best to describe this as a pragmatic workaround rather than a full native UUIDv7 implementation, but from a distributed-write perspective, reducing the flush behavior down to one is a meaningful step forward.
Have Fun!
