PostgreSQL and UUID as Primary Key

Post by openthc » Sat, Jul 06, 2024, 3:59 AM UTC

aero_code wrote: post #59

munk-a wrote: post #23

Earlier quoted context omitted.

Your understanding is correct but you're underselling very very in this context. It is astronomically unlikely to hit a collision with the advised generation methods. If you want a possibly easier to grasp parallel git relies on SHA hashes never colliding and will break in a really awful way if you can produce two commits in a tree with the same hash - it's so astoundingly unlikely that people are okay summarizing it…

"it certainly will eventually" - I think even that is underselling how unlikely it is for Git 256-bit hashes to collide. I calculated (taking into account the birthday paradox), that even if 8 billion people on Earth each created a Git commit every second, that they would have to do that non-stop for 1,588,059,911 trillion years before there's a 50% chance that any of the two commits have the same hash. Our sun is pr…

Post by mjevans » Sat, Jul 06, 2024, 4:00 AM UTC

necovek wrote: post #141

inopinatus wrote: post #12

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, esp…

I've never played enough with UUIDs in Postgres, but I wonder if you could publicly expose only the random bits (i.e. the second half if an UUIDv7) and have another non-b-tree index on SUBSTR(id, 16) for quick lookups based on that value. Similar is done for "short commit hashes" too. Though I would wonder why go with UUIDs in that case at all?

Post by bruce511 » Sat, Jul 06, 2024, 4:13 AM UTC

gregoryl wrote: post #117

jerf wrote: post #105

Earlier quoted context omitted.

My Dream Web Framework, which for a variety of reasons was never and never will be built, has built-in functionality for obscuring IDs in some session-level map, so you can indicate through some sort of type that something is an ID and it automatically allocates some sort of randomized identifier on the way out and converts it back transparently on the way back in. Thus, not only would DB ids in principle never show…

I wrote that style of session mapping for a project long ago. It was fairly easy, but a massive pain in the ass to debug. Ended up needing to record the mappings in the backend for a period of time.

Post by mewpmewp2 » Sat, Jul 06, 2024, 4:20 AM UTC

rareitem wrote: post #57

davisp wrote: post #54

Earlier quoted context omitted.

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).

Thanks, I get it. Do you have any example where leaking a timestamp could pose a security risk? I can't think of any.

Post by arp242 » Sat, Jul 06, 2024, 4:23 AM UTC

azinman2 wrote: post #145

arp242 wrote: post #136

Earlier quoted context omitted.

That's not true, you can increment by 2, 10, 100, or any number. I'm not saying that's necessarily the best solution, but it's not true that you can't use it.

But if you’re distributed or offline incrementing by an arbitrary amount can still create collisions unless you’re willing to increment by very very large amounts at random, in which case you’ve effectively reinvented uuid

  Server 1: [1 4 7]
  Server 2: [2 5 8]
  Server 3: [3 6 9]