UUID in Databases

Why the column type matters

A UUID is 128 bits of data — 16 bytes. The canonical text form 8-4-4-4-12 is 36 characters, which is 36 bytes in UTF-8. Stored as text, every UUID in every row and every index entry that references it costs 2.25× more space than the binary equivalent. On a table with millions of rows and several indexes, that gap compounds fast.

Use the native type wherever the database offers one:

If you are stuck with VARCHAR(36) for legacy reasons, add the native column in a migration, backfill with CAST, then cut over — the index is the expensive part to rebuild, not the column add.

v4 versus v7 as a primary key

The column type affects space; the UUID version affects write performance at scale.

A v4 primary key is 122 bits of random data with no structure. Every new row targets a random position in the primary key B-tree. As the index grows past the size of available memory, each insert is likely to touch a leaf page that is not in cache, read it in, modify it, and write it back. Pages fill unevenly and split; the working set that must stay in cache to keep inserts fast is the entire index.

A v7 primary key starts with a 48-bit Unix millisecond timestamp. Consecutive inserts share a timestamp prefix, so they land at the current end of the index — the same hot leaf page, or a small number of adjacent ones. The index behaves like one keyed on an auto-incrementing integer: sequential writes, high fill factor, a predictable cache footprint.

The difference is invisible on small tables. It becomes measurable at hundreds of thousands of rows and significant at tens of millions.

SQL Server’s newsequentialid()

SQL Server’s uniqueidentifier type stores bytes in an unusual order, so even a time-ordered UUID v7 does not sort as expected there. The database provides newsequentialid() as its own sequential alternative. If you are generating UUIDs outside SQL Server and storing them, use the byte-swap transformation that maps UUID v7 into SQL Server’s sort order — or pin to newsequentialid() if generation on the server is acceptable.

Setting up UUID v7 in PostgreSQL

PostgreSQL 18 ships a native uuidv7() function. For earlier versions, use the pg_uuidv7 extension:

-- PostgreSQL 18+
CREATE TABLE events (
  id   uuid DEFAULT uuidv7() PRIMARY KEY,
  data jsonb NOT NULL
);

-- PostgreSQL 14–17 with pg_uuidv7 extension
CREATE EXTENSION IF NOT EXISTS pg_uuidv7;
CREATE TABLE events (
  id   uuid DEFAULT uuid_generate_v7() PRIMARY KEY,
  data jsonb NOT NULL
);

Because the timestamp occupies the high bits, an index on id can also serve time-range queries:

-- "most recent 100 events" — no separate created_at index needed
SELECT * FROM events ORDER BY id DESC LIMIT 100;

-- "events from the last hour" — again, the primary key index is enough
SELECT * FROM events
WHERE id > uuid_v7_to_timestamptz(now() - interval '1 hour')
ORDER BY id;

MySQL and MariaDB

MySQL has no native UUID type. Store UUIDs as BINARY(16) and use the helper functions introduced in MySQL 8.0:

CREATE TABLE orders (
  id     BINARY(16) DEFAULT (UUID_TO_BIN(UUID(), 1)) PRIMARY KEY,
  amount DECIMAL(10,2) NOT NULL
);

-- Read back as text
SELECT BIN_TO_UUID(id, 1) AS id, amount FROM orders;

The second argument 1 to UUID_TO_BIN reorders the timestamp bytes so that v1 UUIDs sort chronologically — the same idea as v7. If you generate v7 UUIDs outside MySQL and insert them as raw bytes, skip the reorder flag:

INSERT INTO orders (id, amount) VALUES (UUID_TO_BIN('018f3c4e-7a21-7b3c-9d4e-5f6a7b8c9d0e'), 49.99);

SQLite

SQLite has no native UUID type. The two practical options are BLOB (16 bytes, most efficient) or TEXT (36 characters, easier to read in tooling):

-- BLOB — most space-efficient, store raw bytes
CREATE TABLE events (
  id   BLOB NOT NULL PRIMARY KEY,
  data TEXT NOT NULL
);

-- Insert a v7 UUID as raw bytes from application code
-- (convert the hex string to bytes before inserting)

-- TEXT — easier for debugging and external tooling
CREATE TABLE events (
  id   TEXT NOT NULL PRIMARY KEY CHECK(length(id) = 36),
  data TEXT NOT NULL
);

SQLite does not enforce column types strictly — BLOB and TEXT are type affinities. If your ORM or driver converts UUID strings to bytes automatically, use BLOB. If you are inserting and reading the text form directly (common with SQLite CLI and lightweight tools), TEXT(36) is simpler and the space difference rarely matters at SQLite scale.

For time-ordered inserts with v7, SQLite’s B-tree benefits from sequential keys exactly as PostgreSQL does — no special configuration needed.

MongoDB

MongoDB stores UUIDs as Binary BSON values. The driver handles conversion automatically when you pass a UUID string or a UUID object:

import { MongoClient, UUID } from 'mongodb';

const client = new MongoClient(process.env.MONGO_URI);
const col = client.db('app').collection('events');

// Insert with a v7 UUID — pass as UUID object so MongoDB stores Binary subtype 4
import { v7 as uuidv7 } from 'uuid';

await col.insertOne({
  _id: new UUID(uuidv7()),
  name: 'page_view',
});

// Query by UUID
const doc = await col.findOne({ _id: new UUID('018f3c4e-7a21-7b3c-9d4e-5f6a7b8c9d0e') });

MongoDB’s Node.js driver (v4+) recognises UUID objects and stores them as BSON Binary subtype 4 (16 bytes), not as strings. Avoid storing UUIDs as plain strings in MongoDB — it wastes space and prevents efficient binary comparison.

If you are using v7 UUIDs as _id values, note that MongoDB’s default ObjectId is also time-ordered. The two are interchangeable for ordering purposes; v7 UUIDs are preferable when the ID must be consistent across multiple databases or services that don’t all use MongoDB.

Migrating an existing table from v4 to v7

Because both versions are 128-bit values with the same text format, the migration is shallow:

1. Switch the generator. Update the application code or the column default to produce v7.
2. Leave existing rows alone. Old v4 values stay valid and keep resolving. No backfill needed.
3. Rebuild the index after a major change. An index fragmented by years of random inserts does not repack itself. Once most inserts are sequential, REINDEX (PostgreSQL) or OPTIMIZE TABLE (MySQL) recovers the space.
4. Do not sort mixed data by primary key and expect time order. Rows written before the switch have random keys. For queries that must span the cutover, created_at or a separate timestamp column remains the reliable sort field.

Distributed systems and insert hotspots

One concern with time-ordered keys in a distributed database that range-partitions by key: every writer producing v7 UUIDs at a given moment targets the same partition. This is the “hotspot” problem — the current time range receives all writes while historical ranges sit idle.

Mitigations:

• Hash partitioning routes writes evenly regardless of key structure. It trades away the ability to do efficient range scans by primary key but avoids the hotspot.
• A shard prefix — prepend a random or application-derived byte before the UUID timestamp so different writers hash to different partitions.
• For single-primary PostgreSQL or MySQL, this is not a problem: there is one write path, and sequential inserts into a single B-tree are exactly what v7 is optimised for.