How to Use UUID v7 in PostgreSQL

Creating a Table With UUID v7 Keys (PostgreSQL 18+)

CREATE TABLE orders (
  id         uuid PRIMARY KEY DEFAULT uuidv7(),
  customer   text NOT NULL,
  total      numeric(12,2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO orders (customer, total) VALUES ('Ayesha', 49.90)
RETURNING id;

New IDs start with the current Unix time in milliseconds, so they're inserted near the end of the primary key index rather than at random positions. UUID v4 vs UUID v7 explains why that helps.

UUID Functions in PostgreSQL 18

The shift Parameter

uuidv7() accepts an optional interval that shifts the embedded timestamp:

SELECT uuidv7(interval '-1 hour');

This can be useful for backfilling or testing. Be cautious with it in production: shifted IDs no longer reflect when the row was really created.

Reading the Timestamp Back

SELECT id, uuid_extract_timestamp(id) AS id_time
FROM orders
ORDER BY id DESC
LIMIT 5;

That's handy for debugging, but keep a real created_at column for business logic. The embedded value is the generator's clock reading at insert time, and IDs from application servers depend on their clocks. How to get the timestamp from a UUID explains the details.

On PostgreSQL 17 and Earlier

Earlier versions have gen_random_uuid() (built in since PostgreSQL 13) but no native v7 generator. Options:

• Generate in the application. Use .NET's Guid.CreateVersion7(), Python 3.14's uuid.uuid7(), the uuid package in JavaScript or google/uuid in Go, then insert the value. How to generate UUID v7 has examples.
• Use an extension or a SQL function that builds a v7 value from clock_timestamp() and random bytes. Several community implementations exist; review them before use.
• Upgrade when practical; native generation is simplest.

PostgreSQL's uuid type stores any version, so a column created on an older release accepts v7 values without changes.

Migrating From gen_random_uuid()

Switching the default for new rows is a one-line change:

ALTER TABLE orders ALTER COLUMN id SET DEFAULT uuidv7();

Existing v4 rows stay as they are; only new rows are time-ordered. Consider:

• Code that assumes v4. Regexes or validators that require 4 in the version position will reject v7. UUID regex patterns shows version-agnostic patterns.
• Ordering by id. After switching, ORDER BY id roughly follows creation time for new rows only. Don't rely on it for mixed old and new data.
• Public exposure. v7 IDs reveal approximate creation time. If IDs appear in public URLs and timing is sensitive, consider a separate public identifier.

Indexes and Storage

A uuid column uses 16 bytes. With v7 keys, the B-tree primary key index stays compact because inserts append. Foreign keys referencing it are also 16 bytes. If you're deciding between UUIDs and integers altogether, UUID vs auto-increment ID covers the trade-offs.

Test Data and Checks

When writing migrations or fixtures, it's useful to have v7 values to hand. The UUID v7 generator creates them in bulk in your browser, with quotes for pasting into SQL. To confirm what a stored value contains, paste it into the UUID decoder, which shows the version and the embedded timestamp.

Comparing v4 and v7 Inserts Yourself

To see the difference on your own hardware, create two similar tables, insert a large number of rows into each, and compare index size and insert time:

CREATE TABLE t_v4 (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), n int);
CREATE TABLE t_v7 (id uuid PRIMARY KEY DEFAULT uuidv7(),          n int);

INSERT INTO t_v4 (n) SELECT g FROM generate_series(1, 1000000) g;
INSERT INTO t_v7 (n) SELECT g FROM generate_series(1, 1000000) g;

SELECT pg_size_pretty(pg_relation_size('t_v4_pkey')) AS v4_index,
       pg_size_pretty(pg_relation_size('t_v7_pkey')) AS v7_index;

Results depend on hardware, configuration and workload, so measure rather than relying on someone else's numbers.

Partitioning and Time Ranges

Because v7 IDs start with a timestamp, ranges of IDs correspond roughly to time ranges. Some teams use that for archiving or partition pruning. Be careful: it only holds for IDs generated with accurate clocks and without a shift, and partitioning on a real timestamp column is usually clearer and more robust.

Summary

On PostgreSQL 18, use DEFAULT uuidv7() for time-ordered keys and uuid_extract_timestamp() to read them. On older versions, generate v7 in the application. Keep a real created_at column for business logic.