Hacker News Reader

Comment by aynyc | Hacker News Reader

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?

View on HN

Uuids also allow the generation of the ID to seperate from the insertion into the database, which can be useful in distributed systems.

I mean this is the primary reason right here! You can pre-create an entire tree of relationships client side and ship it off to the database with everything all nice and linked up. And since by design each PK is globally unique you’ll never need to worry about constraint violations. It’s pretty damn nice.

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 our tables to use it.

You can use the same techniques except with the smaller int64 space - see e.g. Snowflake ID - https://en.wikipedia.org/wiki/Snowflake_ID

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.

At my company we only use UUIDs as PKs.

Main reason I use it is the German Tank problem: https://en.wikipedia.org/wiki/German_tank_problem

(tl;dr; prevent someone from counting how many records you have in that table)

What stops you from having another uuid field as publicly visible identifier (which is only a concern for a minority of your tables).

This way you avoid most of the issues highlighted in this article, without compromising your confidential data.

I'm new to the security side of things; I can understand that leaking any information about the backend is no bueno, but why specifically is table size an issue?

In my old company new joiners are assigned an monotonic number as id in tech. GitHub profile url reflected that.

Someone may or may not have used the pattern to get to know the attrition rate through running a simple script every month))

This was a great read, thank you for sharing!

Appreciate it!

At least for the Spanner DB, it's good to have a randomly-distributed primary key since it allows better sharding of the data and avoids "hot shards" when doing a lot of inserts. UUIDv4 is the typical solution, although a bit-reversed incrementing integer would work too

https://cloud.google.com/blog/products/databases/announcing-...

Reply on news.ycombinator.com