Hacker News Reader

Comment by crabmusket | Hacker News Reader

The surrogate key uniquely identifies a row in your database, which is an entity just as real and significant as the car or the employee or what-have-you. Don't confuse the two!

I agree with you that having a surrogate key isn't going to save you from the reasons why natural keys can be difficult. The complexity has to go somewhere. But not having a unique identifier for each row is going to make things extra difficult.

View on HN

Precisely. And if I have a surrogate key to identify “a car”, I get to define what makes a car unique for my purposes. Maybe that really is the VIN. Maybe today it’s the VIN, but tomorrow it’s whatever vehicle is currently being driven by its owner. Maybe it’s something else.

Some day I may need to track multiple VINs for a vehicle (maybe it’s got parts from multiple VINs and I want to track that). I can still always decompose that table and have an n-to-1 relationship between cars and VINs without migrating the rest of my data model.

The main thing is that the synthetic key should never leave the database and never be displayed in the app - if you want to have another key that represents the human oriented key do it, but it should be another field, an indexed field even, but one that has a lot less monotonic sequential properties that are inherent to synthetic database identifiers.

You want to change that human key? Sure. You want to to complain that the keys are not sequential? Sure. You want to actually make them weird strings that are harmful to my brain? Why not? You want to update primary keys in the database? No. Absolutely not.

I assume you hint at the security aspect of monotonic keys?

I've found this issue a bit overblown. It's basically security by obscurity, which is a nice bonus, but not something your security model can be based on.

I mean, it is a good practice to expose some kind of non-sequential key (e.g. UUIDv7), but it doesn't seem to me like a dealbreaker.

I love it when my competitors use sequential integer IDs.

Security by obscurity makes it more difficult for bad actors as an additional layer that they need to break. I don't see a reason why that's a bad thing and doesn't take a lot of effort to implement in this case.

I have been in a startup where competitors used our sequential keys to scrape a list of customers. Lesson learned the hard way with actual business consequences!

Sequential keys also leak information (German tank problem)

Your competitors can estimate number of customers, your growth rate and other stats that you often don't want them to know.

https://en.m.wikipedia.org/wiki/German_tank_problem

I'm not saying it's a bad practice, the opposite actually.

> I don't see a reason why that's a bad thing and doesn't take a lot of effort to implement in this case.

True, if you start your application from scratch. Like if I started designing a new app today, I'd just choose the UUIDv7 for the primary key.

It's not an easy thing to add into an existing application, though. I see applications leaking their internal IDs all the time, but usually it's not worth the effort to fix that, because it's a comparatively minor problem.

> I'm not saying it's a bad practice, the opposite actually

I'm sorry that I didn't make that more clear. I saw that you mentioned it as a best practice and are aware of the advantages. It's just that there are so many others that don't have the balanced view as you seem to have.

I have been involved in many discussions at my work place where "security by obscurity" is used as a way to shut down discussions. They changed their minds about sequential keys after the incident I mentioned, but it still has the power to "win" other discussions. Sure, we need to have rate limiting on ip-addresses, auth and other mechanisms, but they are not perfect and bugs happen all the time. An "unguessable" id is an additional security layer

> It's not an easy thing to add into an existing application, though

I agree, but there are ways to reduce the attack surface. You could add an extra "public id" field that can be used for lookup in addition to the existing id. In this way you can have a gradual migration where you go through each endpoint and migrate them individually without changing the foreign keys and other relations in the database (they would still use the sequential key). Maybe you end up not having time to migrate them all, but at least you can reduce the attack surface on the most sensitive endpoints.

If you have low volume endpoints you could perhaps even simply add a mapping layer where you do a simple db lookup to replace the public key with the internal without changing the existing queries. You could even cache this easily to reduce the db load. (both ids are permanently fixed and can be cached forever).

> I have been in a startup where competitors used our sequential keys to scrape a list of customers.

If your system allows customers to see each other (or worse: unauthenticated users to see customers) in this fashion in the first place then whether you're using a sequential integer v. a random UUID is the least of your problems.

The 'customers' could be free tier users - a social media type system where everyone has a public profile - intended for the public - would still be scrapable by /profile/1, profile/2, etc. Doesn't necessarily require 'authentication' for the exposing of sequential integers to have a bad outcome.

You're right. The urls were public to be shared (think of marketing material / ecommerce), so there was not a security incident.

But it did give our competitor free highly qualified leads that they could use to poach customers. This product was new to our customers, and we had spent a lot of time selling and convincing them that it was useful.

UUIDv7 may not be generated sequentially, but it is still temporally ordered and discloses a timestamp, which may be an undesirable leakage for some applications/environments. When obfuscation matters that much, use a UUIDv4 and eat the index performance hit.

Some might suggest, "encrypt your object identifiers on the way in/out", but there's a ton of pitfalls involved since for most applications they are now rolling their own crypto, and it also makes identifiers much longer.

Yes, you can go to great depths, but they each have trade-offs - in performance, increased complexity etc. and it's necessary to make a judgment for each particular app instead of applying the most overengineered solution everywhere.

Sort of, I have way more experience with clients saying that an invoice is missing (which turned out to be a rolled back transaction) and then I get to explain how transactions work, and then if the customer is smart enough they'll say something like "then why the hell is the invoice number part of that?"

A entity can exist over more than one row in your database, but it is useful to uniquely identify each row as the lowest common denominator.

> The surrogate key uniquely identifies a row in your database, which is an entity just as real and significant as the car or the employee or what-have-you. Don't confuse the two!

But the DBMS already maintains a row identifier (called rowid or ctid or whatever depending on the DBMS). Why do you need an explicit one?

It may be useful if you have data that originates from another source or if something outside of our system references your entity. In that case you need to keep some form of an externalRef, so it's usually easier to just use an id that you can control, for referencing both internally and externally.

In the DB we use[1] the internal row id is not stable:

The value returned by the function is not necessarily constant between queries as various operations performed on the database may result in changes to the row identifiers of a table.

So, users should refrain from using the ROWID function in ordinary situations; retrieval by primary key value should be used instead.

[1]: https://infocenter.sybase.com/help/index.jsp?topic=/com.syba...

Be careful with those. SQLite has a rowid concept, but it's not guaranteed to be stable - running a VACUUM against a table can reassign the rowids for every row!

https://sqlite.org/rowidtable.html says:

> If the rowid is not aliased by INTEGER PRIMARY KEY then it is not persistent and might change. In particular the VACUUM command will change rowids for tables that do not declare an INTEGER PRIMARY KEY. Therefore, applications should not normally access the rowid directly, but instead use an INTEGER PRIMARY KEY.

Because every DB can and will shift those as needed. They reference the physical location on disk for a given tuple. They are not meant for general consumption.

Reply on news.ycombinator.com