UUID v4 or v7 as a Primary Key: What Changes in Your Index, Your Logs, and Your URLs

You pick the primary-key type in the first hour of a project, usually without much thought, and it ends up being the hardest column to change three years later. By then the table holds 40 million rows, ten foreign keys point at it, and the value has leaked into API responses, cached URLs, analytics exports, and a few third-party dashboards you do not control. The good news is that choosing deliberately takes an afternoon of understanding what actually changes. The version digit in a UUID determines three concrete things: how the index absorbs your writes, what the identifier reveals to anyone who sees it, and how that value behaves once it leaves the database and lands in logs and columnar files. Neither version wins outright, and the honest job of this post is to show you exactly what you trade.

Why the Version Digit Is the Part of the Schema You Cannot Walk Back

Every UUID looks interchangeable at a glance. Same 36 characters, same 8-4-4-4-12 shape, same column type in the schema. The single hexadecimal digit at position 15, the character immediately after the second hyphen, is the only visible difference, and it determines everything downstream. That digit sets whether the identifier is drawn from pure randomness, stamped with a Unix millisecond timestamp, or carrying a Gregorian clock counting from 1582 with a MAC address bolted on.¹ The choice propagates outward fast. Foreign keys copy it, API responses echo it, cached URLs bake it in, event payloads carry it into queues, and analytics exports store it in every row of every file. This is why the decision resists later revision in a way that adding a column never does: the value is already scattered across systems you do not own. The rest of this post frames the three consequences that matter, and it sets the honest expectation that neither v4 nor v7 wins in every situation. The version digit is not a minor formatting detail; it is a commitment to a specific set of trade-offs that ripple through every layer of the system, from the storage engine to the analytics pipeline. Note that generating one of each locally takes a second and makes the comparison concrete: the browser-based UUID generator that renders v1, v4, and v7 side by side so you can inspect the actual bytes rather than reason about them abstractly.

What Actually Happens in the Index When Keys Arrive in Random Order

A B-tree keeps entries in sorted order, so where a new key lands is decided entirely by its value, not by when it arrived. This mechanism is the foundation for everything else in this section, so it is worth stating plainly: the database does not append rows to the end of the table the way a log file grows. It inserts each new key into the position that keeps the tree balanced, and that position is a function of the key’s bytes. v4 keys are drawn from 122 bits of randomness, so consecutive inserts scatter across the whole keyspace with no locality at all.² Every new row lands somewhere different, and the tree constantly reshuffles to accommodate points that have no relationship to each other. v7 keys carry a leading 48-bit Unix millisecond timestamp in big-endian order, so consecutive inserts cluster at the right edge of the tree. That clustering produces the same insert pattern an auto-increment integer creates, which is why v7 earns its reputation as the database-friendly UUID. The difference matters more under sustained write load than in a benchmark of a thousand rows, because the cost compounds with the size of the index and the pressure on memory.

Page splits and buffer pool churn

A random insert lands in a leaf page that is almost always already full, forcing the database to split that page into two halves and push a separator key upward. Sequential inserts, by contrast, always follow the rightmost path down the tree, so leaves only get added on the right side and the same pages get revisited insert after insert.³ Under a v4 workload, the engine performs meaningfully more page splits per thousand rows, and each split writes additional pages and updates internal pointers. The fragmentation also leaves leaf pages partially filled on average, so the index occupies more disk and more memory than the same data stored in a sorted order. Scattered inserts touch far more distinct pages over a short window, which grows the working set of hot index pages until it stops fitting comfortably in the buffer pool. None of this is theoretical; it is the same class of problem that database teams investigate when they notice write throughput degrading under load, and it is one of the reasons some engineers gravitate toward CapyToolkit’s browser-based tools that process everything locally for quick experiments before committing to a schema change. A workload that runs fine on a 100,000-row table starts to stall at 10 million rows, not because the hardware changed but because the index grew past the point where the cache holds the pages the queries actually need.

Why the write-ahead log grows faster than you expect

