Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Avoid UUID Version 4 Primary Keys in Postgres

andyatkinson.com

Post reply on HN
Print view View original

361–370 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#361

Post by moomoo11 »

I’ll do it regardless because any time I’ve tried to chase optimization early like this, hardware has always evolved faster.

We have all faced issues where we don’t know where the data will ultimate live that’s optimal for our access patterns.

Or we have devices and services doing async operations that need to sync.

I’m not working on mission critical “if this fails there’s a catastrophic event” type shit. It’s just rent seeking SaaS type shit.

Oh no it cost $0.35 extra to make $100. Next year will make more money relative to cost increase.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#362

Post by gwbas1c »

sedatk wrote: post #278
gwbas1c wrote: post #149

I work on an application where we encrypt the integer primary key and then use the bytes to generate something that looks like a UUID. In our case, we don't want database IDs in an API and in URLs. When IDs are sequential, it enables things like dictionary attacks and provides estimates about how many customers we have. Encrypting a database ID makes it very obvious when someone is trying to scan, because the UUID wo…

That sounds quite troublesome if the encryption key is lost, compromised, or rotated for any other reason.

The key will never be rotated. (There's no reason to.)

We're not worried about key compromises.

If the key is lost, we have much bigger problems.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#363

Post by sgarland »

liuliu wrote: post #307
sgarland wrote: post #144

Earlier quoted context omitted.

> Permanent identifiers should not carry data. Did you read the article? He doesn’t recommend natural keys, he recommends integer-based surrogates. > A prime example of premature optimization. Disagree. Data is sticky, and PKs especially so. Moreover, if you’re going to spend time optimizing anything early on, it should be your data model. > Don't make decisions you will regret just to shave off a couple of milliseco…

I read it (and regret it is a waste of my time). Their arguments are: * integer keys are faster; * uuidv7 keys are faster; * if you want obfuscated keys, using integer and do some your own obfuscation (!!!). I can get on-board of uuidv7 (with the trade-off, of course, on stronger guessability). The integer keys argument is strange. At that point, you need to come up with a custom-built system to avoid id collision in…

You can hand out chunks of sequential ids from a central coordinator to avoid collision; this is a well-established pattern.

Re: natural keys (or something like it), I was using it as an example of how badly PK choice can impact performance at scale.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#364

Post by gwbas1c »

puilp0502 wrote: post #173
gwbas1c wrote: post #149

I work on an application where we encrypt the integer primary key and then use the bytes to generate something that looks like a UUID. In our case, we don't want database IDs in an API and in URLs. When IDs are sequential, it enables things like dictionary attacks and provides estimates about how many customers we have. Encrypting a database ID makes it very obvious when someone is trying to scan, because the UUID wo…

Few questions: * How do you manage the key for encrypting IDs? Injected to app environment via envvar? Just embedded in source code? I ask this because I'm curious as to how much "care" I should be putting in into managing the secret material if I were to adopt this scheme. * Is the ID encrypted using AEAD scheme (e.g. AES-GCM)? Or does the plain AES suffice? I assume that the size of IDs would never exceed the block…

> How do you manage the key for encrypting IDs?

The same way we manage all other secrets in the application. (Summarized below)

> Is the ID encrypted using AEAD scheme (e.g. AES-GCM)? Or does the plain AES suffice? I assume that the size of IDs would never exceed the block size of AES, but again, I'm not a cryptographer so not sure if it's safe to do so.

I don't have the source handy at the moment. It's one of the easier to use symmetric algorithms available in .Net. We aren't talking military-grade security here. In general: a 32-bit int encrypts to 64-bits, so we pad it with a few unicode characters so it's 64-bits encrypted to 128 bits.

---

As far as managing secrets in the application: We have a homegrown configuration file generator that's adapted to our needs. It generates both the configuration files, and strongly-typed classes to read the files. All configuration values are loaded at startup, so we don't have to worry about runtime errors from missing configuration values.

Secrets (connection strings, encryption keys, ect,) are encrypted in the configuration file as base64 strings. The certificate to read/write secrets are stored in Azure Keyvault.

The startup logic in all applications is something like:

1: Determine the environment (production, qa, dev)

2: Get the appropriate certificate

3: Read the configuration files, including decrypting secrets (such as the primary key encryption keys) from the configuration files

4: Populate the strongly-typed objects that hold the configuration values

5: These objects are dependency-injected to runtime objects

Re: Avoid UUID Version 4 Primary Keys in Postgres

#365

Post by kortex »

sedatk wrote: post #303
kortex wrote: post #302

I've been using ULIDs [0] in prod for many years now, and I love them. I just use string encoding, though if I really wanted to squeeze out every last MB, I could do some conversion so it is stored as 16 bytes instead of 26 chars. In practice it's never mattered, and the simplicity of just string IDs everywhere is nice. Sometimes I have to talk to legacy systems, all my APIs have str IDs, and I encode int IDs as just…

See https://news.ycombinator.com/item?id=46211578

The python implementation I use doesn't do this quirk. It's just timestamp + randomness in Crockford Base32. That's all I need. Sure it doesn't fully "comply with the spec" but frankly the sequence sub-millis quirk was a complete mistake.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#366

Post by K0nserv »

qntmfred wrote: post #308
K0nserv wrote: post #62

An additional thing I learned when I worked on a ulid alternative over the weekend[0] is: Postgres's internal Datum type is at most 64 bits which means every uuid requires heap allocation[1] (at least until we get 128 bit machines). 0: https://bsky.app/profile/hugotunius.se/post/3m7wvfokrus2g 1: https://github.com/postgres/postgres/blob/master/src/backend...

you may be interested in this postgres extension as well https://github.com/blitss/typeid-postgres

That is indeed interesting.

I have slightly different goals for my version. I want everything to fit in 128 bits so I'm sacrificing some of the random bits, I'm also making sure the representation inside Postgres is also exactly 128 bits. My initial version ended up using CBOR encoding and being 160 bits.

Mine dedicates 16 bits for the prefix allowing up to 3 characters (a-z alphabet).

Re: Avoid UUID Version 4 Primary Keys in Postgres

#367

Post by dspillett »

> Do not assume that UUIDs are hard to guess; they should not be used as security capabilities

It is not just about being hard to guess a valid individual identifier in vacuum. Random (or at least random-ish) values, be they UUIDs or undecorated integers, in this context are also about it being hard to guess one from another, or a selection of others.

Wrt: "it isn't x it is y" form: I'm not an LLM, 'onest guv!

Re: Avoid UUID Version 4 Primary Keys in Postgres

#368

Post by dspillett »

stickfigure wrote: post #180

This is incredibly database-specific. In Postgres random PKs are bad. But in distributed databases like Cockroach, Google Cloud Datastore, and Spanner it is the opposite - monotonic PKs are bad. You want to distribute load across the keyspace so you avoid hot shards.

I wouldn't say it is incredibly database specific, it is more database type specific. For most general, non-sharded, databases, random key values can be a problem as they lead to excess fragmentation in b-trees and similar structures.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#369

Post by lovich »

Dylan16807 wrote: post #351
lovich wrote: post #340

Earlier quoted context omitted.

A million dots scattered randomly over a graph can all land on the exact same coordinate if it’s truly random. What most people intuit as random is some sort of noise function that is generally dispersed and doesn’t trigger the pattern matching part of their brain

> A million dots scattered randomly over a graph can all land on the exact same coordinate if it’s truly random. It won't happen though. 0.00000000% chance it happens even once in a trillion attempts. > What most people intuit as random is some sort of noise function that is generally dispersed and doesn’t trigger the pattern matching part of their brain Yes, people intuit the texture of random wrong in a situation w…

> It won't happen though. 0.00000000% chance it happens even once in a trillion attempts.

It has the same odds as any other specific configuration of randomly assigned dots. The overly active human pattern matching behavior is the only reason it would be treated as special.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#370

Post by andatki »

ellisv wrote: post #161
aynyc wrote: post #158

I've seen this type of advice a few times now. Now I'm not a database expert by any stretch of imagination, but I have yet to see UUID as primary key in any of the systems I've touched. Are there valid reasons to use UUID (assuming correctly) for primary key? I know systems have incorrectly expose primary key to the public, but assuming that's not the concern. Why use UUID over big-int?

About 10 years ago I remember seeing a number of posts saying "don't use int for ids!". Typically the reasons were things like "the id exposes the number of things in the database" and "if you have bad security then users can increment/decrement the id to get more data!". What I then observed was a bunch of developers rushing to use UUIDs for everything. UUIDv7 looks really promising but I'm not likely to redo all of…

Note that if you’re using UUID v4 now, switching to v7 does not require a schema migration. You’d get the benefits when working with new records, for example reduced insert latency. The uuid data type supports both.
Post reply on HN