15 ms·

PostgreSQL and UUID as Primary Key

436 points by taubek 2y ago

leighleighleigh 2y ago
I couldn't really tell you why, but I wrangle many gigabytes of UUID-keyed data for work, and adding a BRIM index yesterday sped things up by 400%. Just my two cents!
hu3 2y ago
Would have been nice to also include bigserial/bigint in the INSERT performance comparison to understand the impact of migrating those to UUIDv4 and UUIDv7.
- AdamJacobMuller 2y ago
  Also testing using postgres-native uuid generation
fabian2k 2y ago
My somewhat naive understanding was that random UUIDs were not that big of a deal in Postgres because it does not cluster by primary key. And of course a UUID (16 bytes) is larger than a serial (4 bytes) or bigserial (8 bytes) by a factor of 2-4 . This certainly might matter for an index, but on a whole table level where you have 20+ bytes overhead per row this doesn't seem that big of a deal for anything except very narrow tables with large row counts. So far my impression is that there are a whole lot of other things I need to worry about in Postgres before I spend time considering serial vs. random UUID vs. ordered UUID. Am I wrong here and this is something that really matters and you should invest more time in?
- masklinn 2y ago
  While Postgres does not cluster table, uuids do affect indexes significantly, random insertion causes increased index slack which translates to cache bloat and thus longer traversal and lower cache residency.
- oppositelock 2y ago
  Random UUID's are super useful when you have distributed creation of UUID's, because you avoid conflicts with very high probability and don't rely on your DB to generate them for you, and they also leak no information about when or where the UUID was created. Postgres is happier with sequence ID's, but keeping Postgres happy isn't the only design goal. It does well enough for all practical purposes if you need randomness.
  - Spivak 2y ago
    > Random UUID's are super useful when you have distributed creation of UUID's, because you avoid conflicts with very high probability and don't rely on your DB to generate them for you See Snowflake IDs for a scheme that gives you the benefit of random UUIDs but are strictly increasing. Which is really UUIDv7 but fits in your bigint column. No entropy required.
  - sgarland 2y ago
    > Postgres is happier with sequence ID's, but keeping Postgres happy isn't the only design goal. It literally is the one thing in the entire stack that must always be happy. Every stateful service likely depends on it. Sad DBs means higher latency for everyone, and grumpy DBREs getting paged.
    - SoftTalker 2y ago
      DBRE? I guess DBA is too old fashioned for the cool kids?
      - sgarland 2y ago
        Listen, I didn't make the title up, I just grabbed onto it from the SRE world because I love databases. There are some pragmatic differences I've found, though - generally, DBAs are less focused on things like IaC (though I know at least one who does), SLIs/SLOs, CI/CD, and the other things often associated with SRE. So DBRE is SRE + DBA, or a DB-focused SRE, if you'd rather.
    - matharmin 2y ago
- kevin_nisbet 2y ago
  > So far my impression is that there are a whole lot of other things I need to worry about in Postgres before I spend time considering serial vs. random UUID vs. ordered UUID. Am I wrong here and this is something that really matters and you should invest more time in? Like any sort of optimization I believe this will depend on your workload and what's important. For me when I switched to UUIDv7 a few months ago, it was basically no effort to switch v4 to v7 on a relatively new system. I was observing much higher batch insertion latencies than I expected, and producing inserts that touch less of the btree on an index created a very noticeable reduction in insertion latencies. But my workloads and latencies may look nothing like yours. On amazon RDS instances with EBS volumes and relatively low memory, insertion latency stood out, so using strategies that reduce the number of disk blocks that are needed has an outsized performance impact. This of course would produce different results on different hardware / system sizing.
- munk-a 2y ago
  To: > Am I wrong here and this is something that really matters and you should invest more time in? Specifically, no - you don't need to worry about it. Reconfiguring your tables to use a different style of unique identifier if your tables have a unique identifier is a bit of a pain but no more so than any other instance of renaming a column - if you want to minimize downtime you add the new column, migrate data to the column, deploy code that utilizes the new column and then finally retire the old column. Even if the previous version of the table lacked any sort of unique key it is still possible to add one after the fact (it's a bit technically harder to properly keep them in sync but it is possible to do safely). It's just a question of the cost of doing so and the benefits of it - I work in a system that exclusively uses integral keys and our data is such that we don't really suffer any downsides from that choice - if you're working in a larger system with less confidence in the security practices of other teams then avoiding sequential keys so that you have obscurity to fall back on if someone really drops the ball on real security isn't the worst idea... but I think the really compelling reason to prefer UUIDs is for the power of distributed generation... that really only applies to inherently decentralized or astoundingly large products though - and if your product eventually grows to astoundingly large you'll have plenty of time to switch first (probably the wake-up call will be closing in on running out of 4 bit serial unique keys).
  - big_whack 2y ago
    Reconfiguring tables to use a different kind of unique ID (primary key in this context) can be a much bigger pain than an ordinary column rename if it is in use by foreign key constraints.
    - snicker7 2y ago
      OR if the primary key is exported out of the DB, i.e. for constructing URLs.
- sgarland 2y ago
  You still get performance hits from Visibility Map lookups, and WAL bloat. As a DBRE, I believe it always matters, and you should invest time in it. Pragmatically, it is unlikely to have noticeable effects until your tables are at least in the 1E5 rows range, if not higher. Unfortunately, by that point, it’s likely that you’ll find other things of higher importance to deal with, so the problem will only grow.
- stouset 2y ago
  UUIDv7 are good enough that you can just avoid thinking and use it as a “default”. The worst consequences of doing so are some slightly impacted performance and leak of some timing information but these are extremely minor drawbacks. Using completely random UUIDs is IMO the worst choice. It’s fine right up until it isn’t, and then you are stuck in hell with no good way out.
  - groestl 2y ago
    > Using completely random UUIDs is IMO the worst choice Can't say that without context. I've worked in systems where even the version bits were randomized, and for good reason (i know, technically, no UUID anymore).
    - stouset 2y ago
      I’m talking about default choices. UUIDv7 has few significant downsides for most cases. UUIDv4 has serious downsides. There are obviously cases where the latter is appropriate but that will come from unique requirements.
      - groestl 2y ago
        How about secure by default?
        stouset 2y ago
        “Secure” is only meaningful against a defined threat model. Most threat models for database IDs do not require their creation timestamp to be secret. Meanwhile every use case for database IDs requires them to be looked up in an index.
        groestl 2y ago
        I've run multiple info-exfiltrating attacks based on timestamps and sequential identifiers against various actors. There you have your threat model. While I don't have any clue about your "most", I suggest you don't ignore this vector for your opsec.
- feydaykyn 2y ago
  A dev at $WORK thought it wasn't a big deal too because they "tested" on their unrestricted dev setup with 3 rows in the table. Of course production crashed when their latest sql query did a multijoin on the real table with a few millions of rows. The size of the uuid needed to join filled the available RAM, everything slowed to a crawl and the system collapsed. The uuid as primary key can be seen as a risk multiplicator : it will amplify any performance issue you may have, converting a temporary slowness into a full system stop.
- nextaccountic 2y ago
  Postgres doesn't automatically reorder rows by clustering on a primary key but you can perform clustering manually, and if the table isn't completely filled it can attempt to maintain rows in order (as long as there is room for it) https://www.postgresql.org/docs/current/sql-cluster.html https://www.postgresql.org/docs/current/sql-cluster.html
quotemstr 2y ago
> If you have an option to choose, take a look at TSID maintained by Vlad Mihalcea. TSID: > A Java library for generating Time-Sorted Unique Identifiers (TSID). Wouldn't this TSID thing be more useful if it were implemented as a set of PostgreSQL stored procedures or something than a Java library? Not everyone uses Java.
- busterarm 2y ago
  It's forked from https://github.com/f4b6a3/tsid-creator https://github.com/f4b6a3/tsid-creator. While this is also in Java, it has many reimplementations in other languages.
- avkrpatel 2y ago
  Ulid is also similar to it.
- stoperaticless 2y ago
  - Plenty of use-cases want to generate it on application side, so they can just “insert”, instead of “insert+select the id” - not everybody uses Postgre (either way, somebody will be left out)