PostgreSQL writes a full image of a page to the write-ahead log on the first modification of that page after each checkpoint, so that a torn page can be reconstructed during recovery.⁴ Subsequent modifications of the same page within the same checkpoint interval log only the row-level delta. Random inserts touch many distinct pages between checkpoints, which means many more of those first-touches happen, and each one carries a full page image rather than a small row-level record. The consequence is a WAL volume that exceeds what the row count alone would suggest, sometimes by a wide margin on write-heavy tables. In one benchmark from a PostgreSQL contributor, an hour of throttled inserts produced roughly 2GB of WAL behind a sequential integer key and more than 40GB behind a UUID key, with the vast majority of those records being full-page images.⁵ This extra volume is not a logging curiosity. It is more bytes for replicas to receive and replay, so replication lag tracks the insert pattern directly. If you run synchronous replication or maintain a hot standby for failover, the additional WAL bytes also affect network throughput and the time a replica needs to stay current. The pattern holds across engines that use page-based WAL, even if the exact threshold for a full-page image differs.

The Timestamp That Travels With Every v7 Identifier

The same property that fixes the index, a readable creation time in the leading 48 bits, is also a disclosure, and it is the tradeoff the benchmark posts consistently skip. v7 is not a drop-in privacy equivalent of v4. It moves information from your database into the identifier itself, and that identifier shows up in more places than you might expect. The embedded timestamp is harmless for an internal order key and potentially awkward for a public-facing user ID, and the distinction depends entirely on what the row represents and who can see the value. Here is where an identifier becomes visible beyond the database: URLs and path segments, API responses returned to clients, webhook payloads sent to third parties, referrer headers logged by external services, client-side error reports, support tickets and screenshots, and CSV exports handed to other teams.

What someone can infer from an identifier they were given

A v7 identifier reveals its creation time to the millisecond, which is enough to determine signup ordering, account age, and whether two records were created in the same request. Two identifiers together reveal the interval between them, and that interval is enough to estimate volume over a period without ever querying the database. You can count how many accounts were created between two customer IDs, or how many orders fell inside a specific hour, by decoding the leading bytes of any two v7 values you possess. This is often harmless and occasionally not, and the distinction depends on what the row represents rather than on the identifier format. An internal job-run identifier does not care who knows it was created at 02:14 on a Tuesday. A public user slug or a shareable document link is a different situation entirely, because the timestamp turns the identifier into a signal about activity and growth.

What the specification says about using them in a security context

RFC 9562, the UUID specification that defines versions 1 through 8 is explicit that implementations should not assume UUIDs are hard to guess and must not use them as security capabilities, meaning identifiers whose mere possession grants access. The RFC’s own guidance is that where a UUID is needed in any security operation, v4 is the version to use, because the embedded timestamp and counter in v7 constitute a small additional attack surface. The document also recommends a cryptographically secure random number generator for producing unguessable values, which is the same foundation that browsers use for crypto.randomUUID().⁶ The practical consequence is that the version question and the “is this identifier a secret” question are separate, and treating an identifier as a bearer token is the actual bug either way. OWASP frames the same point from the application side: a hard-to-guess identifier is a defence-in-depth measure, and the authorisation check still has to run on every access attempt.⁷ Where a value genuinely needs to resist guessing, its strength is worth measuring properly with an entropy analyser rather than assumed from its length. A 36-character string feels unguessable; the bits tell you whether that feeling is justified.

Reading the Creation Time Back Out of an Identifier

The embedded timestamp is not just a cost, it is a capability. It means every row carries its own creation time even when nobody remembered to add a created_at column, and that fallback has rescued more than one incident investigation. The payoff shows up during operational work: bound a query by identifier range and you have bounded it by time, without touching a secondary index or maintaining a column that someone has to populate correctly on every insert path. This is the argument for v7 that goes beyond index fragmentation, and it is the one that converts people who thought of UUIDs as opaque blobs. The timestamp is right there in the primary key, always present, always consistent with the moment the row was born, and readable without joining to another table.

Native extraction in PostgreSQL 18

PostgreSQL 18 adds a native uuidv7() function that generates time-ordered identifiers inside the database itself, and it pairs that with uuid_extract_timestamp(), which returns the embedded creation time as a timestamp with time zone, as documented in the PostgreSQL reference for uuidv7() and uuid_extract_timestamp(). That extractor arrived in PostgreSQL 17 for version 1 identifiers and gained version 7 support in 18.⁸ The combination lets you store the value once and recover the moment later, without storing a separate timestamp column. A range filter on the key column uses the primary-key index directly, which a filter on a separate timestamp column does not do for free. That index-only path is the operational advantage.

