For a single database, bigints are smaller and faster, with less footguns.
UUIDs can be nice for an opaque public ID, however I'd still prefer something like a Sqid for space and usability.
But be careful!! Javascript WILL interpret your bigints as Number() and round them down because they are too big without telling you!!!
Famously seen by every snowflake user that has interacted with Javascript, quite an annoying problem.
In JS - BigInt is 64bit integer.
In anything else - BigInt is a arbitrarily large integer.
const obj = { a: 9007199254740993n }
JSON.stringify(obj, (_key, value) => typeof value === 'bigint' ? JSON.rawJSON(value.toString()) : value) {"a":9007199254740993}
{"a":"9007199254740993"} const json = '{ "a": 9007199254740993 }'
JSON.parse(json, (_key, value, context) => /^\d+$/.test(context.source) ? BigInt(context.source) : value)Node.js drivers will correctly read int64 as string or bigint, not number.
E.g. pg for PostgreSQL
Maybe there’s a buggy driver but I don’t know it.
You can of course, change the api such that it does {"id": "1324535222364012585"} instead and voila, it will no longer try parsing it as number. Or the many other workarounds people have recommended above (like appending a prefix, or using a different encoding), but why is it trying to parse a number thats too big and instead of throwing it just rounds down without telling you????!
You seem to be talking about JSON. (Which technically has no limit on number size or precision, but in practice is float64.)
Then it will always be a string and you will be free to change the format/type of the key in the future to UUID or whatever you like.
Why, you ask? Let's you have a number like 10,000 and you want to replace it with 20,000. You delete the leading 1, and boom! The number is now zero, and three of the digits are gone, and you'll have to retype them like you got no other things to do with your life.
const json = '{ "a": 9007199254740993 }'
JSON.parse(json, (_key, value, context) => /^\d+$/.test(context.source) ? BigInt(context.source) : value)A similar horror story from PHP, which I discovered by diagnosing a test failure. (Or maybe it was in production? Long ago, can't remember.)
I think the code in question was for some kind of web auth, comparing random 32-character hexadecimal strings. PHP has a "feature" where its == operator falls back to trying certain strings as numbers... and that includes a version with scientific notation. (12000 == "12000" == "12e3")
Such a collision through bad comparison may seem unlikely, but there are two islands of higher odds: 0*10^X is zero for any X, and X*10^0 is one for any X. Finally, leading zeros can be included. ("0e1234" == "00000e1" and "1234e0" == "9e0000")
The fix was simply going to stricter ===, but it definitely reinforced my dislike of "loose" languages.
It does not actually make it impossible to query the wrong table it just tells you quickly when you’ve done so.
All sequences use step 16.
Type A has discriminant/offset 0, yielding IDs {0, 16, 32, 48, 64, …}.
Type B has discriminant/offset 1, mapping to IDs {1, 17, 33, 49, 65, …}.
All the way up to Type P with discriminant/offset 15 and IDs {15, 31, 47, 63, 79, …}.
This is also trivially invertible so that you can determine the type from the ID.
A more common approach is to make IDs opaque strings and put a type prefix—A0, B12, P34, that kind of thing. But this way you can keep it as a number, if you wish.
I've developed a field survey app for foresters. They use it on toughbooks, tablets and phones. They are collecting spatial data, so the geometry column in the tables gets quite big. The app on the device uses a SQLite (Spatialite) database, the central database is Postgres (PostGIS). They will often edit the same area, so without UUIDs, there will be duplication of primary keys, thus making the database inconsistent. Then I will be flooded in support tickets and it will cause more slowdown than just using UUID4. And the performance drop of UUID7 is negligible compared to bigint for primary key.
IME, most of the arguments for why UUIDs make things better are due to developer ignorance of RDBMS features (or B+tree performance).
Inappropriate aliasing of integer keys allows for silent errors in queries because it will actually return some result a lot of the time. A UUID is immune to this problem. The model recognizes its mistake a lot more reliably when previously non-empty tables start showing up empty after attempting a join.