Separate three different choices
A slow query involving a UUID does not establish that the identifier is the bottleneck. Separate its representation, generation method, and index design before changing the schema.
| Choice | Example comparison | What it can affect |
|---|---|---|
| Representation | 36-character text versus 16-byte UUID | Key width and cache use |
| Generation | Random v4 versus time-oriented v7 | Insert locality |
| Index design | UUID primary key versus another primary key plus UUID index | Storage and lookup path |
A binary UUID's payload is 16 bytes; hyphenated ASCII text is 36. Database overhead and other columns determine the total saving. See the binary UUID guide for conversion examples and byte-order pitfalls.
This guide does not present measured database benchmark results. The comparisons below explain what to investigate, and the SQL gives you a starting point for collecting your own evidence.
PostgreSQL: inspect the query and indexes
PostgreSQL has a native uuid type. A UUID primary key creates a unique B-tree index; it does not turn the table into an InnoDB-style clustered primary-key structure. The PostgreSQL primary-key documentation explains the constraint and index behavior.
Random v4 keys can spread inserts across an index. Timestamp-oriented v7 keys can improve locality, but a point lookup still depends on cache state, selectivity, and the query plan. Do not infer faster reads from insertion order alone.
For a controlled experiment, use a disposable PostgreSQL 18 database. The following creates sample data and explains a lookup for an ID that exists:
CREATE TABLE uuid_perf_sample (
id uuid PRIMARY KEY DEFAULT uuidv7(),
payload text NOT NULL
);
INSERT INTO uuid_perf_sample (payload)
SELECT repeat('x', 100) FROM generate_series(1, 10000);
ANALYZE uuid_perf_sample;
EXPLAIN (ANALYZE, BUFFERS)
SELECT payload FROM uuid_perf_sample
WHERE id = (SELECT id FROM uuid_perf_sample LIMIT 1);
SELECT
pg_relation_size('uuid_perf_sample') AS table_bytes,
pg_indexes_size('uuid_perf_sample') AS index_bytes,
pg_total_relation_size('uuid_perf_sample') AS total_bytes;This is a smoke experiment, not a throughput benchmark. It includes a subquery to find a real ID; benchmark direct lookups with a prepared set of IDs when measuring latency. EXPLAIN ANALYZE executes the statement and adds instrumentation overhead. PostgreSQL's EXPLAIN guide explains the output, and its size functions distinguish table, index, and total storage.
Repeat in a separate fresh table using gen_random_uuid() to compare v4, keeping payload and row count unchanged. PostgreSQL 18 documents both generators in its UUID function reference. Test a realistic dataset as well as this small sample; 10,000 rows may fit entirely in memory and conceal the behavior you care about.
MySQL InnoDB: primary-key width has wider effects
InnoDB stores row data with the clustered index and includes primary-key columns in secondary-index records. A wider primary key can therefore enlarge several indexes. These details are documented in InnoDB clustered and secondary indexes.
Compare a binary UUID primary key with a compact sequential primary key plus a unique UUID column if your application can support either. The second design adds another index and can require an extra lookup, so it is a trade-off rather than a universal improvement.
For v7 binary storage, preserve its normal byte order. MySQL's optional UUID time-part swapping is intended for v1; the binary conversion guide explains why matching conversion conventions matter.
MongoDB: match indexes to access patterns
Do not assume every MongoDB UUID is already stored as binary; the application and driver determine the value's representation. Query using the same representation that was stored.
MongoDB normally creates a unique index on _id. A UUID in another field needs the indexes appropriate to that field's queries and uniqueness requirements. See MongoDB single-field indexes. UUID version alone does not establish which query plan will be faster.
Build a fair comparison
Record database and driver versions, hardware, cache settings, schema, secondary indexes, row counts, and the ID-generation implementation. Keep transaction size and client concurrency consistent between runs.
Measure insert throughput, median and tail lookup latency, index and table sizes, and resource use. Include joins and tenant lists if those are normal requests. Repeat runs, distinguish warm-cache from cold-cache results, and include generation cost only when both sides of the comparison include it.
Test a write-heavy workload as well as a read-heavy one. Ordered keys can concentrate concurrent insertion work, while random keys can scatter it. Actual limits depend on the engine and workload. For multiple nodes, also test the shard-routing strategy.
Make migration earn its cost
Keep an existing v4 design if it meets requirements. Before migrating, identify the query or storage constraint you expect to improve and define a measurable target. Preserve existing public IDs and relationships unless there is a separate reason to change them; a new generation default can apply to new records without rewriting every old identifier.
Use the v7 guide to understand ordering limits, and the bulk generator for small sample datasets. Generated examples are test inputs, not evidence of database performance.