SELECT id, uuid_extract_timestamp(id) AS created_at
FROM events
WHERE uuid_extract_timestamp(id) >= '2026-08-01 00:00:00+00'
  AND uuid_extract_timestamp(id) <  '2026-08-02 00:00:00+00';

The function reads the leading 48 bits and converts them to a timestamp, so you can filter by creation window while scanning the primary-key B-tree. On a table where the key is the thing you are already joining on and filtering by, that is a genuine simplification: one column doing the work of two.

Decoding the timestamp by hand

The first 12 hexadecimal characters of a v7 identifier, read as a 48-bit big-endian integer, are Unix milliseconds since the epoch.¹ No library required, no extension to install, and the conversion works in any language that can parse hex and divide. For engines without a native extractor, the same decoding works in a local SQL session over an exported file, using the epoch-to-timestamp helpers covered in the DuckDB reference for epoch-to-timestamp conversions. Open a CSV export, substring the first 12 characters of the key column, cast the hex to an integer, divide by 1000, and you have a creation timestamp without touching the source database. The approach doubles as a sanity check while you are learning: generate a v7 in the UUID v1, v4 & v7 Generator, decode the leading segment yourself, and confirm it matches the current clock. If the number you compute is a few hundred milliseconds off from “now”, the method is working and you understand what the bytes mean.

Where v4 Is Still the Right Answer

v7 is the better default for a primary key on an internal table, and that is a narrower statement than “v7 is better.” There are still several cases where unordered randomness is exactly what you want, and reaching for v7 there buys you a disclosure you did not need. Consider these situations: identifiers that appear in public URLs where enumeration order matters, tokens and nonces that must not reveal sequence, anything where creation time is itself sensitive, records whose ordering would reveal a business metric such as signup velocity, and tests that rely on unpredictable ordering. In each of these, the timestamp is not a feature. It is a leak. Note one more time on v1: it contains a timestamp but does not sort as a string because the layout leads with time_low, the least significant 32 bits of the clock value, so the fastest-changing bytes sit at the front of the identifier.² It gives the disclosure without the index benefit. It also originally carried a MAC address in the node field, which is why modern designs avoid it entirely. Browsers expose no MAC address, so any v1 you generate in the browser fills that field with random bytes, but the strange byte ordering remains and makes v1 a poor fit for new work.

Version	Sorts as a string	Disclosed by the value itself	Index insert position	Best suited for
v1	No	Creation time plus a node identifier	Random-ish, due to byte order	Legacy systems; avoid in new designs
v4	No	Nothing beyond randomness	Uniformly random across the tree	Public-facing IDs, tokens, security operations
v7	Yes	Creation time to the millisecond	Right edge, sequential	Internal primary keys on write-heavy tables

What Key Order Does to Logs and Analytics Files

The primary key does not stay in the database. It becomes a correlation ID in structured logs and a column in every export, and its ordering properties follow it into both. This is the consequence that the benchmark posts almost never measure, and it is the one that shows up months after launch when someone is hunting a request across four services or Parquet files are larger than they ought to be. The index story and the log story are really the same story: sorted values cluster, clustered values are easier to search and cheaper to compress, and random values resist both.

Correlation IDs in structured logs

When the key is the correlation ID threaded through a request, a v7 identifier lets you sort log lines from separate services into true creation order even when the machines’ clocks disagree by a few milliseconds. That ordering is a genuine debugging advantage at 02:00, because it means a chronological ORDER BY id on merged logs reflects what actually happened rather than what the NTP-synced clocks recorded. Chasing a single request across replicas means searching a large log file for one correlation ID, and a time-ordered identifier narrows where in the file to look before the search even runs. If the identifier increases monotonically, a binary search or a simple range estimate can jump to the right megabyte of a multi-gigabyte file. One caveat worth stating plainly: the timestamp reflects when the identifier was minted, not when the log line was written, and the two diverge for queued or retried work. A message retried an hour later still carries the original identifier timestamp, so do not confuse identifier order with event order when async processing enters the picture.