aabhay 2y ago
I think insert performance is a bad way to evaluate performance here, no? While B-Tree performance for time sorted keys is better on insert, what about during large transactions? In SQLite, my assumption was that the consensus was towards UUID4 rather than 7 because it meant less likelihood for page cache contention during transaction locks? Would that not also roughly map onto a Postgres-flavored system? Or dues Postgres only have row-level locking?
- samokhvalov 2y ago
  there is also a problem of data locality and blocks present in caches (page cache, buffer pool) at any given time, in general -- UUIDv4 is losing to bigint and UUIDv7 in this area
  - shi 2y ago
    This is a valid point but it highly depends on the use case and larger context on whether this will be relevant or not. If you have a table where you fetch multiple rows close to each other in paginated manner this would be relevant for the performance but if you only fetch individual records by uuid, data locality wouldn’t increase the performance.
scotty79 2y ago
Isn't B-Tree with UUIDv4 keys getting more balanced than with UUIDv7? Doesn't longer insert time result in faster searches later?
- stoperaticless 2y ago
  Related pondering: - UUIDv4 causes page fragmentation (ideally, on disk pages of data would be stored in sorted fashion; HDDs still exist) - if in your app fresh records have more reads, having them close will help with read speed (being cpu cache friendly)
  - hu3 2y ago
    To contribute if I may: Page fragmentation from UUIDv4 can be desirable for SQLite because marking the same page as dirty can give SQLite little opportunity to sync it to disk. Causing contention. In this scenario your Write Ahead Log (WAL) file just grows and grows. SQLite introduced WAL2 which means switching between 2 WAL files instead of one, helping to mitigate unbounded contention. But it's recent. Most tools don't have SQLite supporting that yet.
AtlasBarfed 2y ago
UUIDs are guaranteed to be unique? They often use tricks like including the MAC address of the generator machine and other ways to increase uniqueness assurances. It was my understanding that uuids are simply very very unlikely to duplicate in situations with random generation.
- wongarsu 2y ago
  UUIDv2 uses Mac addresses, but those turned out to be mostly a bad idea. Today when people say UUID they mean UUIDv4, which is just 124 random bits (and 4 version bits). Assuming a good random number generator it's basically impossible to generate the same UUIDv4 twice by pure chance. Even if you make billions of them per second it's vanishingly unlikely to happen.
- throwawayffffas 2y ago
  They are not theoretically guaranteed they are in practice though. 2^128 and 122 are big numbers. Even if you are producing a billion per second you have a 50% chance of not getting a collision for 100 years.
  - jacobgorm 2y ago
    I've used 128 secure-random bits for ages, not caring for any of the UUID version nonsense. Per the birthday paradox, I need to have 2*64 entries in my tables to reach 50% collision probability, and it will be a while before I can afford that much storage anyhow.
    - efilife 2y ago
      And of course the sanest person gets downvoted, as always
    - jacobgorm 2y ago
      Should have been 2*64.
- munk-a 2y ago
samokhvalov 2y ago
some related stuff: - https://commitfest.postgresql.org/48/4388/ https://commitfest.postgresql.org/48/4388/ (original patch created live https://www.youtube.com/watch?v=YPq_hiOE-N8 https://www.youtube.com/watch?v=YPq_hiOE-N8) - https://postgres.fm/episodes/uuid https://postgres.fm/episodes/uuid - https://postgres.fm/episodes/partitioning-by-ulid https://postgres.fm/episodes/partitioning-by-ulid - https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/main/0065_uuid_v7_and_partitioning_timescaledb.md https://gitlab.com/postgres-ai/postgresql-consulting/postgre...
inopinatus 2y ago
The best advice I can give you is to use bigserial for B-tree friendly primary keys and consider a string-encoded UUID as one of your external record locator options. Consider other simple options like PNR-style (airline booking) locators first, especially if nontechnical users will quote them. It may even be OK if they’re reused every few years. Do not mix PK types within the schema for a service or application, especially a line-of-business application. Use UUIDv7 only as an identifier for data that is inherently timecoded, otherwise it leaks information (even if timeshifted). Do not use hashids - they have no cryptographic qualities and are less friendly to everyday humans than the integers they represent; you may as well just use the sequence ID. As for the encoding, do not use base64 or other hyphenated alphabets, nor any identifier scheme that can produce a leading ‘0’ (zero) or ‘+’ (plus) when encoded (for the day your stuff is pasted via Excel). Generally, the principles of separation of concerns and mechanical sympathy should be top of mind when designing a lasting and purposeful database schema. Finally, since folks often say “I like stripe’s typed random IDs” in these kind of threads: Stripe are lying when they say their IDs are random. They have some random parts but when analyzed in sets, a large chunk of the binary layout is clearly metadata, including embedded timestamps, shard and reference keys, and versioning, in varying combinations depending on the service. I estimate they typically have 48-64 bits of randomness. That’s still plenty for most systems; you can do the same. Personally I am very fond of base58-encoded AES-encrypted bigserial+HMAC locators with a leading type prefix and a trailing metadata digit, and you can in a pinch even do this inside the database with plv8.
- vbezhenar 2y ago
  IMO using bigserial by default is wrong. Use whatever data type is appropriate. Not every table will grow to 4 billion rows and not every table will grow to even 60k rows. ID data type leaks to every foreign key referencing given table. Many foreign key usually will be indexed, so this further degrades performance. There are multiple data types for a reason.
  - inopinatus 2y ago
    Defaulting to 64-bit integers internally is to me a matter of mechanical sympathy, it has little to do with row capacity. It’s just a word size that current CPUs and memory architectures like working with.
netcraft 2y ago
Another day, another article saying not to use UUIDs as PKs. I've maintained systems using UUIDs stored as char(36) with million record tables without issue - This is not an endorsement, just explaining that this is bikeshedding. Should you use v7 when you can? Sure. Would int/bigint be faster in your benchmarks? Sure. But the benefits totally outweigh the speed differences until you get to a very large system. But instead of worrying about this, spend your energy on a million other things first and then celebrate when UUIDs become your bottleneck.
- eerikkivistik 2y ago
  I was gonna say... When your system is large enough to run into this specific performance bottleneck, pop a bottle and celebrate, you are making enough money to solve that problem. While knowing this information is useful, most services fail in different domains and problems way before you reach that point. I'm not sure people really comprehend how hard you can hit a single machine before you need to distribute a workload.
- paulddraper 2y ago
  I haven't maintained any sizable database system without any issues, least of all performance ones. I call BS.
  - tomnipotent 2y ago
    This gave me a good chuckle, and has generally been my experience. Systems grow often in unpredictable or unintuitive ways. You can pay the cost for something upfront, and the cost of maintaining it, and in the long term paid too much for something you didn't actually need. Alternatively you can wait to pay it until you're certain you need it but the work involved has become much more significant, in which it can cost more than it would have to have built and maintained it from the beginning. Compounding the issue is the build-up-front scenario costs fade with time and you don't really think about them, but build-when-you-need-it always creates a stir even if the costs are less overall than build-up-front. Either way something will go wrong no matter how many times you predict where the cards will fall.
  - 2y ago
brigadier132 2y ago
My strategy is to use v4 Uuids for anything that is not inserted frequently and don't need to be ordered (think user ids) and v7 ids for things that are. If your dataset is small the overhead from Uuids wont matter, if your dataset is large the randomness of Uuids will save your ass when you migrate to a distributed solution.
- rareitem 2y ago
  Is there a reason to not always use v7 by default?
  - davisp 2y ago
    Information leakage since they have a timestamp component. Some people may not care, but plenty of folks do. As others have said, anything security related likely should UUIDv4. UUIDv7 is basically an engineering compromise on security (they leak a timestamp) vs performance (random reads and writes to an index aren’t as performant as localized reads and writes).
    - rareitem 2y ago
      Thanks, I get it. Do you have any example where leaking a timestamp could pose a security risk? I can't think of any.
      - lazide 2y ago
        Timing attacks?
      - mewpmewp2 2y ago
        If it is public, any cases where for an entity, when it was created could be potentially sensitive information. E.g. some sort of legal documents, product, ecommerce data that could potentially reveal data to your competitors, etc.
  - brigadier132 2y ago
    Information leakage like the other person said but also if you ever move to a distributed db v7 uuids can lead to hotspots.
