How to Store a UUID in PostgreSQL (the Native uuid Type)

PostgreSQL has a native uuid data type, so storing a UUID is genuinely easy: declare the column uuid and you are done. It holds the 128-bit value in 16 bytes, accepts the canonical hyphenated string on insert, and prints it back the same way on SELECT. No BINARY(16) column, no pack-and-unpack functions, no byte-order tricks. This is the single biggest difference from MySQL, which has no UUID column type and makes you store the raw bytes in BINARY(16) and convert by hand.

Short answer: use the native type, id uuid PRIMARY KEY DEFAULT gen_random_uuid(). The column is 16 bytes, the value round-trips as the standard a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 string with zero conversion, and gen_random_uuid() (built into core since PostgreSQL 13) fills it for you. Do not store UUIDs in text or varchar(36): you more than double the storage and throw away the type's validation and index efficiency. The one real decision left is the UUID version for a primary key, covered below.

Why the native uuid type, and not text?

The uuid type has shipped with PostgreSQL since 8.3, so on any version you are realistically running it is just there. Internally it stores the value as the raw 128 bits, which is 16 bytes, exactly the size of the identifier itself. On input it is forgiving: lower-case, upper-case, with or without braces, even with the hyphens in odd places all parse to the same value. On output it always normalizes to the standard lower-case hyphenated form, so what you read is canonical no matter how it went in.

The mistake I see most often in Postgres schemas is a UUID stored as varchar(36) or text. It looks reasonable, the string is 36 characters, so varchar(36) "fits". But it costs you on three fronts at once:

• Storage. A 36-character ASCII string is 36 bytes of data plus 1 byte of length header in Postgres's variable-length format, so 37 bytes per value against the native type's 16. That is more than double, and it carries straight through into every index on the column.
• Validation. A uuid column will reject not-a-uuid at insert time. A text column will happily store it, and you find out months later when something downstream tries to parse it.
• Comparison and indexing. The native type compares 16 bytes; the text version compares up to 36 characters with collation rules in play. Equality lookups and index entries are both smaller and faster on the native type.

There is no upside to the text representation. The value is already human-readable when you SELECT a uuid column, so you do not even gain readability. Use the native type. This is the same lesson as picking the right column type for an IP address in Postgres: a purpose-built type beats stuffing the value into a string every time.

Generating the value. You have three sensible options, and they all produce something the uuid column accepts directly:

• gen_random_uuid() returns a version-4 (random) UUID. It moved into core in PostgreSQL 13; before that it lived in the pgcrypto extension (so on PostgreSQL 12 or earlier you run CREATE EXTENSION pgcrypto; first, and from 13 on the pgcrypto version is just a wrapper around the core function). On PostgreSQL 13+ you need no extension at all.
• The uuid-ossp extension provides uuid_generate_v1(), uuid_generate_v4(), and the rest of the RFC versions. Enable it with CREATE EXTENSION "uuid-ossp";. Reach for it only if you specifically need a v1 or v3/v5 namespace UUID; for plain random v4 the core gen_random_uuid() is simpler.
• Your application. Generate the UUID in app code and insert the string. The uuid column takes it as-is.

Comparison table

The choices that actually matter: native type versus the text representations, and which UUID version to reach for as a primary key.

The storage gap is the easy call: the native type is less than half the size of the string forms, every time. The version question is the one worth thinking about, and it is the same trade-off random identifiers have in MySQL when a UUID is your primary key and again in MongoDB's BinData representation: the value's byte order decides whether inserts cluster nicely in the index or scatter across it.

The performance nuance: random v4 as a primary key

This bites people the same way in Postgres as it does in MySQL, just through a slightly different mechanism. Postgres does not physically cluster the table by its primary key the way InnoDB does (the heap is unordered, and the PK is enforced by a separate B-tree index). So you do not get InnoDB's clustered-index page-split problem on the table itself. But the primary-key B-tree index still suffers.

A version-4 UUID is entirely random, so every insert generates a key that lands in a random leaf page of that PK index. On a large index that means the page you need to write to is rarely the one already in cache, so you pay a random read to fetch it, dirty it, and eventually flush it. Insert-heavy workloads end up with poor cache locality, more buffer churn, and more write amplification than a sequential key would cause. Index pages also pack less densely because random inserts cause splits. None of this is fatal, plenty of systems run fine on random UUID PKs, but on a high-write table it is real and measurable.