Compression in columnar exports

A UUID column is high-cardinality by construction, so Parquet’s dictionary encoding typically hits its size threshold and falls back to plain encoding for that column.⁹ Sorted values still compress better than random ones, because neighbouring v7 identifiers share long leading byte runs. That sharing is a direct consequence of how columnar formats group each column’s values together on disk, which is what makes dictionary and run-length encodings effective in the first place. With v7, the first several bytes change slowly enough that run-length encoding finds repeated prefixes; with v4, every byte is effectively random and no such runs exist. The practical framing is that this is a modest file-size difference, not a headline win, and it is worth measuring on your own export in the SQL Workbench rather than trusting a general claim. Dump a few million rows to Parquet, compare the column sizes, and decide whether the savings matter at your scale.

Migrating a Table That Already Holds Millions of v4 Keys

Most readers are not choosing on a blank slate, and the honest answer is that a full key migration is rarely worth it. The cost is not the column rewrite, which is the easy part. It is every foreign key, cached URL, external system holding the old value, and analytics history keyed on the old identifier. Changing the type means regenerating every related row, invalidating every cache, and backfilling every warehouse table, and most of that work buys you a fragmentation benefit you could get more cheaply by switching the generation function for new rows and leaving the old ones in place. That incremental approach is almost always the right first move.

Living with both versions in one column

Switching generation to v7 for new rows leaves old v4 rows in place, and the column stays valid because both are UUIDs and both fit the same uuid type. What you gain immediately is that new inserts append to the right edge of the index, so the fragmentation problem stops growing even though the existing bloat remains. Over time, as old rows age out or get archived, the index naturally shifts toward a healthier insert pattern without a single migration script. What breaks if you are careless is any code that assumes every identifier has a decodable timestamp. A v4 identifier has no embedded timestamp, so calling uuid_extract_timestamp() on an old row returns NULL rather than raising an error, and a NULL that nobody handles propagates quietly through the rest of the query.⁸ Version-check before extracting: inspect the version nibble, and only decode when it is 7. One line of defensive code prevents a class of incidents that only shows up on the oldest rows in the table.

Confirming the cutover actually happened

Check the version nibble across the table to count how many rows still use the old format, and watch that count stop growing after you switch the generation function. A simple substr(id, 14, 1) grouped by version tells you the split at any moment. Watch for a forgotten code path still minting v4, because a background job or a second service is the usual culprit, and it shows up as an identifier format that keeps reappearing in application logs after you believed the switch was complete. I have seen a nightly batch job quietly re-introduce v4 keys for weeks because nobody updated the worker config, and the only signal was a version count that refused to drop to zero. Rebuilding the index after cutover reclaims the space that random inserts left behind, and it is a separate decision from the version switch. A REINDEX or pg_repack compacts the bloat, but schedule it for a quiet window because it locks or churns the table.

UUID v4 vs v7: Which One Actually Sorts

Two UUIDs generated a second apart should, in a lot of systems, come back out in that same order when you query them. Whether that happens depends entirely on which version generated them, not on anything you configure at the database layer. UUID v4 gives you no ordering guarantee at all; UUID v7 gives you one by design. If your event stream, message log, or audit trail needs to replay in the order things actually happened, this is the one property worth checking before you pick a generator.

Why v4 UUIDs sort in no meaningful order

UUID v4 dedicates 122 of its 128 bits to a cryptographically random source, leaving no structural relationship between one generated value and the next.¹ Sorting a column of v4 UUIDs alphabetically, or by byte value, produces an order that has nothing to do with when each row was created; it is effectively as random as the bits themselves, a pattern well documented in database engineering writeups on random primary keys.¹⁰

For a use case where ordering never matters, that randomness is a feature, not a limitation: it guarantees no two clients can predict or coordinate on the next identifier, and it spreads generated values evenly across the entire 128-bit space. The moment your application needs to replay events in the order they happened, though, a v4 UUID column gives you nothing to sort on.

What you would need instead to recover order from v4 alone

Recovering chronological order from a table keyed on v4 UUIDs means adding a separate timestamp column and sorting on that instead of the primary key, since the identifier itself carries no time information. That works, but it is an extra column, an extra index, and one more place a write can disagree with the value it is supposed to represent.

