How to store UUIDs in PostgreSQL and MySQL

Why the column type matters

A UUID is a 128-bit number, which is 16 bytes. Its text form, like 01a16818-85c0-7b3e-9f2a-4c6d81e05a73, is 36 characters. Stored as text, each ID takes more than twice the space it needs.

That cost repeats. Every index on the column holds a copy, and every foreign key that points at it holds another. Text is also compared character by character, sometimes with collation rules, while a binary value is compared as plain bytes. A proper UUID type also rejects bad input, so a typo cannot slip into your data.

PostgreSQL

PostgreSQL has a native uuid type. It stores 16 bytes and always prints the standard lowercase form. It also accepts upper case, braces and missing hyphens on input.

Create the table

On PostgreSQL 18, generate a time-ordered UUID v7 as the default:

CREATE TABLE users (
  id    uuid PRIMARY KEY DEFAULT uuidv7(),
  email text NOT NULL
);

On PostgreSQL 13 to 17, gen_random_uuid() is built in, but it makes UUID v4. For v7 on those versions, generate the ID in your application and insert it.

CREATE TABLE users (
  id    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text NOT NULL
);

Useful functions

uuid_extract_version() returns the version number, and uuid_extract_timestamp() reads the creation time. Both arrived in PostgreSQL 17, which could read the time from v1 only. PostgreSQL 18 reads v7 too.

SELECT id, uuid_extract_timestamp(id) AS created
FROM users
ORDER BY id DESC
LIMIT 10;

Fix an existing text column

If your IDs are already in a text or varchar column, convert it in place:

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

This fails if any row holds an invalid UUID, which is a good way to find bad data. Columns that reference this one need the same change.

MySQL

MySQL has no UUID type. Use BINARY(16) and convert at the edges:

CREATE TABLE users (
  id    BINARY(16) NOT NULL PRIMARY KEY,
  email VARCHAR(255) NOT NULL
);

INSERT INTO users (id, email)
VALUES (UUID_TO_BIN('01a16818-85c0-7b3e-9f2a-4c6d81e05a73'), 'ada@example.com');

SELECT BIN_TO_UUID(id) AS id, email
FROM users
WHERE id = UUID_TO_BIN('01a16818-85c0-7b3e-9f2a-4c6d81e05a73');

UUID_TO_BIN() and BIN_TO_UUID() arrived in MySQL 8.0. MySQL’s own UUID() function makes version 1 UUIDs. It has no built-in v7 function, so generate v7 IDs in your application.

MariaDB is different. It has had a native UUID type since 10.7, so you can use that instead of BINARY(16).

The MySQL swap flag

UUID_TO_BIN() takes an optional second argument, the swap flag. With 1, MySQL swaps the first and third groups of the UUID before storing it.

This exists for version 1 UUIDs, the kind UUID() makes. In v1, the first group is the fastest-changing part of the time, so IDs made in a row look scattered. Swapping puts the slowest-changing part first, so the stored bytes roughly follow time order.

CREATE TABLE events (
  id BINARY(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID(), 1)) PRIMARY KEY
);
SELECT BIN_TO_UUID(id, 1) FROM events;

Do not use the swap flag with UUID v7. A v7 ID already has its time at the front. Swapping moves the version digit and some random bits to the front instead, which breaks the order:

1. 1
   A UUID v7, already in time order01a16818-85c0-7b3e-9f2a-4c6d81e05a73
2. 2
   UUID_TO_BIN(id, 1) stores this, which is no longer in time order7B3E85C001A168189F2A4C6D81E05A73
3. 3
   For v7 and v4, leave the flag offUUID_TO_BIN('01a16818-85c0-7b3e-9f2a-4c6d81e05a73')

Whatever you pick, use the same flag when reading. BIN_TO_UUID() with the wrong flag returns a different, wrong UUID without any error.

Keep primary keys fast

Both databases keep primary keys in a B-tree, a structure held in sorted order. MySQL’s InnoDB goes further: the table rows themselves are stored in primary key order, and every secondary index stores a copy of the primary key.

With random UUID v4 keys, each insert lands at a random spot in the index. The database reads and writes more pages, splits full pages more often, and needs more memory to keep the busy parts cached. With UUID v7, new keys go at the end, much like an auto-increment number.

So for a primary key, use v7. If you already use v4 and the table is small, it is fine to leave it.

Good to know