rootedbox 2y ago
Just a heads up about UUID 7.. be careful when using. par the RFC If UUIDs are required for use with any security operation within an application context in any shape or form then [RFC4122] UUIDv4 SHOULD be utilized.
- spoiler 2y ago
  I was thinking of adopting UUIDv7 for some of my stuff. So, I'm curious: why is this the case? Is it because of the time component?
  - vbezhenar 2y ago
    1. UUIDv7 allows to extract timestamp 2. UUIDv7 allows to predict first half, if you know the timestamp. 3. UUIDv7 provides 62 bits of randomness compared to 122 bits for UUIDv4. Whether that's a problem for your particular use-case or not, it's up for you to decide. I don't think that UUIDv7 is "insecure", it just provides different trade-offs and in some situations it might be less secure compared to UUIDv4, but I hardly see any attack vector where you could issue 2^62 requests to brute-force the ID.
  - lulzury 2y ago
    That's what the RFC states: > Timestamps embedded in the UUID do pose a very small attack surface. The timestamp in conjunction with an embedded counter does signal the order of creation for a given UUID and its corresponding data but does not define anything about the data itself or the application as a whole. If UUIDs are required for use with any security operation within an application context in any shape or form, then UUIDv4 (Section 5.4) SHOULD be utilized. https://datatracker.ietf.org/doc/html/rfc9562#name-security-considerations https://datatracker.ietf.org/doc/html/rfc9562#name-security-...
  - deleted 2y ago
    [deleted]
- jandrewrogers 2y ago
  Unfortunately, UUIDv4 is often specifically prohibited for some sensitive applications due to many cases of broken RNGs undermining the security guarantees of UUIDv4. Deterministic generation plus encryption is a common choice in these cases.
vog 2y ago
Note that the article's link to the UUID v7 standard is meanwhile outdated. You should instead head directly to RFC 9562: https://datatracker.ietf.org/doc/html/rfc9562 https://datatracker.ietf.org/doc/html/rfc9562 (which wasn't yet finished at the time of the article)
londons_explore 2y ago
It would be nice for these comparisons to also include 'int64' so people can see how much of an overhead UUID's are compared to the traditional approach.
- tills13 2y ago
  The problem here is that auto increment ints are guessable. The size of that problem depends on your situation
  - lazide 2y ago
    Also auto increment IDs that are exposed publicly cause [https://en.m.wikipedia.org/wiki/German_tank_problem https://en.m.wikipedia.org/wiki/German_tank_problem].
  - sgarland 2y ago
    If your API is relying purely on the user ID for AuthZ, you have much bigger problems.
    - t-writescode 2y ago
      It's not just authz, it can also be a reflection of how many customers you have, and that can also be its own problem. Or, let's say you are a middleware company and you're trying to contract your services to two other companies. These companies are competitors of each other, but they're also the biggest in their space. If you have use, let's say, autoincrementing ints for orders or operations, then the companies could use the distance between order numbers as a way of calculating how much business the other business is doing. If that functionality is possible / easy, that may be a reason for those customers to stop using you, and you've now lost one or two of your major contracts. Apparently this has been brought up by others and is called the German Tank Problem.
nubinetwork 2y ago
If you're generating random UUIDs as the primary key, how do you not run into key collisions? Having to search the entire table before inserting is slow, and catching the error and trying again is also annoying.
- davisp 2y ago
  Worrying about UUID collisions is like worrying about being hit in the head by a meteor. Sure, its technically possible, but it happens so rarely that worrying about a collision as a performance concern is just a misunderstanding on how UUIDs work. And, it’s so random that if you ever do see a collision you should immediately start looking for a compromised system or bug. This is basically how GitHub discovered the OpenSSL bug that had removed too much entropy from the RNG setup.
  - kevincox 2y ago
    You don't need to worry about a collision in a UUIDv4 that you created on your server. But I have seen a surprising number of applications that took a UUID generated client side and basically upserted it. Allowing taking over resources who's ID was known via the insert API (even if the update API has proper access control).
    - groestl 2y ago
      > UUID generated client side and basically upserted it Read and take notes. This is crazy in untrusted environments.
      - kevincox 2y ago
        Generating IDs on the client can be very useful for offline-first systems. But you need to check for conflicts and permissions on the server (or be sure to keep the IDs secret which I wouldn't recommend).
        groestl 2y ago
sbuttgereit 2y ago
The article had a link to the PostgreSQL commitfest for UUIDv7 support, but as far as I can't tell it looks unlikely that it will actually be in PostgreSQL 17. The most recent action was the committer being removed from the task and I believe version 17 is already well past feature freeze.... Is my understanding correct? This is what I think is going on, but I can't find any substantiated facts too point me to a definite conclusion. I was hoping to see it in version 17, but can't get a really good read on what's going on with this feature.
- klabb3 2y ago
  What database support is needed? Assuming Postgres already has the uuid type and that you can (and often should) do the actual generation of them in application code?
  - tyre 2y ago
    Why should they be done in application code?
    - eproxus 2y ago
      There’s no need since they are random, so you might as well generate them on your application servers which are easier to scale, to offload the write database.
      - bozey07 2y ago
        IMO marking the row as generated makes its intent clearer. It also means less code - no generating UUIDs, shorter queries = more robust. For my workloads at least, I think it's worth the tradeoff of forcing Postgres to muster 15 random bytes.
        deleted 2y ago
        [deleted]
  - sbuttgereit 2y ago
declan_roberts 2y ago
Call me old fashion but I really like integer autoincrement primary keys. It's easy to understand and obviously simple to sort. Furthermore when working on large batch projects you can just store the last primary key as your high water mark and get everything greater than that. I suppose TSID works to this end, but certainly more complicated.
- tommy_axle 2y ago
  It is simpler but like everything else it depends on the application. For a private app you can generally get away with it. Something that's more public facing? The ID will most likely leak information. As systems mature and you introduce things like replication, having IDs that are more universal starts looking good. In general, starting off with a uuid like uuid v4 or nanoid is a good bet.
  - lazide 2y ago
    The info leak, specifically, is [https://en.m.wikipedia.org/wiki/German_tank_problem https://en.m.wikipedia.org/wiki/German_tank_problem].
    - sgarland 2y ago
      Oh no, someone might know the number of customers, or the rate of signups. Traditional businesses can figure this out by sitting in the parking lot. Why SaaS has decided it’s a huge problem is beyond me.
      - lazide 2y ago
        By sitting in every parking lot, yes. Which requires physical presence. And hedge funds do indeed do it against some targets specifically as a leg up, and pay quite a bit of money to do so, presumably because it is worth it to them. It certainly helped the Allies in the war, as previous intelligence had the rate of tank production much higher, and they were expending a lot of effort trying to exceed that previous false number. Information is power, etc. etc. As to if it's worth it for your SaaS to mitigate is up to you of course. I know I notice when things like Invoice #'s, my userID, customerID, etc. get shown, and it's a small number, or small delta. And that impacts my behavior. Does it also impact someone like a VC’s behavior? Or a competitor? Unknown. But Hell, a bunch of people right now are probably launching SaaS's with NO-OP auth flows for documents or images, or with trivial external SQL injection flaws, which is a way bigger problem. But it doesn't make the vulnerability/problem non-existent. Also feel free to use 16 bit auto incrementing primary keys for all your core tables if you want. But if we're in an engineering discussion on the topic, knowledgeable folks will bring up the issues with it, because that is what they do. Most would feel it is their duty to do it, even.
rizky05 2y ago
I've been using gen_random_uuid() (postgres's function) as default value in primary key. is it any downside ?
tantalor 2y ago
> inserting UUID v7 is ~2x faster and inserting regular UUID v4 Okay but why
- mythrwy 2y ago
  It tells you in the previous section: "UUID - even though always looks similar - comes in multiple variants. Java's UUID.randomUUID() - returns UUID v4 - which is a pseudo-random value. For us the more interesting one is UUID v7 - which produces time-sorted values. It means that each time new UUID v7 is generated, a greater value it has. And that makes it a good fit for B-Tree index."
  - necovek 2y ago
    The benchmark seems to include generation in the total time. Not sure that's a useful comparison of how b-tree index behaves in each case (as UUIDv7 has fewer bits of randomness, it's also cheaper to generate).
    - mythrwy 2y ago
      Key generation time (for either type of UUID) is negligible compared to insert times. On the order of fractions of a microsecond for key generation as opposed to several milliseconds for inserts.
      - necovek 2y ago
        Thanks: I guess we've moved away from true sources of entropy for RNG and it's mostly pseudo-RNGs seeded by truly random numbers if we can achieve such performance today. I've done a quick benchmark of /dev/urandom locally, and I get ~450 MB/s out, which is roughly 28M worth of UUIDs per second — on my Ryzen 6850U laptop. Even that amounts to sub-microsecond times. I distinctly remember a time where you could only get a few MB/s, and external sources of true entropy were outright expensive for anything more than a few KB/s.
osigurdson 2y ago
I'm surprised there is no mention of hash based indexes (i.e. CREATE INDEX ... USING hash...) since lookups would conceivably always use equality.
- openthc 2y ago
  With UUIDv7/v8 (and ULID) there are some timestamps in the front half. I've seen spots where the query was in the style of `uuid_col >= 'SOME_UUID_0000' AND ulid_col <= 'SOME_UUID_FFFF'` When one is using them ast the timing for record create/insert these things happen.
sarreph 2y ago
We chose ULID for our Postgres PK recently, and this article helped a lot in making that decision: https://brandur.org/nanoglyphs/026-ids https://brandur.org/nanoglyphs/026-ids I personally prefer ULID since it is compat with a UUID type and you also get a timestamp lexicographically built into the ID so that sorting by ID also means sorting by timestamp. There are multiple PG extensions to make it easy to drop in and use.
- saaspirant 2y ago
  How do you deal with ulid exposing the timestamp (since is lexicographically sortable) ? Maybe your ULID is not public facing? Or this is not an issue for your application? I want to use something url friendly too since uuid sucks..
  - presentation 2y ago
    You could probably just use random uuids and then encode/decode them to ULID whenever you read/write them.
  - sarreph 2y ago
    I don’t have a problem with people knowing the timestamp - it’s only precise down to the second.
rjurney 2y ago
I've started using python-ulid as ULIDs seem superior to UUIDs. https://pypi.org/project/python-ulid/ https://pypi.org/project/python-ulid/
- openthc 2y ago
  I love ULID too; but it's really just UUIDv7 (or v8) with a mustache (it's all just 128bit IDs). And in the PG world, where there isn't native support for ULID one can use UUIDv7/8 and bridge that gap. We use ULID extensively in exposed IDs, it's very URL friendly, copy/paste friendly, etc -- but it's a UUID datatype in the DB. (ie: ULID => UUID => DB)
  - rjurney 2y ago
    But I thought ULID will work in a UUID field in Postgres? Will it in other DBs?