Retrofitting that column onto an existing table also means backfilling a creation time for every row that predates the change, since the identifier itself gives you nothing to derive one from after the fact. A new table avoids that backfill entirely by picking v7 from the start, which is one more reason the version decision is easier to make early than to correct later.

How v7’s timestamp prefix produces real chronological order

UUID v7 places a 48-bit Unix millisecond timestamp in the first six bytes of the value, followed by four version bits, then a run of random and variant bits filling the rest.¹ Because the timestamp occupies the most significant bits, comparing two v7 values as plain strings, or as raw byte arrays, produces the same order as comparing their creation times directly, the monotonically ascending, lexically sortable behavior the version was specifically designed to provide.²

Two v7 UUIDs generated within the same millisecond share an identical timestamp prefix, so their relative order within that millisecond falls back to the random bits that follow. RFC 9562 leaves that job to the implementation, which can add a sub-millisecond fraction or a monotonic counter to the rand_a field to tighten ordering further; PostgreSQL’s native uuidv7() does exactly that, combining millisecond precision with a sub-millisecond fraction before the random bits.¹¹ For a message log or event stream generating far fewer than a thousand entries per millisecond, the plain random-bit fallback almost never produces a visibly wrong order, and even when it does, the gap is a single millisecond, not the effectively random spread a v4 column would produce.

Confirming sort behavior in the UUID generator

You can see this difference directly without touching a database. Generate a batch in the UUID generator, wait a few seconds, and generate a second batch: the V7 row’s leading characters shift forward in a predictable direction each time, while the V4 row’s leading characters show no relationship at all between the two batches. That is the entire chronological-sort claim, visible in twelve characters.

When you actually need this property

An event stream that replays entries in creation order, an audit trail that a compliance review reads chronologically, or a message queue that processes entries roughly in the order they arrived, all benefit directly from a v7 identifier, because the sort order and the creation order are the same thing.

One caveat worth knowing: the timestamp comes from the generating client’s own clock, not from a central authority, so values generated on two different machines with meaningfully skewed clocks can sort slightly out of true chronological order. Standard library implementations read the system clock directly for that field, with .NET’s Guid.CreateVersion7() using UtcNow to derive the Unix epoch timestamp it embeds.¹² For a browser-based tool generating one value at a time, that skew is rarely large enough to matter, but a distributed system generating v7 values across many servers should keep those clocks synchronized if strict cross-machine ordering matters.

Verifying the sort order yourself

None of this requires trusting either version blindly. Generate a handful of each in the UUID generator, paste them into a spreadsheet, and sort the column yourself; the v7 rows will land in generation order and the v4 rows will not, which is a faster way to confirm the property than reading about it secondhand.

Repeating that check across a few separate batches, spaced a few seconds apart, makes the pattern even clearer: each new v7 batch sorts after the one before it, while a freshly generated v4 batch lands in no particular position relative to the others. That direct comparison is a more convincing demonstration than any explanation of bit layout, since it shows the property doing exactly what the specification promises rather than asking you to trust a description of it.

When to pick v7 for ordering

Reach for v7 whenever the reading side of your system, an event stream, message log, or audit trail, needs entries to come back in the order they were created without a separate timestamp column doing the sorting.

Examples

• Two identifiers generated one second apart. Before: click Regenerate All, note the V4 and V7 values, wait a second, then click Regenerate All again. After: The second V7 value’s leading 12 characters are numerically greater than the first, matching the one-second gap. The second V4 value shares no such relationship with the first; comparing the two tells you nothing about which one came later.
• An event log that needs to replay in creation order. Before: the log table’s id column is populated with UUID v4 values and a separate created_at TIMESTAMP column. After: Switching the generator to produce v7 values lets a plain ORDER BY id return the same order as ORDER BY created_at, so the timestamp column becomes redundant for read-path ordering, though many teams keep it anyway for readability.

Common questions about v4 and v7 sort order

If I already sort by a created_at column, do I still need v7 for ordering? No, not strictly. A dedicated timestamp column already gives you a sortable field regardless of which UUID version keys the row. v7 becomes useful when you want the primary key itself to double as that sortable field, which removes one column and one index from tables where storage or index count matters.

