UUID Primary Keys in MySQL vs PostgreSQL: What Actually Hurts

UUID primary keys are one of those decisions teams make early, for good reasons, client-side ID generation, no coordination between services, safe public identifiers, and then pay for at scale in ways that depend heavily on the engine underneath. I have watched random UUIDs bring an InnoDB buffer pool to its knees, and I have watched the same schema migrate to PostgreSQL and hurt noticeably less, while still hurting. If you are moving from MySQL to PostgreSQL, UUID keys are one of the places where the ground genuinely shifts under you.

This article walks through the damage model in each engine, why time-ordered identifiers fix most of it, and the storage details that trip people up in both directions.

Why InnoDB suffers most: the clustered index

InnoDB tables are clustered on the primary key: the entire row lives in the PK b-tree, physically ordered by key. With an auto-increment key, every insert lands at the rightmost leaf page. The working set for inserts is a handful of hot pages, and the tree grows tidily.

A random UUIDv4 key destroys that locality. Every insert targets a uniformly random position in the tree, so under sustained load your insert working set becomes the entire clustered index. Pages split constantly and are left half full, the table's on-disk footprint grows, and the buffer pool fills with pages that each received one row before going cold. Once the index no longer fits in memory, inserts start reading pages from disk just to write one row into them. This is the classic random-PK failure mode, and it applies to the whole row, because in InnoDB the PK index is the table.

There is a multiplier: every secondary index entry carries a copy of the primary key as its row pointer. A 16-byte binary UUID, or worse, a 36-character CHAR key, inflates every secondary index on the table and every foreign key referencing it. Wide random keys make everything wider and colder.

PostgreSQL suffers differently: heap plus btree

PostgreSQL tables are heaps. Rows are placed wherever free space exists, typically appended, regardless of primary key value. A random UUID does not scatter your rows; the table itself is largely indifferent to key order. This one architectural difference is why the same UUID schema hurts less after migration.

The pain relocates to the primary key btree. Random keys mean every index insert lands on a random leaf page, so you get the same poor cache locality and page-split behavior as InnoDB, confined to the 16-bytes-per-entry index instead of the full-row clustered tree. Two PostgreSQL-specific costs deserve mention. First, full-page writes: after each checkpoint, the first modification to any page writes the whole page into the WAL, so touching thousands of random index pages between checkpoints generates dramatically more WAL than appending to a few hot rightmost pages. That inflates replication traffic and checkpoint I/O. Second, random insertion patterns interact poorly with btree page-split heuristics, leaving indexes larger and less dense than sequential insertion would.

Secondary indexes, by contrast, do not inherit the problem: PostgreSQL index entries point to heap locations, not to primary key values, so a UUID PK does not widen your other indexes the way it does in InnoDB. Foreign key columns still store the full 16 bytes, of course, and joins on uuid are marginally more expensive than on bigint, but the systemic amplification is absent. The PostgreSQL architecture overview covers the heap-versus-clustered distinction in more depth.

Storage types: native uuid vs binary gymnastics

PostgreSQL has a first-class uuid type: 16 bytes on disk, indexed and compared natively, generated in core with gen_random_uuid() since version 13, and rendered in the canonical text form automatically.

CREATE TABLE orders (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id uuid NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  total numeric(12,2) NOT NULL
);
-- 16 bytes, native comparisons, no conversion functions anywhere

MySQL has no uuid type, and the workarounds are where teams get hurt. Storing CHAR(36) costs 36-plus bytes per value, in the clustered index and again in every secondary index, and compares through a collation. The efficient pattern is BINARY(16) with the MySQL 8.0 conversion functions, and for the UUIDv1 output of MySQL's UUID() function, the swap flag that rearranges the time components so the stored bytes are roughly time-ordered:

CREATE TABLE orders (
  id BINARY(16) PRIMARY KEY,
  created_at DATETIME(6) NOT NULL,
  total DECIMAL(12,2) NOT NULL
);

INSERT INTO orders (id, created_at, total)
VALUES (UUID_TO_BIN(UUID(), 1), NOW(6), 49.90);

SELECT BIN_TO_UUID(id, 1) AS id, created_at
FROM orders
WHERE id = UUID_TO_BIN('0195f8a2-3c1e-11f0-9d6a-0242ac120002', 1);

Every query boundary now needs UUID_TO_BIN and BIN_TO_UUID with a consistent swap flag, every ORM needs a converter, and every human debugging session includes one incident of someone forgetting the flag and reading garbage. It works, and thousands of systems run it, but it is ceremony PostgreSQL simply does not require. This is consistently one of the small quality-of-life wins MySQL teams notice first after migrating.

UUIDv7 and ULID: fixing the order, keeping the properties

The core problem was never "UUIDs" but "random insert position." UUIDv7, standardized in RFC 9562, puts a 48-bit Unix millisecond timestamp in the high bits followed by random bits. New IDs sort after old ones, so inserts land on the rightmost pages of the btree in both engines, restoring the locality of auto-increment while keeping decentralized generation and global uniqueness. ULID is the same idea with a different text encoding, and it can be stored in a uuid column since it is also 128 bits.

Practical status as of mid-2026: PostgreSQL 18 ships a native uuidv7() function; on PostgreSQL 16 and 17 you generate UUIDv7 in the application, which is where client-generated IDs come from anyway, or use an extension where your platform allows it. MySQL 8.x has no native UUIDv7 either, so the application generates there too, stored as BINARY(16) with no swap flag needed, because v7 is already time-ordered. Library support is mature across mainstream languages.

Two caveats before you standardize on v7. The timestamp prefix leaks creation time to anyone who sees the ID; if IDs are public and creation time is sensitive, that is a real consideration, and the classic answer is random public IDs on the outside with ordered keys on the inside. And if you insert from many writers with skewed clocks, ordering is only approximately monotonic, fine for index locality, not a substitute for a created_at column.

The remaining alternative deserves honest mention: an internal bigint identity key with a UUID as an external, secondarily indexed identifier. It gives the smallest keys, the fastest joins, and clean public IDs, at the cost of maintaining two identifiers. For migrations, it also happens to sidestep every UUID-related index issue in this article.

Watching for the damage in production

UUID key pain is a trend, not an event. On InnoDB you would watch buffer pool hit rate and page-split counters. On PostgreSQL after migration, the equivalents are index size growth relative to row growth, WAL volume per unit of write throughput, checkpoint behavior, and insert latency drift as the PK index outgrows shared buffers. Index bloat from random insertion also degrades the cache efficiency of every query using the index, which shows up as gradually rising buffer reads in your slow query history rather than as one dramatic incident.

If you are changing key strategy during a migration, that is the cheapest moment to do it: you are rewriting every row anyway, so converting v4 keys to v7 for new rows, or introducing an internal bigint, costs a design meeting instead of an online migration.

How MonPG helps once you are on PostgreSQL

MonPG monitors PostgreSQL only, and the UUID story after migration is exactly its territory: index size and bloat trends on primary key btrees, insert-heavy query families whose latency drifts as indexes outgrow memory, WAL and checkpoint pressure, and cache hit behavior per query family from pg_stat_statements history. If you adopt UUIDv7, the before-and-after should be visible in the data, tighter index growth, calmer WAL, flatter insert latency, and if it is not, you want to know that too. The PostgreSQL index advisor view is the natural place to keep an eye on what your key choice is doing to the indexes it touches.