necovek 2y ago
The benchmark for UUIDv7 insertion time includes UUID generation: when looking at simply index update cost, I'd like to see those decoupled. FWIW, I do expect UUIDv7 to take roughly half the time to generate if it has roughly half the random bits of UUIDv4 and an unprotected source of entropy is used.
avkrpatel 2y ago
Instead of uuid7 why cant they use ulid?
hakanderyal 2y ago
Using ULID has probably the best ROI among all the architectural decisions I've made. Generating IDs on the server before insert is helpful, and not just for distributed system issues. If I don't want to leak the timestamp, I just use an auto generated integer along with it.
Pinus 2y ago
I’m not sure I understand the bit about time-ordered values being a better fit for B-trees. Actually, I’m sure that I don’t understand it... If anything, I’d naively assume that inserting already-sorted values would be a worst case, always going down the same branch of the tree and triggering re-balancing operations left and right (figuratively — literally mostly left!). The Wikipedia article on B-trees notices that there are some shortcuts that can be taken if one knows one is going to do a batch insert of sorted values. Is that what happens? But then how does Postgres know this?
- conradludgate 2y ago
  Btrees don't need to be rebalanced. They only split. The cost of splitting to the root is counteracted by the cache locality of always accessing the same set of pages. Inserting into the last page only needs O(logn) page accesses and O(logn) page writes worst case.
  - Pinus 2y ago
    Ah, of course. Thanks!
blits 2y ago
You might also be interested in TypeID, which is extension of UUIDv7 and also sortable and unlike UUIDv7 more human readable. It is inspired by Stripe IDs and looks like that: user_2x4y6z8a0b1c2d3e4f5g6h7j8k I've recently built a postgres extension for it that allows to use it like UUID: https://github.com/blitss/typeid-postgres https://github.com/blitss/typeid-postgres
- hu3 2y ago
  love the underscore instead of hyphen. easy to double click for selecting text.
bob1029 2y ago
I see some comments conflating privacy of sequence statistics with global uniqueness considerations and UX. If your concern is globally unique identifiers (i.e. so that you can merge tables across multiple instances of your database), then UUID is exactly what you want. This is entirely what it is designed for. If your concern is the privacy of sequence statistics, then UUID incidentally solves your problem. It may not be precisely what you want, and could continue to leak private information depending on the specific variant used. If you want privacy of sequence statistics, then I would suggest something like a sha256 hash of the primary key concatenated with a cryptographic salt stored in a separate column. These make excellent identifiers in places like APIs and URLs. If you desire a unique identifier that has a high quality UX, then this is in addition to the above columns. This sequence generally has lower entropy than the cryptographic approach (or the UX would suffer), so additional measures should be taken to protect the privacy of the identifiers (e.g. expire redemption tokens after a period of time and reissue via email). Autoincrementing integers are really nice if you don't actually need UUIDs. Lots of tricks you can apply with them.
- almog 2y ago
  > It may not be precisely what you want, and could continue to leak private information depending on the specific variant used. If you want privacy of sequence statistics, then I would suggest something like a sha256 hash of the primary key concatenated with a cryptographic salt stored in a separate column. These make excellent identifiers in places like APIs and URLs. Is there a reason not to use version 4 UUID and if time ordering is needed, save timestamp explicitly as another column?
  - bob1029 2y ago
    Type 4 guids are a reasonable choice. I personally prefer the extra 128 bits of entropy, but I have no evidence that says it's more secure in practice.
- tinodb 2y ago
  Most of the comments here seem to gear towards “you must always…”. But as you rightfully point out, “it depends” and is an architectural trade off depending our the required qualities.
branko_d 2y ago
I'm surprised author didn't mention foreign keys: since primary key is often referenced by foreign keys, then if you have a "fat" PK all your FKs will also be "fat". This can be solved by using the UUID as an alternate key and the regular integer as a primary key in the "top" table, and then referencing that integer from all the "child" tables. Obviously, this has ramifications for data write perf, but may well be worth it depending on the use case. Technically, you could also make the integer alternate (and leave UUID as primary), but that may not de desirable for clustered / index-organized tables (for DBMSes that support them).
- groestl 2y ago
  If the UUID is used for external representation, now you need to join a row to get it.
GiorgioG 2y ago
UUIDs are miserable to work with. For 99% of use cases, sequential IDs are fine. Can they be guessed? Sure, but your software should guard against unauthorized access. Security through obscurity or randomness is a poor excuse for using UUIDs as PKs. If you don't want to expose your IDs, use a slug.
- hu3 2y ago
  Agreed. The dev experience of debugging with UUIDs involved degrades so much, it is depressing. For example it's much harder to spot patterns and wrong IDs in SQL query results if you are looking at these giant blobs of random characters called UUIDs.
kortex 2y ago
Has there ever been an attack/leak based on time metadata in UUIDs/ULIDs/Snowflakes/etc (not sequential), in a domain outside of sensitive ones (HIPAA/healthcare, defense)? I'm skeptical that for the vast majority of uses, have time data in the IDs (and also not leaking it from actual timestamp fields) is an issue, and for the cases where it does, just use random IDs. Even theoretically, is there a way to translate timestamps into a German tank problem? If I give you a sampled set of timestamps (with no other node identifiers), can you estimate the cardinality of the greater dataset?
danolivo 2y ago
The big problem of serials is their monotonic grow. Each time adding new record you go beyond statistics boundaries and challenge PostgreSQL to guess..

