In our previous tip, Generate UUIDv7 in YSQL, we showed how to generate UUIDv7 values directly in YugabyteDB YSQL and use them as a column default. UUIDv7 is attractive because it embeds a millisecond Unix timestamp while remaining safe for distributed, coordination-free ID generation.
In this follow-up tip, we’ll answer the next natural question:
- “If UUIDv7 embeds time, how should I query and order by time, and how do I make that fast at scale?”
🔍 Ordering by UUIDv7 time: what works (and what doesn’t)
UUIDv7 encodes the timestamp in the first 48 bits, so extracting and ordering by that timestamp is semantically correct and behaves the same as PostgreSQL:
ORDER BY <expression>
That means “evaluate the expression per row, then sort by the result.”
However, most YugabyteDB tables using UUIDs are defined with a HASH primary key, for example:
PRIMARY KEY (id HASH)
With a HASH primary key:
● Rows are distributed by hash, not by value
● There is no natural on-disk ordering by UUID or timestamp
As a result, both of the following require a global distributed sort:
ORDER BY id;
ORDER BY extracted_timestamp;
The timestamp extraction itself is cheap; the real cost at scale is the distributed sort and data movement.
🧪 Extracting the UUIDv7 timestamp (recap)
From the first tip, you can decode the embedded timestamp like this:
SELECT
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 decoded_ts;
This is great for:
● validation
● debugging
● small result sets
But it’s not the right approach for frequent, large-scale time-based queries.
🚀 The recommended pattern: materialize and index the timestamp
If your access patterns include:
● “give me the most recent rows”
● “scan the last N minutes / hours”
● pagination by time
… the recommended YugabyteDB pattern is to materialize the UUIDv7 timestamp into its own column and index it.
Add a generated timestamp column
ALTER TABLE test_uuid7
ADD COLUMN id_ts timestamptz
GENERATED ALWAYS AS (
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
)
) STORED;
Example:
yugabyte=# ALTER TABLE test_uuid7
yugabyte-# ADD COLUMN id_ts timestamptz
yugabyte-# GENERATED ALWAYS AS (
yugabyte(# to_timestamp(
yugabyte(# (
yugabyte(# (get_byte(uuid_send(id), 0)::bigint << 40) |
yugabyte(# (get_byte(uuid_send(id), 1)::bigint << 32) |
yugabyte(# (get_byte(uuid_send(id), 2)::bigint << 24) |
yugabyte(# (get_byte(uuid_send(id), 3)::bigint << 16) |
yugabyte(# (get_byte(uuid_send(id), 4)::bigint << 8) |
yugabyte(# (get_byte(uuid_send(id), 5)::bigint)
yugabyte(# ) / 1000.0
yugabyte(# )
yugabyte(# ) STORED;
NOTICE: table rewrite may lead to inconsistencies
DETAIL: Concurrent DMLs may not be reflected in the new table.
HINT: See https://github.com/yugabyte/yugabyte-db/issues/19860. Set 'ysql_suppress_unsafe_alter_notice' yb-tserver gflag to true to suppress this notice.
ALTER TABLE
Because this column is STORED, the timestamp is computed once at write time… not on every read.
Note the NOTICE: This operation will initiate a table rewrite. On a very large table, this can be highly undesirable. A better approach is to create a new table with the additional column and migrate the data.
📌 Use a range-ordered, covering secondary index
To make “recent rows” queries efficient, create a range-ordered secondary index on the timestamp and make it covering for common query columns:
CREATE INDEX test_uuid7_id_ts_idx
ON test_uuid7 (id_ts ASC)
INCLUDE (id, payload);
Why this matters:
●
(id_ts)creates a range-ordered index, ideal for time-based scans●
INCLUDE (id, payload)allows many queries to be satisfied directly from the index● Avoids unnecessary table lookups and global sorts
Now queries like this scale cleanly:
SELECT id_ts, id, payload
FROM test_uuid7
WHERE id_ts >= now() - interval '10 minutes'
ORDER BY id_ts DESC
LIMIT 100;
Example:
yugabyte=# EXPLAIN (ANALYZE, DIST)
yugabyte-# SELECT id_ts, id, payload
yugabyte-# FROM test_uuid7
yugabyte-# WHERE id_ts >= now() - interval '10 minutes'
yugabyte-# ORDER BY id_ts DESC
yugabyte-# LIMIT 100;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=20.03..24.33 rows=3 width=56) (actual time=1.054..1.055 rows=0 loops=1)
-> Index Only Scan Backward using test_uuid7_id_ts_idx on test_uuid7 (cost=20.03..24.33 rows=3 width=56) (actual time=1.052..1.052 rows=0 loops=1)
Index Cond: (id_ts >= (now() - '00:10:00'::interval))
Heap Fetches: 0
Storage Index Read Requests: 1
Storage Index Read Execution Time: 0.891 ms
Storage Index Read Ops: 1
Planning Time: 0.150 ms
Execution Time: 1.096 ms
Storage Read Requests: 1
Storage Read Execution Time: 0.891 ms
Storage Read Ops: 1
Storage Rows Scanned: 0
Catalog Read Requests: 0
Catalog Read Ops: 0
Catalog Write Requests: 0
Storage Write Requests: 0
Storage Flush Requests: 0
Storage Execution Time: 0.891 ms
Peak Memory Usage: 8 kB
(20 rows)
🧠 Ordering semantics and stability
UUIDv7 guarantees ordering by timestamp first, but rows generated within the same millisecond may appear in any order. For deterministic ordering (identical to PostgreSQL), add a tie-breaker:
ORDER BY id_ts, id
🏁 Conclusion
UUIDv7 gives YugabyteDB users a powerful foundation for time-aware, distributed identifiers, but efficient time-based queries still require explicit modeling. While extracting the timestamp from UUIDv7 is useful for validation and debugging, production workloads should materialize the timestamp and index it.
A range-ordered, covering secondary index on the generated timestamp column provides predictable performance, avoids global sorts, and pairs naturally with UUIDv7-based primary keys, letting you keep distributed ID generation and fast “recent data” queries.
Have Fun!