The fix is a time-ordered UUID: version 7. A v7 UUID puts a millisecond timestamp in its high bits, so successive values sort in roughly creation order. Inserts then land at the right-hand edge of the index instead of scattering, which restores the locality you would get from a bigint sequence while keeping the global uniqueness of a UUID. PostgreSQL 18 adds a built-in uuidv7() function (alongside uuidv4() as an alias for gen_random_uuid()); before 18 you generate v7 in the application (most language UUID libraries now support it) or with a community extension, and store it in the same native uuid column. Nothing about the column changes, only how you fill it.

For a brand-new system I default to UUIDv7 for any UUID primary key. For an existing one on gen_random_uuid() that is not write-bound, leaving it as v4 is a perfectly defensible choice, do not rewrite a working schema for a problem you do not have.

Converting an existing text or varchar(36) column to uuid

If you inherited a table that stores its UUIDs as text or varchar(36), the migration is one ALTER TABLE. Postgres can cast the string form to the native type as long as every existing value actually parses as a UUID, so add a USING clause that does the cast:

ALTER TABLE orders
  ALTER COLUMN id TYPE uuid USING id::uuid;

That rewrites the column to 16-byte native storage and rebuilds any index on it in the new type. If even one row holds a non-UUID string, the cast aborts the whole statement (which is the validation you wanted all along). Find the offenders first with a regex check:

SELECT id FROM orders
WHERE id !~ '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$';

On a large, busy table the ALTER ... TYPE takes an ACCESS EXCLUSIVE lock and rewrites the whole table, so treat it like any other rewriting migration: do it in a maintenance window, or stage it through a new uuid column you backfill and swap in. The same caution applies to a column you genuinely want indexed afterward: a uuid column takes a plain B-tree index (CREATE INDEX ON orders (id)) like any other type, and the primary-key constraint already gives you one for free.

Worked schema: an orders table

Here is the whole thing end to end. An orders table whose primary key is a native uuid defaulting to a freshly generated value, so the application inserts without supplying an id.

CREATE TABLE orders (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id bigint NOT NULL,
  total_cents integer NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

-- insert without naming the id; the DEFAULT generates one
INSERT INTO orders (customer_id, total_cents) VALUES (42, 1999);

-- read the id back; it prints as the canonical hyphenated string
SELECT id, customer_id, total_cents FROM orders;
-- id                                   | customer_id | total_cents
-- a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 |          42 |        1999

-- look one up by its string id, no conversion needed
SELECT customer_id, total_cents
FROM orders
WHERE id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';

Notice what is absent: no UUID_TO_BIN(), no HEX(), no swap_flag. You compare a uuid column to a string literal and Postgres parses the literal to the type for you. To make those primary-key inserts index-friendly on PostgreSQL 18 or later, swap one word in the default:

CREATE TABLE orders (
  id          uuid PRIMARY KEY DEFAULT uuidv7(),
  customer_id bigint NOT NULL,
  total_cents integer NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

On PostgreSQL 12 or earlier, replace gen_random_uuid() with a pgcrypto install first:

CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- then DEFAULT gen_random_uuid() works as above

FAQ

See also

• Storing a UUID in MySQL: the BINARY(16) and UUID_TO_BIN() machinery Postgres saves you from.
• UUIDs in MongoDB: the BinData subtype representation and how the driver handles it.
• Storing a JSON document in Postgres: jsonb versus json, another spot where the native type beats a text column.
• Storing an array in PostgreSQL: when a real array column earns its keep over a delimited string.
• Picking a column type for an IP address in Postgres: inet over varchar, the same native-type-wins argument applied to addresses.
• Storing money in PostgreSQL: numeric versus integer cents, another schema-design call worth getting right up front.
• Hashing a password with bcrypt in Postgres: the right column shape for a fixed-length hash, next to where you keep the uuid id.
• Running PostgreSQL in Docker: a throwaway instance to test gen_random_uuid() and uuidv7() against.