436 points by taubek 2y ago

I couldn't really tell you why, but I wrangle many gigabytes of UUID-keyed data for work, and adding a BRIM index yesterday sped things up by 400%. Just my two cents!

hu3 2y ago

Would have been nice to also include bigserial/bigint in the INSERT performance comparison to understand the impact of migrating those to UUIDv4 and UUIDv7.

AdamJacobMuller 2y ago
Also testing using postgres-native uuid generation

fabian2k 2y ago

My somewhat naive understanding was that random UUIDs were not that big of a deal in Postgres because it does not cluster by primary key. And of course a UUID (16 bytes) is larger than a serial (4 bytes) or bigserial (8 bytes) by a factor of 2-4 . This certainly might matter for an index, but on a whole table level where you have 20+ bytes overhead per row this doesn't seem that big of a deal for anything except very narrow tables with large row counts. So far my impression is that there are a whole lot of other things I need to worry about in Postgres before I spend time considering serial vs. random UUID vs. ordered UUID. Am I wrong here and this is something that really matters and you should invest more time in?

masklinn 2y ago
While Postgres does not cluster table, uuids do affect indexes significantly, random insertion causes increased index slack which translates to cache bloat and thus longer traversal and lower cache residency.
oppositelock 2y ago
Random UUID's are super useful when you have distributed creation of UUID's, because you avoid conflicts with very high probability and don't rely on your DB to generate them for you, and they also leak no information about when or where the UUID was created. Postgres is happier with sequence ID's, but keeping Postgres happy isn't the only design goal. It does well enough for all practical purposes if you need randomness.
- Spivak 2y ago
  > Random UUID's are super useful when you have distributed creation of UUID's, because you avoid conflicts with very high probability and don't rely on your DB to generate them for you See Snowflake IDs for a scheme that gives you the benefit of random UUIDs but are strictly increasing. Which is really UUIDv7 but fits in your bigint column. No entropy required.
- sgarland 2y ago
  > Postgres is happier with sequence ID's, but keeping Postgres happy isn't the only design goal. It literally is the one thing in the entire stack that must always be happy. Every stateful service likely depends on it. Sad DBs means higher latency for everyone, and grumpy DBREs getting paged.
  - SoftTalker 2y ago
    DBRE? I guess DBA is too old fashioned for the cool kids?
    - sgarland 2y ago
      Listen, I didn't make the title up, I just grabbed onto it from the SRE world because I love databases. There are some pragmatic differences I've found, though - generally, DBAs are less focused on things like IaC (though I know at least one who does), SLIs/SLOs, CI/CD, and the other things often associated with SRE. So DBRE is SRE + DBA, or a DB-focused SRE, if you'd rather.
  - matharmin 2y ago
kevin_nisbet 2y ago
> So far my impression is that there are a whole lot of other things I need to worry about in Postgres before I spend time considering serial vs. random UUID vs. ordered UUID. Am I wrong here and this is something that really matters and you should invest more time in? Like any sort of optimization I believe this will depend on your workload and what's important. For me when I switched to UUIDv7 a few months ago, it was basically no effort to switch v4 to v7 on a relatively new system. I was observing much higher batch insertion latencies than I expected, and producing inserts that touch less of the btree on an index created a very noticeable reduction in insertion latencies. But my workloads and latencies may look nothing like yours. On amazon RDS instances with EBS volumes and relatively low memory, insertion latency stood out, so using strategies that reduce the number of disk blocks that are needed has an outsized performance impact. This of course would produce different results on different hardware / system sizing.
munk-a 2y ago
To: > Am I wrong here and this is something that really matters and you should invest more time in? Specifically, no - you don't need to worry about it. Reconfiguring your tables to use a different style of unique identifier if your tables have a unique identifier is a bit of a pain but no more so than any other instance of renaming a column - if you want to minimize downtime you add the new column, migrate data to the column, deploy code that utilizes the new column and then finally retire the old column. Even if the previous version of the table lacked any sort of unique key it is still possible to add one after the fact (it's a bit technically harder to properly keep them in sync but it is possible to do safely). It's just a question of the cost of doing so and the benefits of it - I work in a system that exclusively uses integral keys and our data is such that we don't really suffer any downsides from that choice - if you're working in a larger system with less confidence in the security practices of other teams then avoiding sequential keys so that you have obscurity to fall back on if someone really drops the ball on real security isn't the worst idea... but I think the really compelling reason to prefer UUIDs is for the power of distributed generation... that really only applies to inherently decentralized or astoundingly large products though - and if your product eventually grows to astoundingly large you'll have plenty of time to switch first (probably the wake-up call will be closing in on running out of 4 bit serial unique keys).
- big_whack 2y ago
  Reconfiguring tables to use a different kind of unique ID (primary key in this context) can be a much bigger pain than an ordinary column rename if it is in use by foreign key constraints.
  - snicker7 2y ago
    OR if the primary key is exported out of the DB, i.e. for constructing URLs.
sgarland 2y ago
You still get performance hits from Visibility Map lookups, and WAL bloat. As a DBRE, I believe it always matters, and you should invest time in it. Pragmatically, it is unlikely to have noticeable effects until your tables are at least in the 1E5 rows range, if not higher. Unfortunately, by that point, it’s likely that you’ll find other things of higher importance to deal with, so the problem will only grow.
stouset 2y ago
UUIDv7 are good enough that you can just avoid thinking and use it as a “default”. The worst consequences of doing so are some slightly impacted performance and leak of some timing information but these are extremely minor drawbacks. Using completely random UUIDs is IMO the worst choice. It’s fine right up until it isn’t, and then you are stuck in hell with no good way out.
- groestl 2y ago
  > Using completely random UUIDs is IMO the worst choice Can't say that without context. I've worked in systems where even the version bits were randomized, and for good reason (i know, technically, no UUID anymore).
  - stouset 2y ago
    I’m talking about default choices. UUIDv7 has few significant downsides for most cases. UUIDv4 has serious downsides. There are obviously cases where the latter is appropriate but that will come from unique requirements.
    - groestl 2y ago
      How about secure by default?
      - stouset 2y ago
        “Secure” is only meaningful against a defined threat model. Most threat models for database IDs do not require their creation timestamp to be secret. Meanwhile every use case for database IDs requires them to be looked up in an index.
        groestl 2y ago
        I've run multiple info-exfiltrating attacks based on timestamps and sequential identifiers against various actors. There you have your threat model. While I don't have any clue about your "most", I suggest you don't ignore this vector for your opsec.
feydaykyn 2y ago
A dev at $WORK thought it wasn't a big deal too because they "tested" on their unrestricted dev setup with 3 rows in the table. Of course production crashed when their latest sql query did a multijoin on the real table with a few millions of rows. The size of the uuid needed to join filled the available RAM, everything slowed to a crawl and the system collapsed. The uuid as primary key can be seen as a risk multiplicator : it will amplify any performance issue you may have, converting a temporary slowness into a full system stop.
nextaccountic 2y ago
Postgres doesn't automatically reorder rows by clustering on a primary key but you can perform clustering manually, and if the table isn't completely filled it can attempt to maintain rows in order (as long as there is room for it) https://www.postgresql.org/docs/current/sql-cluster.html https://www.postgresql.org/docs/current/sql-cluster.html

quotemstr 2y ago

> If you have an option to choose, take a look at TSID maintained by Vlad Mihalcea. TSID: > A Java library for generating Time-Sorted Unique Identifiers (TSID). Wouldn't this TSID thing be more useful if it were implemented as a set of PostgreSQL stored procedures or something than a Java library? Not everyone uses Java.

busterarm 2y ago
It's forked from https://github.com/f4b6a3/tsid-creator https://github.com/f4b6a3/tsid-creator. While this is also in Java, it has many reimplementations in other languages.
avkrpatel 2y ago
Ulid is also similar to it.
stoperaticless 2y ago
- Plenty of use-cases want to generate it on application side, so they can just “insert”, instead of “insert+select the id” - not everybody uses Postgre (either way, somebody will be left out)