Can two v7 UUIDs generated in the same millisecond tie? Their 48-bit timestamp prefix will be identical, but the random bits following it almost always differ, so a true tie across the full 128 bits is vanishingly unlikely. What can happen is that the sub-millisecond order between the two falls to those random bits rather than to true creation order, which only matters at very high generation rates.

Does UUID v1 sort chronologically the same way v7 does? No. UUID v1 also encodes a timestamp, but in a byte order that places the least significant bits first, so v1 values do not sort chronologically as plain strings even though the time data is technically present in them. Only v7 places the timestamp where a simple string or byte comparison sorts correctly.

Will a database index behave differently if I sort a v7 column versus a v4 column? Sorting itself works the same way regardless of version, since both are still 128-bit values to a database engine. The practical difference shows up on write, not on read: v7’s ordered inserts avoid the random-position writes that fragment a v4-keyed index.

Is the timestamp in a v7 UUID accurate enough to use as an actual event time? It is accurate to the millisecond the value was generated on the client that generated it, which makes it a reasonable proxy for creation time. For anything that needs a verified, tamper-resistant timestamp, treat it as a convenience for sorting rather than an audited event-time field, and CapyToolkit doesn’t attach any server-side timestamp to what you generate in its UUID generator, since everything runs locally in your browser.

Does switching to v7 change how many bits are available for randomness? Yes, slightly. v7 spends 48 bits on the timestamp versus roughly zero structural bits in v4 beyond the version and variant markers, leaving 74 random bits in v7 against 122 in v4. Both remain far more than enough entropy to avoid collisions at any realistic generation rate.

Generating a UUID for a Database Primary Key

A UUID solves the primary-key problem that auto-increment integers create the moment you split writes across two database replicas: no coordinating sequence, no collision, no central authority deciding who owns the next value. But the version you generate is not a cosmetic choice. UUID v4’s pure randomness and UUID v7’s built-in timestamp behave completely differently once that key becomes the clustering key of a B-tree index, and picking the wrong one on a high-write table shows up later as slower inserts, not as a bug you can spot in a code review.

What a B-tree index does with a random key

A relational database’s primary key index is usually a B-tree, and a B-tree keeps its entries sorted so range scans and lookups stay fast. Every new row has to land at the position in the tree that matches its key value, not simply get appended to the end. An auto-increment integer always inserts at the rightmost edge of the tree, because every new value is larger than every existing one, and that predictable insert point is exactly why integer primary keys have historically been fast to write.

UUID v4 breaks that pattern completely. Because 122 of its 128 bits come from a cryptographically random source, a freshly generated v4 value has no relationship to the values already in the index.¹ Each insert lands at a random position somewhere inside the tree instead of at the edge, which forces the database to load, modify, and rewrite whatever page happens to hold that position, splitting an already-full page when the new row does not fit. At low write volume the cost is invisible. Under sustained high-throughput inserts, it compounds into page splits, cache misses, and index bloat that a purely sequential key never produces.¹⁰

Why write amplification gets worse as the table grows

The problem does not stay constant as a table grows. A small table’s B-tree fits mostly in memory, so a random insert rarely triggers a disk read before the write completes. Once the index outgrows available cache, though, every random insert risks a cache miss, and the database has to pull an entire page from disk just to place one new row inside it.¹³ That per-row cost accumulates fastest on the tables most likely to need it least: high-volume, write-heavy tables like orders, events, or sessions.

How UUID v7 keeps inserts at the edge of the tree

UUID v7 exists specifically to remove that penalty while keeping the decentralized generation that made UUIDs attractive as primary keys in the first place. Its leading 48 bits encode a Unix millisecond timestamp,¹ so two v7 values generated a second apart differ in their very first characters, in the same direction every time. Sorted as plain strings or compared byte by byte, v7 values come out in the order they were created, which is exactly the monotonically ascending, creation-time-ordered property a B-tree index rewards.²

That timestamp prefix means every new v7 row inserts at, or very near, the rightmost edge of the index, the same position an auto-increment integer would use. You get the decentralized generation of a UUID and the sequential insert behavior of an integer key at the same time, without a database-managed sequence coordinating who gets the next value. PostgreSQL 18 ships a native uuidv7() function that builds a version 7 value from a millisecond-precision Unix timestamp plus a sub-millisecond fraction and random bits, so a Postgres 18 table can default its key column to v7 without an extension or an application-side generator.¹¹

