UUID v7 and ULID Generator for PostgreSQL

Pick a type and a batch size, press Generate, then copy the block. Beside it the same batch sits wrapped in quotes and parentheses, the shape a multi-row VALUES list wants, with a copy button of its own.

Paste a value into Decode and it reports the version, the variant bits and the creation time where the format carries one, which the section on reading the timestamp goes through, while a v4 comes back with no time at all, because it has none to give.

Under SQL sits an INSERT built from the batch on screen, next to five table definitions covering the built-in functions and the two extensions. The decoder has no button because the reading follows what you paste, and on the Generate tab Ctrl or Cmd with Enter draws a fresh batch.

A ULID is 128 bits shown as 26 characters of Crockford base32, and the choice is between keeping the characters and keeping the bits. char(26) or text holds the string as it is, and pg_column_size on PostgreSQL 18 reports 30 bytes for either against 16 for a uuid. Storing the bits means the application decodes the base32 and hands Postgres 32 hex digits, which uuid accepts, so the ULID 01ARZ3NDEKTSV4RRFFQ69G5FAV becomes 01563e3a-b5d3-d676-4c61-efb99302bd5b; the 26-character string itself is refused with invalid input syntax for type uuid.

Both forms sort in creation order across milliseconds, because the first ten characters of a ULID and the first 48 bits of the same value are the same timestamp, and on PostgreSQL 18 three values ordered as uuid came out in the same order as the strings under both the C collation and the default one. Keep the string form in one case, since a lower-case ULID sorts after every upper-case one, and within a millisecond the order depends on the generator, as the questions below explain.

Whichever form holds it, a B-tree sees new keys arriving at its right-hand edge, which is the point of a time-ordered identifier and the reason a random v4 fills an index differently. The uuid column spends 16 bytes per entry instead of 30, so the index is smaller and more of it stays in memory, and there is no ULID generator PostgreSQL ships in core to weigh against that, where v7 has one from 18 onward.

Read the timestamp out of a UUID v7

The decoder writes the creation time both as ISO 8601 and as Unix milliseconds, which is the number to compare against a created_at column when you are checking whether a backfill kept its order, and marks the two variant bits that make the value RFC 9562. Inside the database, uuid_extract_timestamp() does the same for v1 and v7 and returns null for anything else, so on PostgreSQL 18 it read 2024-11-30 05:32:01.580 UTC out of a pasted v7 and nothing out of a v4. A ULID stored as uuid gets null as well, since its version bits are not 7, and uuid_extract_version() reports null for it too; the decoder on this page reads the ULID form directly.

UUID v7 on PostgreSQL 16 and 17

Native UUID v7 Postgres support arrived with 18, so on 16 and 17 there are three routes. Generate the value in the application, which is what this page does, or install the pg_uuidv7 extension, whose README covers PostgreSQL 13 through 18 and adds uuid_generate_v7(), or write a small SQL function that assembles the bytes yourself. From 18, the UUID v7 generator Postgres runs itself is uuidv7(), which on PostgreSQL 18.3 returned version 7 values whose uuid_extract_timestamp() matched now() to the millisecond, takes an optional interval that shifts the timestamp, and sits beside an explicit uuidv4(); uuid_extract_timestamp() and uuid_extract_version() arrived one release earlier, in 17.

gen_random_uuid() has been in core since PostgreSQL 13, whose release note records that UUID generation had until then lived only in the uuid-ossp and pgcrypto modules. That history is behind the question people keep landing on, where the extension is installed and uuid_generate_v4() still fails. CREATE EXTENSION loads into the current database only, and an unqualified call is resolved through search_path, so a function sitting in an extensions schema stays invisible until you qualify it or put that schema on the path.

select uuid_generate_v4();
-- ERROR:  function uuid_generate_v4() does not exist

select extensions.uuid_generate_v4();   -- resolves
show search_path;                       -- "$user", public

What separates v7 from v4 is the leading 48 bits, which hold a Unix millisecond count. Values made one after another therefore sort in that order, and a B-tree primary key index takes them as appends at one edge instead of as writes landing anywhere in the tree. How much that buys depends on the write volume of the table, which is why this page quotes no benchmark and the next section quotes sizes instead.

A bigint primary key costs 8 bytes and, fed by an identity column, arrives in strictly increasing order, so every new entry lands on the rightmost leaf of the B-tree and the pages before it are never touched again. A uuid costs 16 bytes, and a v4 lands anywhere, so an insert can dirty any page of the index. On PostgreSQL 18, twenty thousand rows keyed by uuidv7() produced a primary key index of 632 KB against 456 KB for an identity bigint, and a heap of 1,024 KB against 872 KB.

That gap is the price of a key the application can mint before the row exists and merge across databases without renumbering, though a v7 in a URL does tell the reader when the row was made. v7 exists to keep the cost at the width of the column, since values made one after another sort in that order and reach the tree in the same append-only pattern as the bigint, where a v4 spread its writes over every page.