aabhay 2y ago

I think insert performance is a bad way to evaluate performance here, no? While B-Tree performance for time sorted keys is better on insert, what about during large transactions? In SQLite, my assumption was that the consensus was towards UUID4 rather than 7 because it meant less likelihood for page cache contention during transaction locks? Would that not also roughly map onto a Postgres-flavored system? Or dues Postgres only have row-level locking?

samokhvalov 2y ago
there is also a problem of data locality and blocks present in caches (page cache, buffer pool) at any given time, in general -- UUIDv4 is losing to bigint and UUIDv7 in this area
- shi 2y ago
  This is a valid point but it highly depends on the use case and larger context on whether this will be relevant or not. If you have a table where you fetch multiple rows close to each other in paginated manner this would be relevant for the performance but if you only fetch individual records by uuid, data locality wouldn’t increase the performance.

scotty79 2y ago

Isn't B-Tree with UUIDv4 keys getting more balanced than with UUIDv7? Doesn't longer insert time result in faster searches later?

stoperaticless 2y ago
Related pondering: - UUIDv4 causes page fragmentation (ideally, on disk pages of data would be stored in sorted fashion; HDDs still exist) - if in your app fresh records have more reads, having them close will help with read speed (being cpu cache friendly)
- hu3 2y ago
  To contribute if I may: Page fragmentation from UUIDv4 can be desirable for SQLite because marking the same page as dirty can give SQLite little opportunity to sync it to disk. Causing contention. In this scenario your Write Ahead Log (WAL) file just grows and grows. SQLite introduced WAL2 which means switching between 2 WAL files instead of one, helping to mitigate unbounded contention. But it's recent. Most tools don't have SQLite supporting that yet.

AtlasBarfed 2y ago

UUIDs are guaranteed to be unique? They often use tricks like including the MAC address of the generator machine and other ways to increase uniqueness assurances. It was my understanding that uuids are simply very very unlikely to duplicate in situations with random generation.

wongarsu 2y ago
UUIDv2 uses Mac addresses, but those turned out to be mostly a bad idea. Today when people say UUID they mean UUIDv4, which is just 124 random bits (and 4 version bits). Assuming a good random number generator it's basically impossible to generate the same UUIDv4 twice by pure chance. Even if you make billions of them per second it's vanishingly unlikely to happen.
throwawayffffas 2y ago
They are not theoretically guaranteed they are in practice though. 2^128 and 122 are big numbers. Even if you are producing a billion per second you have a 50% chance of not getting a collision for 100 years.
- jacobgorm 2y ago
  I've used 128 secure-random bits for ages, not caring for any of the UUID version nonsense. Per the birthday paradox, I need to have 2*64 entries in my tables to reach 50% collision probability, and it will be a while before I can afford that much storage anyhow.
  - efilife 2y ago
    And of course the sanest person gets downvoted, as always
  - jacobgorm 2y ago
    Should have been 2*64.
munk-a 2y ago

samokhvalov 2y ago

some related stuff: - https://commitfest.postgresql.org/48/4388/ https://commitfest.postgresql.org/48/4388/ (original patch created live https://www.youtube.com/watch?v=YPq_hiOE-N8 https://www.youtube.com/watch?v=YPq_hiOE-N8) - https://postgres.fm/episodes/uuid https://postgres.fm/episodes/uuid - https://postgres.fm/episodes/partitioning-by-ulid https://postgres.fm/episodes/partitioning-by-ulid - https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/main/0065_uuid_v7_and_partitioning_timescaledb.md https://gitlab.com/postgres-ai/postgresql-consulting/postgre...

inopinatus 2y ago

The best advice I can give you is to use bigserial for B-tree friendly primary keys and consider a string-encoded UUID as one of your external record locator options. Consider other simple options like PNR-style (airline booking) locators first, especially if nontechnical users will quote them. It may even be OK if they’re reused every few years. Do not mix PK types within the schema for a service or application, especially a line-of-business application. Use UUIDv7 only as an identifier for data that is inherently timecoded, otherwise it leaks information (even if timeshifted). Do not use hashids - they have no cryptographic qualities and are less friendly to everyday humans than the integers they represent; you may as well just use the sequence ID. As for the encoding, do not use base64 or other hyphenated alphabets, nor any identifier scheme that can produce a leading ‘0’ (zero) or ‘+’ (plus) when encoded (for the day your stuff is pasted via Excel). Generally, the principles of separation of concerns and mechanical sympathy should be top of mind when designing a lasting and purposeful database schema. Finally, since folks often say “I like stripe’s typed random IDs” in these kind of threads: Stripe are lying when they say their IDs are random. They have some random parts but when analyzed in sets, a large chunk of the binary layout is clearly metadata, including embedded timestamps, shard and reference keys, and versioning, in varying combinations depending on the service. I estimate they typically have 48-64 bits of randomness. That’s still plenty for most systems; you can do the same. Personally I am very fond of base58-encoded AES-encrypted bigserial+HMAC locators with a leading type prefix and a trailing metadata digit, and you can in a pinch even do this inside the database with plv8.

vbezhenar 2y ago
IMO using bigserial by default is wrong. Use whatever data type is appropriate. Not every table will grow to 4 billion rows and not every table will grow to even 60k rows. ID data type leaks to every foreign key referencing given table. Many foreign key usually will be indexed, so this further degrades performance. There are multiple data types for a reason.
- inopinatus 2y ago
  Defaulting to 64-bit integers internally is to me a matter of mechanical sympathy, it has little to do with row capacity. It’s just a word size that current CPUs and memory architectures like working with.

netcraft 2y ago

Another day, another article saying not to use UUIDs as PKs. I've maintained systems using UUIDs stored as char(36) with million record tables without issue - This is not an endorsement, just explaining that this is bikeshedding. Should you use v7 when you can? Sure. Would int/bigint be faster in your benchmarks? Sure. But the benefits totally outweigh the speed differences until you get to a very large system. But instead of worrying about this, spend your energy on a million other things first and then celebrate when UUIDs become your bottleneck.

eerikkivistik 2y ago
I was gonna say... When your system is large enough to run into this specific performance bottleneck, pop a bottle and celebrate, you are making enough money to solve that problem. While knowing this information is useful, most services fail in different domains and problems way before you reach that point. I'm not sure people really comprehend how hard you can hit a single machine before you need to distribute a workload.
paulddraper 2y ago
I haven't maintained any sizable database system without any issues, least of all performance ones. I call BS.
- tomnipotent 2y ago
  This gave me a good chuckle, and has generally been my experience. Systems grow often in unpredictable or unintuitive ways. You can pay the cost for something upfront, and the cost of maintaining it, and in the long term paid too much for something you didn't actually need. Alternatively you can wait to pay it until you're certain you need it but the work involved has become much more significant, in which it can cost more than it would have to have built and maintained it from the beginning. Compounding the issue is the build-up-front scenario costs fade with time and you don't really think about them, but build-when-you-need-it always creates a stir even if the costs are less overall than build-up-front. Either way something will go wrong no matter how many times you predict where the cards will fall.
- 2y ago

brigadier132 2y ago

My strategy is to use v4 Uuids for anything that is not inserted frequently and don't need to be ordered (think user ids) and v7 ids for things that are. If your dataset is small the overhead from Uuids wont matter, if your dataset is large the randomness of Uuids will save your ass when you migrate to a distributed solution.

rareitem 2y ago
Is there a reason to not always use v7 by default?
- davisp 2y ago
  Information leakage since they have a timestamp component. Some people may not care, but plenty of folks do. As others have said, anything security related likely should UUIDv4. UUIDv7 is basically an engineering compromise on security (they leak a timestamp) vs performance (random reads and writes to an index aren’t as performant as localized reads and writes).
  - rareitem 2y ago
    Thanks, I get it. Do you have any example where leaking a timestamp could pose a security risk? I can't think of any.
    - lazide 2y ago
      Timing attacks?
    - mewpmewp2 2y ago
      If it is public, any cases where for an entity, when it was created could be potentially sensitive information. E.g. some sort of legal documents, product, ecommerce data that could potentially reveal data to your competitors, etc.