Generating and reading a v7 key for a new table

Generating a v7 UUID in the UUID generator shows exactly what that timestamp encoding looks like: the first 12 hexadecimal characters of the value are a direct big-endian encoding of the millisecond the ID was created. Clicking Regenerate All produces a fresh v4, v7, and v1 identifier side by side, so you can compare a random value against a time-ordered one from the same instant and see the difference in their leading characters directly, rather than taking the sorting claim on faith.

Deciding whether the switch is worth it for an existing table

Not every table needs this decision made carefully. A reference table that receives a handful of inserts a day will never notice index fragmentation regardless of which UUID version generates its keys, because the write volume never gets close to where the cost becomes visible, and benchmarking B-tree behavior for a table that inserts ten rows an hour would be effort spent solving a problem that does not actually exist in practice.

A table that logs events, sessions, or orders at meaningful volume is a different story. If that table already uses v4 and shows no symptoms, an urgent migration is not warranted; changing a primary key format on a live table with foreign key references is real, disruptive work. For a brand-new table, though, defaulting to v7 avoids ever having that conversation, since it costs nothing beyond generating the identifier the UUID generator already produces.

When migrating is worth the disruption

Weigh the migration cost against the write pattern the table actually sees, not against a general rule that every UUID column must use one version. For anything new, the calculus is simpler: generate v7 by default and keep v4 for cases where global randomness matters more than sort order, such as identifiers you deliberately do not want anyone to guess the creation time of.

A useful signal to watch for on an existing table is index bloat or insert latency that correlates with write volume rather than with table size alone, since that specific pattern points at random-key fragmentation rather than a missing index or an unrelated query plan regression. If that signal shows up on a table already keyed with v4, treat the migration as a scoped, deliberate project rather than deferring it indefinitely.

When to pick v7 for a primary key

Reach for v7 whenever you are creating a new table’s primary key column and want a UUID that also sorts in insertion order; use v4 when you specifically want an identifier with no derivable creation time.

Examples

• A new orders table needs a primary key column. Before: your schema currently uses id UUID DEFAULT gen_random_uuid(), which generates UUID v4 values with no relationship to insertion order. After: Generating a v7 identifier instead produces a value whose first 12 hex characters encode the creation timestamp, so rows inserted later always sort after rows inserted earlier, matching how the table’s own B-tree index prefers new writes to land.
• Comparing a v4 key against a v7 key generated at the same instant. Before: click Regenerate All once and note the leading characters of the V4 and V7 rows. After: The V4 value’s leading characters are unrelated to the moment it was generated, while the V7 value’s leading 12 characters are a direct encoding of that same millisecond, which is why only the V7 row keeps the same relative order if you generate a second batch a moment later.

Common questions about UUID primary keys

Does switching from v4 to v7 primary keys require a schema migration on an existing table? Yes. The identifier length and format stay the same 128-bit value, but existing rows keep whatever version they were created with; switching only changes what new inserts look like. A live migration to renumber every existing v4 row to v7 is a significant, disruptive change and rarely worth it unless the index fragmentation is measurably hurting performance today.

Is a v7 UUID less random than a v4 UUID? Only in its first 48 bits. Those bits encode a millisecond timestamp on purpose, so anyone can read the approximate creation time from a v7 value. The remaining 74 bits are still drawn from the same cryptographically random source v4 uses, so v7 is not a weaker identifier, just a partially ordered one.

Does UUID v7 leak information that v4 does not? Yes, the creation timestamp. If your primary key is ever exposed in a public URL or API response, a v7 value reveals roughly when that row was created, which a v4 value does not. For internal database keys this rarely matters; for a public-facing identifier where creation time should stay private, v4 avoids that leak.

Can I generate v4 and v7 UUIDs for the same table if I am not sure yet? You can, but mixing versions inside one column removes the sorting benefit v7 exists to provide, since a v4 value sorts randomly relative to any v7 value around it. Pick one version per table and stick with it for the life of that column.

