In PostgreSQL 18, how does uuidv7() differ from gen_random_uuid(), and what ordering does uuidv7() actually guarantee?
answer
- version 4 versus version 7
- 48-bit millisecond prefix
- 12 bits of sub-millisecond time
- monotonic per backend only
- uuid_extract_timestamp reads it back
basics
~20 sgen_random_uuid(), also spelled uuidv4() in 18, returns a fully random version 4 UUID. uuidv7() puts a millisecond Unix timestamp first, so values sort by creation time: strictly increasing within one session, only roughly time-ordered across sessions.
solid answer
~50 s`gen_random_uuid()` returns a **version 4** UUID: random bits apart from the version and variant fields, so no order. PostgreSQL 18 adds `uuidv4()` as an explicit alias and `uuidv7()`, which builds a **version 7** UUID from a 48-bit Unix timestamp in milliseconds, a 12-bit sub-millisecond fraction and random bits. Because the timestamp leads, new values sort after older ones, so a B-tree primary key receives inserts near its newest edge instead of scattered across every page. The ordering guarantee is narrower than people assume: within one backend each call's timestamp is pushed at least a minimal step past the previous one, so a session's values strictly increase; across sessions they are ordered only by their clock readings. `uuid_extract_timestamp()` reads the creation time back out, so a v7 key discloses when its row was made. On 17 there is no built-in v7 generator.
code
sql · 14 linesCREATE TABLE orders (
order_id uuid PRIMARY KEY DEFAULT uuidv7(),
shard_id smallint NOT NULL,
placed_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO orders (shard_id) VALUES (3) RETURNING order_id;
SELECT order_id,
uuid_extract_version(order_id) AS version,
uuid_extract_timestamp(order_id) AS embedded_time
FROM orders
ORDER BY order_id DESC
LIMIT 3;go deeper
Recall that gen_random_uuid() gives random version 4 values and PostgreSQL 18's uuidv7() gives time-ordered version 7 values, both stored in the uuid type.
Explain the v7 layout, a millisecond timestamp, a 12-bit sub-millisecond fraction and a random tail, and why ordering is guaranteed only within one backend.
Decide where v7 keys belong: database defaults on insert-heavy tables, random v4 for public tokens, and timestamptz columns for real event order across shards.
Weigh identifier policy for a multi-shard fleet: clock discipline, disclosure of creation time, upgrade timing for 17 servers, and whether ids are minted in the database or the application.
## The functions | Function | UUID version | Available | What the bits hold | |---|---|---|---| | `gen_random_uuid()` | 4 | core function; `pgcrypto`'s same-named function is now an obsolete wrapper | random bits plus version and variant fields | | `uuidv4()` | 4 | 18 | alias of `gen_random_uuid()` that names the version | | `uuidv7()` / `uuidv7(shift interval)` | 7 | 18 | 48-bit Unix milliseconds, 12-bit sub-millisecond fraction, random bits | | `uuid_extract_version(uuid)` | n/a | 17 | the version number for RFC 9562 variants, otherwise null | | `uuid_extract_timestamp(uuid)` | n/a | 17 | a `timestamptz` for version 1 or 7, otherwise null | The generators are volatile and return the `uuid` type, a 128-bit value stored in 16 bytes, not the 36-character text form. The `uuid-ossp` extension adds `uuid_generate_v1()`, `uuid_generate_v1mc()`, `uuid_generate_v3()`, `uuid_generate_v4()` and `uuid_generate_v5()`, but **no version 7**. ## How PostgreSQL 18 builds a v7 value The implementation follows RFC 9562's method of replacing the leftmost random bits with extra clock precision: 1. Read the real-time clock, at nanosecond precision where the platform offers it. 2. Put the Unix time in **milliseconds** into the first 48 bits. 3. Put a **1/4096-millisecond fraction** into the next 12 bits (`rand_a`), so values made within the same millisecond still differ in time order. 4. Fill the remaining bits from the strong random source. Step 1 carries a guard: each backend remembers the previous timestamp it used and, if the clock has not advanced by at least a minimal step, pushes the new timestamp forward by that step. That is what makes values **monotonic within a session**, even when two calls land in the same microsecond or the clock briefly steps backwards. ## What the ordering does and does not promise - **Within one session (one backend process):** each `uuidv7()` call returns a value greater than the previous call's. - **Across sessions:** values are ordered only by each backend's clock reading. Two checkout workers inserting in the same millisecond produce interleaved ids, and commit order can differ from id order because the timestamp is taken when the function runs, not at commit. - **Across servers:** each shard of the sharded checkout service generates its own v7 ids without coordination. Uniqueness comes from the random bits; order across shards is only as good as their clocks. So `ORDER BY order_id` approximates creation order; it is not an audit trail. Keep a `placed_at timestamptz` column when the order of events matters. ## The shift argument and extraction `uuidv7(shift interval)` offsets the embedded timestamp, for example `uuidv7(interval '-1 hour')` when backfilling rows that should sort near their historic time. The shifted time must stay inside the 48-bit range, from 1970 to roughly the year 10889; infinite or out-of-range shifts raise an error. `uuid_extract_timestamp()` returns the embedded time as `timestamptz`, which is useful for debugging and a reason not to expose v7 keys where creation time is sensitive. ## Choosing on PostgreSQL 18 - Use `DEFAULT uuidv7()` for a uuid primary key on an insert-heavy table where ids are minted in the database. - Keep `gen_random_uuid()` or `uuidv4()` for tokens and public identifiers that should reveal nothing. - Generating v7 in the application is equally valid; the `uuid` column stores any version, whatever produced it. Whether a uuid or a `bigint` identity is the better key at all is a schema-design trade-off answered elsewhere; this question is about what PostgreSQL's own functions do. ## Mistakes interviewers listen for - Assuming `gen_random_uuid()` changed behaviour in 18. It did not; 18 only added new functions beside it. - Storing UUIDs in a `text` or `varchar` column, which more than doubles the key size and compares values as collated strings. - Sorting by a v7 id to reconstruct the order of events across workers or shards. - Calling `uuid_extract_timestamp()` on a version 4 value and expecting a time; it returns null. ## On PostgreSQL 17 and earlier `uuidv7()` does not exist and `uuid-ossp` cannot produce a version 7 value. Teams generate v7 in the application, or with a hand-written SQL function, until they upgrade. `uuid_extract_timestamp()` already works on 17 for values generated elsewhere, and switching a column default to `uuidv7()` after the upgrade needs no change to existing rows.
- Does uuidv7() order ids by commit time across concurrent PostgreSQL sessions?No. The timestamp is taken when the function runs, not at commit, and only a single backend's calls are forced to increase. Two sessions can read the same millisecond and interleave, and a transaction that takes its id first may commit last. Treat v7 order as approximate creation order and keep a `timestamptz` column when consumers need the real order of events.
- How do you get version 7 keys on a PostgreSQL 17 server?There is no built-in generator: `uuidv7()` arrived in 18, and `uuid-ossp` offers versions 1, 3, 4 and 5 only. Generate v7 in the application and insert it into the `uuid` column, or write a SQL function that assembles one. `uuid_extract_timestamp()`, present since 17, can verify either, and changing the column default to `uuidv7()` after upgrading needs no data change.
- gen_random_uuid() returns time-ordered UUIDs in PostgreSQL 18
- uuidv7() values are strictly ordered across all sessions
- uuid-ossp's uuid_generate_v1() is the version 7 generator on PostgreSQL 17
- A v7 UUID hides when its row was created
- uuidv7() orders ids by commit time
Assumes PostgreSQL 18: uuidv7() and the uuidv4() alias are new in 18, while uuid_extract_timestamp() and uuid_extract_version() exist since 17.