- brigadier132 2y ago
  Information leakage like the other person said but also if you ever move to a distributed db v7 uuids can lead to hotspots.

rootedbox 2y ago

Just a heads up about UUID 7.. be careful when using. par the RFC If UUIDs are required for use with any security operation within an application context in any shape or form then [RFC4122] UUIDv4 SHOULD be utilized.

spoiler 2y ago
I was thinking of adopting UUIDv7 for some of my stuff. So, I'm curious: why is this the case? Is it because of the time component?
- vbezhenar 2y ago
  1. UUIDv7 allows to extract timestamp 2. UUIDv7 allows to predict first half, if you know the timestamp. 3. UUIDv7 provides 62 bits of randomness compared to 122 bits for UUIDv4. Whether that's a problem for your particular use-case or not, it's up for you to decide. I don't think that UUIDv7 is "insecure", it just provides different trade-offs and in some situations it might be less secure compared to UUIDv4, but I hardly see any attack vector where you could issue 2^62 requests to brute-force the ID.
- lulzury 2y ago
  That's what the RFC states: > Timestamps embedded in the UUID do pose a very small attack surface. The timestamp in conjunction with an embedded counter does signal the order of creation for a given UUID and its corresponding data but does not define anything about the data itself or the application as a whole. If UUIDs are required for use with any security operation within an application context in any shape or form, then UUIDv4 (Section 5.4) SHOULD be utilized. https://datatracker.ietf.org/doc/html/rfc9562#name-security-considerations https://datatracker.ietf.org/doc/html/rfc9562#name-security-...
- deleted 2y ago
  [deleted]
jandrewrogers 2y ago
Unfortunately, UUIDv4 is often specifically prohibited for some sensitive applications due to many cases of broken RNGs undermining the security guarantees of UUIDv4. Deterministic generation plus encryption is a common choice in these cases.

vog 2y ago

Note that the article's link to the UUID v7 standard is meanwhile outdated. You should instead head directly to RFC 9562: https://datatracker.ietf.org/doc/html/rfc9562 https://datatracker.ietf.org/doc/html/rfc9562 (which wasn't yet finished at the time of the article)

londons_explore 2y ago

It would be nice for these comparisons to also include 'int64' so people can see how much of an overhead UUID's are compared to the traditional approach.

tills13 2y ago
The problem here is that auto increment ints are guessable. The size of that problem depends on your situation
- lazide 2y ago
  Also auto increment IDs that are exposed publicly cause [https://en.m.wikipedia.org/wiki/German_tank_problem https://en.m.wikipedia.org/wiki/German_tank_problem].
- sgarland 2y ago
  If your API is relying purely on the user ID for AuthZ, you have much bigger problems.
  - t-writescode 2y ago
    It's not just authz, it can also be a reflection of how many customers you have, and that can also be its own problem. Or, let's say you are a middleware company and you're trying to contract your services to two other companies. These companies are competitors of each other, but they're also the biggest in their space. If you have use, let's say, autoincrementing ints for orders or operations, then the companies could use the distance between order numbers as a way of calculating how much business the other business is doing. If that functionality is possible / easy, that may be a reason for those customers to stop using you, and you've now lost one or two of your major contracts. Apparently this has been brought up by others and is called the German Tank Problem.

nubinetwork 2y ago

If you're generating random UUIDs as the primary key, how do you not run into key collisions? Having to search the entire table before inserting is slow, and catching the error and trying again is also annoying.

davisp 2y ago
Worrying about UUID collisions is like worrying about being hit in the head by a meteor. Sure, its technically possible, but it happens so rarely that worrying about a collision as a performance concern is just a misunderstanding on how UUIDs work. And, it’s so random that if you ever do see a collision you should immediately start looking for a compromised system or bug. This is basically how GitHub discovered the OpenSSL bug that had removed too much entropy from the RNG setup.
- kevincox 2y ago
  You don't need to worry about a collision in a UUIDv4 that you created on your server. But I have seen a surprising number of applications that took a UUID generated client side and basically upserted it. Allowing taking over resources who's ID was known via the insert API (even if the update API has proper access control).
  - groestl 2y ago
    > UUID generated client side and basically upserted it Read and take notes. This is crazy in untrusted environments.
    - kevincox 2y ago
      Generating IDs on the client can be very useful for offline-first systems. But you need to check for conflicts and permissions on the server (or be sure to keep the IDs secret which I wouldn't recommend).
      - groestl 2y ago

sbuttgereit 2y ago

The article had a link to the PostgreSQL commitfest for UUIDv7 support, but as far as I can't tell it looks unlikely that it will actually be in PostgreSQL 17. The most recent action was the committer being removed from the task and I believe version 17 is already well past feature freeze.... Is my understanding correct? This is what I think is going on, but I can't find any substantiated facts too point me to a definite conclusion. I was hoping to see it in version 17, but can't get a really good read on what's going on with this feature.

klabb3 2y ago
What database support is needed? Assuming Postgres already has the uuid type and that you can (and often should) do the actual generation of them in application code?
- tyre 2y ago
  Why should they be done in application code?
  - eproxus 2y ago
    There’s no need since they are random, so you might as well generate them on your application servers which are easier to scale, to offload the write database.
    - bozey07 2y ago
      IMO marking the row as generated makes its intent clearer. It also means less code - no generating UUIDs, shorter queries = more robust. For my workloads at least, I think it's worth the tradeoff of forcing Postgres to muster 15 random bytes.
      - deleted 2y ago
        [deleted]
- sbuttgereit 2y ago

declan_roberts 2y ago

Call me old fashion but I really like integer autoincrement primary keys. It's easy to understand and obviously simple to sort. Furthermore when working on large batch projects you can just store the last primary key as your high water mark and get everything greater than that. I suppose TSID works to this end, but certainly more complicated.

tommy_axle 2y ago
It is simpler but like everything else it depends on the application. For a private app you can generally get away with it. Something that's more public facing? The ID will most likely leak information. As systems mature and you introduce things like replication, having IDs that are more universal starts looking good. In general, starting off with a uuid like uuid v4 or nanoid is a good bet.
- lazide 2y ago
  The info leak, specifically, is [https://en.m.wikipedia.org/wiki/German_tank_problem https://en.m.wikipedia.org/wiki/German_tank_problem].
  - sgarland 2y ago
    Oh no, someone might know the number of customers, or the rate of signups. Traditional businesses can figure this out by sitting in the parking lot. Why SaaS has decided it’s a huge problem is beyond me.
    - lazide 2y ago
      By sitting in every parking lot, yes. Which requires physical presence. And hedge funds do indeed do it against some targets specifically as a leg up, and pay quite a bit of money to do so, presumably because it is worth it to them. It certainly helped the Allies in the war, as previous intelligence had the rate of tank production much higher, and they were expending a lot of effort trying to exceed that previous false number. Information is power, etc. etc. As to if it's worth it for your SaaS to mitigate is up to you of course. I know I notice when things like Invoice #'s, my userID, customerID, etc. get shown, and it's a small number, or small delta. And that impacts my behavior. Does it also impact someone like a VC’s behavior? Or a competitor? Unknown. But Hell, a bunch of people right now are probably launching SaaS's with NO-OP auth flows for documents or images, or with trivial external SQL injection flaws, which is a way bigger problem. But it doesn't make the vulnerability/problem non-existent. Also feel free to use 16 bit auto incrementing primary keys for all your core tables if you want. But if we're in an engineering discussion on the topic, knowledgeable folks will bring up the issues with it, because that is what they do. Most would feel it is their duty to do it, even.

rizky05 2y ago

I've been using gen_random_uuid() (postgres's function) as default value in primary key. is it any downside ?

tantalor 2y ago

> inserting UUID v7 is ~2x faster and inserting regular UUID v4 Okay but why

mythrwy 2y ago
It tells you in the previous section: "UUID - even though always looks similar - comes in multiple variants. Java's UUID.randomUUID() - returns UUID v4 - which is a pseudo-random value. For us the more interesting one is UUID v7 - which produces time-sorted values. It means that each time new UUID v7 is generated, a greater value it has. And that makes it a good fit for B-Tree index."
- necovek 2y ago
  The benchmark seems to include generation in the total time. Not sure that's a useful comparison of how b-tree index behaves in each case (as UUIDv7 has fewer bits of randomness, it's also cheaper to generate).
  - mythrwy 2y ago
    Key generation time (for either type of UUID) is negligible compared to insert times. On the order of fractions of a microsecond for key generation as opposed to several milliseconds for inserts.
    - necovek 2y ago
      Thanks: I guess we've moved away from true sources of entropy for RNG and it's mostly pseudo-RNGs seeded by truly random numbers if we can achieve such performance today. I've done a quick benchmark of /dev/urandom locally, and I get ~450 MB/s out, which is roughly 28M worth of UUIDs per second — on my Ryzen 6850U laptop. Even that amounts to sub-microsecond times. I distinctly remember a time where you could only get a few MB/s, and external sources of true entropy were outright expensive for anything more than a few KB/s.

osigurdson 2y ago

I'm surprised there is no mention of hash based indexes (i.e. CREATE INDEX ... USING hash...) since lookups would conceivably always use equality.

openthc 2y ago
With UUIDv7/v8 (and ULID) there are some timestamps in the front half. I've seen spots where the query was in the style of `uuid_col >= 'SOME_UUID_0000' AND ulid_col <= 'SOME_UUID_FFFF'` When one is using them ast the timing for record create/insert these things happen.

sarreph 2y ago

We chose ULID for our Postgres PK recently, and this article helped a lot in making that decision: https://brandur.org/nanoglyphs/026-ids https://brandur.org/nanoglyphs/026-ids I personally prefer ULID since it is compat with a UUID type and you also get a timestamp lexicographically built into the ID so that sorting by ID also means sorting by timestamp. There are multiple PG extensions to make it easy to drop in and use.

saaspirant 2y ago
How do you deal with ulid exposing the timestamp (since is lexicographically sortable) ? Maybe your ULID is not public facing? Or this is not an issue for your application? I want to use something url friendly too since uuid sucks..
- presentation 2y ago
  You could probably just use random uuids and then encode/decode them to ULID whenever you read/write them.
- sarreph 2y ago
  I don’t have a problem with people knowing the timestamp - it’s only precise down to the second.

rjurney 2y ago

I've started using python-ulid as ULIDs seem superior to UUIDs. https://pypi.org/project/python-ulid/ https://pypi.org/project/python-ulid/

openthc 2y ago
I love ULID too; but it's really just UUIDv7 (or v8) with a mustache (it's all just 128bit IDs). And in the PG world, where there isn't native support for ULID one can use UUIDv7/8 and bridge that gap. We use ULID extensively in exposed IDs, it's very URL friendly, copy/paste friendly, etc -- but it's a UUID datatype in the DB. (ie: ULID => UUID => DB)
- rjurney 2y ago
  But I thought ULID will work in a UUID field in Postgres? Will it in other DBs?

