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.
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.
Yes, sure, it leaks some information - but to be fair printing an invoice also leaks information.
For me the priority is security. If I get a link (visible or invisible) that contains a numeric ID, there's the possibility to tweak that link with another number.
Ideally, the server treats that number as suspect. Every. Single . Time. In practice I only need one developer to miss the check in one place and I have a serious security issue. Like leaking my entire customer list serious. This has happened multiple times -that we know of-.
Yes obscurity is not security. But sequential numbers are insecure by design, and it takes a lot of well-maintained walls to protect them. One crack leaves them very vulnerable.
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.
It requires physical access. Presence can be achieved by placing a dash camera or window camera strategically.
> and they were expending a lot of effort trying to exceed that previous false number.
The idea here being that if the allies just made more tanks than the axis they would automatically win? It's a lionized story of intelligence agency cleverness built squarely on the back of insane military "strategy."
Having a dashcam in this situation is only useful if that dashcam is physically there during the times you care about eh? Which is the physical presence part. With some applied statistics, that can be reduced (no need to sit in every parking lot 24/7 for instance), but there is definitely some sitting in parking lots involved.
No, it’s that they would set tank production quotas (and budgets) based on what they expect to need to produce to crush the enemy. Modulo whatever level of effectiveness had been apparent so far.
Which, for any given set of resources, would mean taking away resources from something else. Either manpower, steel, oil/gas, etc.
So overproducing tanks means fewer aircraft, or less artillery, etc.
Convincing the enemy you’re producing too much (or too little) of something to get them to waste their time and energy is a classic counter intelligence move.
WW2 was won largely with logistics and economic might + reasonable tactics. Applying the economic might effectively was part of that.
> Convincing the enemy you’re producing too much (or too little) of something to get them to waste their time and energy is a classic counter intelligence move.
So much so that it calls into question the entire idea of clandestine wartime intelligence having any exceptional value.
> WW2 was won largely with logistics and economic might + reasonable tactics.
I think people who say this simply haven't counted the dead.
> Applying the economic might effectively was part of that.
Which had essentially no bearing on our problems with Japan.
Uh huh. Populations of conscriptable soldiers or percent of the population killed totally didn’t matter? Those are part of the economic equation.
The war in the pacific was started by, and almost entirely dominated by, economic matters. Japan attacked the US because of US embargoes on fuel, for instance.
And the creation of the atom bomb (which ended it) was only possible due to the insane logistical feats of the US - and its excess economic capacity and resources. Which is why Germany failed, and the USSR only got one after getting the ‘ingredients list’ post war - once the wartime resources had been freed up.
War is hell, so don’t start them. But also, don’t think modern war isn’t won (or lost) based on logistics and economic factors. Which intelligence can help with.
> 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.
To be clear, this is not directed at you specifically, and I have no idea what your level of expertise is on anything.
In general, I’ve found HN commenters level of knowledge to be fairly bimodal. They’re either regurgitating things they read on a Medium blog, or they really know their shit.
Every time this topic comes up, people delightedly mention the German Tank Problem, but I have never, not once, seen anyone post an actual example of when a modern business got rekt by a competitor using knowledge gained from monotonic IDs.
Re: security viz. AuthZ, my stance is the same as SQL injection – it’s such a trivially easy problem to avoid that it shouldn’t be a consideration for harming the performance of the DB. (Not that SQL injection mitigations cause performance impacts; sorry, that analogy didn’t work well)
> Every time this topic comes up, people delightedly mention the German Tank Problem, but I have never, not once, seen anyone post an actual example of when a modern business got rekt by a competitor using knowledge gained from monotonic IDs.
At $previous_job (payments provider), the sales engineers would often spot merchants using sequential order IDs and mention it to the account managers.
Rekt? I guess not, but knowing what percentage of their business we processed was extremely valuable information when it came to renegotiate the contract.
i mean, you might care if the investors you’re trying to woo for that bridge round figure out your churn is a lot higher than you’re willing to admit… or worse, your traction is terrible.
So I should introduce complexity in my application so in the future it might help me dupe potential investors if my startup is not going well? That’s a whole new take on pre-mature optimization. I’d rather not dupe investors, and would rather not introduce unnecessary complexity, instead I’d rather focus on actually delivering value so investors actually want to invest.
>And they managed it just fine despite working with auto-incrementing big ints.
I wonder how. I've had to do several big merges in my career, and it was always a nightmare because of all the external systems which were already referencing and storing those pre-existing ints. Sure, merging the databases is easy if you don't mind regenerating all the Id's, but it's not usually that simple.
Simplest way is to keep the identifiers from DB A and increment all the identifiers from DB B by an offset. Third parties complicates things of course but internally it can be pretty simple, so maybe they just didn't have too many third parties using the IDs.
They wrote a small script with the logic involved in the merging. PKs and FKs of only one database had to be incremented by an offset of max(table.pk) + safe margin.
They did this for each table.
Once this script was tested multiple times with subsets of each database, they stopped production and ran the script against it (with backup fallbacks). A small downtime window in a Sunday.
And that was it. The databases never had to pay the UUID tax, before or after.
Not being able to stop production database for a very short window once in a lifetime is another exceptionally rare business case.
I've seen architecture astronauts make their business pay unreasonable tech insurances by adding complexity to avoid simply pausing production for some minutes when it could have been much cheaper this way.
And from my understanding, in the case I mentioned, they chose to stop production to simplify the process. But they didn't have to.
A mixture of replication plus code changes to write in two databases could also have solved the issue.
Most business die because they can't move fast enough. Not because their production database stopped for a few minutes.
"If your architecture can't withstand life threatening solar flares, third world war, sabotaging of undersea cables and 1 billion concurrent users can you even call yourself an engineer?"
Be advised using the last auto-incremented value as a "high water mark" and getting everything greater than that is not 100% reliable, because in some scenarios auto-incrementing values can be written out of order.
Yup, not many people seem to know about CACHE vals and/or sequence reservation. It's a problem that I've had to explain a couple of times already to colleagues. And unfortunately hidden assumptions are oft the most dangerous kind.
I suppose TSID works to this end, but certainly more complicated.