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.1 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.2 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.3 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.4 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.5 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().6 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.7 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.8 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.1 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.2 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.9 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.8 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.
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.
- 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.
“Universally unique identifier,” Wikipedia, accessed August 2026. https://en.wikipedia.org/wiki/Universally_unique_identifier
- 3.
Ben Dicken, “B-trees and database indexes,” planetscale.com, September 2024. https://planetscale.com/blog/btrees-and-database-indexes
- 4.
PostgreSQL Global Development Group, “Full page writes,” wiki.postgresql.org, accessed August 2026. https://wiki.postgresql.org/wiki/Full_page_writes
- 5.
Tomas Vondra, “On the impact of full-page writes,” enterprisedb.com, November 2016. https://www.enterprisedb.com/blog/impact-full-page-writes
- 6.
Mozilla Developer Network, “Crypto: randomUUID() method,” developer.mozilla.org, September 2024. https://developer.mozilla.org/en-US/docs/Web/API/Crypto/randomUUID
- 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.
“uuid_extract_timestamp(),” pgPedia, accessed August 2026. https://pgpedia.info/u/uuid_extract_timestamp.html
- 9.
Apache Software Foundation, “Encodings,” parquet.apache.org, accessed August 2026. https://parquet.apache.org/docs/file-format/data-pages/encodings/