Does CapyToolkit store or log any UUID I generate in its generator? No. Every value the UUID generator creates is generated locally with your browser’s crypto.randomUUID() and crypto.getRandomValues(), and CapyToolkit doesn’t send anything you generate to a server. You can confirm this yourself by checking the Network tab while clicking Regenerate All.

Which version does PostgreSQL’s built-in gen_random_uuid() function produce? PostgreSQL’s native gen_random_uuid() function produces UUID v4. PostgreSQL 18 adds a native uuidv7() function; on earlier versions you need an extension or an application-side generator, such as CapyToolkit’s UUID generator.

Choosing a Version for the Table You Are About to Create

Run through this sequence before you write the migration. First, does the identifier appear anywhere a stranger can see it, including URLs, API responses, and referrer headers? Second, is creation time sensitive for this specific row, or would revealing it expose a business metric? Third, does the table take sustained high-volume writes where fragmentation would compound? Fourth, is anything treating this value as a secret, a capability, or a bearer token? The default that survives most of these questions is v7 for internal primary keys on write-heavy tables, v4 for anything public-facing or security-adjacent, and never v1 in a new design. If you are still unsure after working through the sequence, generate both versions and look at them side by side; the structural difference is small enough to grasp in a minute, and that minute pays for itself the first time you avoid a migration. The reason to decide deliberately is not that one version is faster in a benchmark. It is that the version digit encodes a policy about what your identifiers disclose, and that policy is far easier to set now than to revise later when the value has already escaped into logs, exports, and other people’s dashboards. Generating all three versions locally and inspecting them takes less time than reading a benchmark, and nothing about that inspection needs to leave the machine.

Sources

1. 1.

   K. Davis, B. Peabody, and P. Leach, “Universally Unique IDentifiers (UUIDs),” RFC 9562, IETF, May 2024. https://datatracker.ietf.org/doc/html/rfc9562

2. 2.

   “Universally unique identifier,” Wikipedia, accessed August 2026. https://en.wikipedia.org/wiki/Universally_unique_identifier

3. 3.

   Ben Dicken, “B-trees and database indexes,” planetscale.com, September 2024. https://planetscale.com/blog/btrees-and-database-indexes

4. 4.

   PostgreSQL Global Development Group, “Full page writes,” wiki.postgresql.org, accessed August 2026. https://wiki.postgresql.org/wiki/Full_page_writes

5. 5.

   Tomas Vondra, “On the impact of full-page writes,” enterprisedb.com, November 2016. https://www.enterprisedb.com/blog/impact-full-page-writes

6. 6.

   Mozilla Developer Network, “Crypto: randomUUID() method,” developer.mozilla.org, September 2024. https://developer.mozilla.org/en-US/docs/Web/API/Crypto/randomUUID

7. 7.

   OWASP Foundation, “Insecure Direct Object Reference Prevention Cheat Sheet,” owasp.org, accessed August 2026. https://cheatsheetseries.owasp.org/cheatsheets/Insecure_Direct_Object_Reference_Prevention_Cheat_Sheet.html

8. 8.

   “uuid_extract_timestamp(),” pgPedia, accessed August 2026. https://pgpedia.info/u/uuid_extract_timestamp.html

9. 9.

   Apache Software Foundation, “Encodings,” parquet.apache.org, accessed August 2026. https://parquet.apache.org/docs/file-format/data-pages/encodings/

10. 10.

    Andy Atkinson, “Avoid UUID Version 4 Primary Keys (for Postgres),” andyatkinson.com, 2025. https://andyatkinson.com/avoid-uuid-version-4-primary-keys

11. 11.

    PostgreSQL Global Development Group, “9.14. UUID Functions,” PostgreSQL 18 Documentation, postgresql.org, accessed October 2026. https://www.postgresql.org/docs/18/functions-uuid.html

12. 12.

    Microsoft, “Guid.CreateVersion7 Method,” .NET API Browser, learn.microsoft.com, accessed October 2026. https://learn.microsoft.com/en-us/dotnet/api/system.guid.createversion7?view=net-10.0

13. 13.

    Brian Morrison II, “The Problem with Using a UUID Primary Key in MySQL,” planetscale.com, March 2024. https://planetscale.com/blog/the-problem-with-using-a-uuid-primary-key-in-mysql