necovek 2y ago

The benchmark for UUIDv7 insertion time includes UUID generation: when looking at simply index update cost, I'd like to see those decoupled. FWIW, I do expect UUIDv7 to take roughly half the time to generate if it has roughly half the random bits of UUIDv4 and an unprotected source of entropy is used.

avkrpatel 2y ago

Instead of uuid7 why cant they use ulid?

hakanderyal 2y ago

Using ULID has probably the best ROI among all the architectural decisions I've made. Generating IDs on the server before insert is helpful, and not just for distributed system issues. If I don't want to leak the timestamp, I just use an auto generated integer along with it.

Pinus 2y ago

I’m not sure I understand the bit about time-ordered values being a better fit for B-trees. Actually, I’m sure that I don’t understand it... If anything, I’d naively assume that inserting already-sorted values would be a worst case, always going down the same branch of the tree and triggering re-balancing operations left and right (figuratively — literally mostly left!). The Wikipedia article on B-trees notices that there are some shortcuts that can be taken if one knows one is going to do a batch insert of sorted values. Is that what happens? But then how does Postgres know this?

conradludgate 2y ago
Btrees don't need to be rebalanced. They only split. The cost of splitting to the root is counteracted by the cache locality of always accessing the same set of pages. Inserting into the last page only needs O(logn) page accesses and O(logn) page writes worst case.
- Pinus 2y ago
  Ah, of course. Thanks!

blits 2y ago

You might also be interested in TypeID, which is extension of UUIDv7 and also sortable and unlike UUIDv7 more human readable. It is inspired by Stripe IDs and looks like that: user_2x4y6z8a0b1c2d3e4f5g6h7j8k I've recently built a postgres extension for it that allows to use it like UUID: https://github.com/blitss/typeid-postgres https://github.com/blitss/typeid-postgres

hu3 2y ago
love the underscore instead of hyphen. easy to double click for selecting text.

bob1029 2y ago

I see some comments conflating privacy of sequence statistics with global uniqueness considerations and UX. If your concern is globally unique identifiers (i.e. so that you can merge tables across multiple instances of your database), then UUID is exactly what you want. This is entirely what it is designed for. If your concern is the privacy of sequence statistics, then UUID incidentally solves your problem. It may not be precisely what you want, and could continue to leak private information depending on the specific variant used. If you want privacy of sequence statistics, then I would suggest something like a sha256 hash of the primary key concatenated with a cryptographic salt stored in a separate column. These make excellent identifiers in places like APIs and URLs. If you desire a unique identifier that has a high quality UX, then this is in addition to the above columns. This sequence generally has lower entropy than the cryptographic approach (or the UX would suffer), so additional measures should be taken to protect the privacy of the identifiers (e.g. expire redemption tokens after a period of time and reissue via email). Autoincrementing integers are really nice if you don't actually need UUIDs. Lots of tricks you can apply with them.

almog 2y ago
> It may not be precisely what you want, and could continue to leak private information depending on the specific variant used. If you want privacy of sequence statistics, then I would suggest something like a sha256 hash of the primary key concatenated with a cryptographic salt stored in a separate column. These make excellent identifiers in places like APIs and URLs. Is there a reason not to use version 4 UUID and if time ordering is needed, save timestamp explicitly as another column?
- bob1029 2y ago
  Type 4 guids are a reasonable choice. I personally prefer the extra 128 bits of entropy, but I have no evidence that says it's more secure in practice.
tinodb 2y ago
Most of the comments here seem to gear towards “you must always…”. But as you rightfully point out, “it depends” and is an architectural trade off depending our the required qualities.

branko_d 2y ago

I'm surprised author didn't mention foreign keys: since primary key is often referenced by foreign keys, then if you have a "fat" PK all your FKs will also be "fat". This can be solved by using the UUID as an alternate key and the regular integer as a primary key in the "top" table, and then referencing that integer from all the "child" tables. Obviously, this has ramifications for data write perf, but may well be worth it depending on the use case. Technically, you could also make the integer alternate (and leave UUID as primary), but that may not de desirable for clustered / index-organized tables (for DBMSes that support them).

groestl 2y ago
If the UUID is used for external representation, now you need to join a row to get it.

GiorgioG 2y ago

UUIDs are miserable to work with. For 99% of use cases, sequential IDs are fine. Can they be guessed? Sure, but your software should guard against unauthorized access. Security through obscurity or randomness is a poor excuse for using UUIDs as PKs. If you don't want to expose your IDs, use a slug.

hu3 2y ago
Agreed. The dev experience of debugging with UUIDs involved degrades so much, it is depressing. For example it's much harder to spot patterns and wrong IDs in SQL query results if you are looking at these giant blobs of random characters called UUIDs.

kortex 2y ago

Has there ever been an attack/leak based on time metadata in UUIDs/ULIDs/Snowflakes/etc (not sequential), in a domain outside of sensitive ones (HIPAA/healthcare, defense)? I'm skeptical that for the vast majority of uses, have time data in the IDs (and also not leaking it from actual timestamp fields) is an issue, and for the cases where it does, just use random IDs. Even theoretically, is there a way to translate timestamps into a German tank problem? If I give you a sampled set of timestamps (with no other node identifiers), can you estimate the cardinality of the greater dataset?

danolivo 2y ago

The big problem of serials is their monotonic grow. Each time adding new record you go beyond statistics boundaries and challenge PostgreSQL to guess..