61–70 of 345 posts
I suppose TSID works to this end, but certainly more complicated.
The best advice I can give you is to use bigserial for B-tree friendly primary keys and consider a string-encoded UUID as one of your external record locator options. Consider other simple options like PNR-style (airline booking) locators first, especially if nontechnical users will quote them. It may even be OK if they’re reused every few years. Do not mix PK types within the schema for a service or application, esp…
One challenge with PNR is actually restricting the alphabet appropriately. They sure are easy to say aloud -- just five or six letters in many cases -- but how do you ensure you have (a) enough letters to get a reasonable bitwidth and (b) not form ridiculous words?
Depends on what you mean by ridiculous.
For example https://sqids.org/ ensures that there are no profanities in the generated ids. And it allows you to add additional words that you want to avoid.
The best advice I can give you is to use bigserial for B-tree friendly primary keys and consider a string-encoded UUID as one of your external record locator options. Consider other simple options like PNR-style (airline booking) locators first, especially if nontechnical users will quote them. It may even be OK if they’re reused every few years. Do not mix PK types within the schema for a service or application, esp…
One challenge with PNR is actually restricting the alphabet appropriately. They sure are easy to say aloud -- just five or six letters in many cases -- but how do you ensure you have (a) enough letters to get a reasonable bitwidth and (b) not form ridiculous words?
You can still get vaguely offensive sequences like FKNNGR or BLKCNT, but at some point you have to put this down not to your software being offensive or hateful but to humans finding patterns in randomness.
Earlier quoted context omitted.
Using 32 bit ints for IDs is insane in today’s world. If an attacker can control record generation, e.g. creating a record via API, then they can easily exhaust your ID space. A lot of kernel vulnerabilities stem from using incrementing 32 bit integers as an identifier. If you’re considering using 32 bits for an ID, don’t do it!
If an attacker can create billions of records through your API, maybe that is a problem you need to address either way.
Just a heads up about UUID 7.. be careful when using. par the RFC If UUIDs are required for use with any security operation within an application context in any shape or form then [RFC4122] UUIDv4 SHOULD be utilized.
My strategy is to use v4 Uuids for anything that is not inserted frequently and don't need to be ordered (think user ids) and v7 ids for things that are. If your dataset is small the overhead from Uuids wont matter, if your dataset is large the randomness of Uuids will save your ass when you migrate to a distributed solution.
Is there a reason to not always use v7 by default?
Earlier quoted context omitted.
What database support is needed? Assuming Postgres already has the uuid type and that you can (and often should) do the actual generation of them in application code?
Why should they be done in application code?
Earlier quoted context omitted.
If an attacker can create billions of records through your API, maybe that is a problem you need to address either way.
Conversely: if using bigserial for a primary key introduces crippling performance problems to your system, maybe that is a problem you need to address either way.
Earlier quoted context omitted.
Defaulting to 64-bit integers internally is to me a matter of mechanical sympathy, it has little to do with row capacity. It’s just a word size that current CPUs and memory architectures like working with.
What architecture? Both amd64 and ARM64 can work with 32-bit integers just fine.
The best advice I can give you is to use bigserial for B-tree friendly primary keys and consider a string-encoded UUID as one of your external record locator options. Consider other simple options like PNR-style (airline booking) locators first, especially if nontechnical users will quote them. It may even be OK if they’re reused every few years. Do not mix PK types within the schema for a service or application, esp…
I dont understand the recommendation of using bigserial with uuid column when you can use UUIDv7. I get that it made sense years ago when there was no UUIDv7, but why do people keep recommending it over UUIDv7 now beats me.
You can guess the time the system took between 2 uuid v7 id's.
They can only be used if they're not shown to the user. (so not in the form mysite.com/mypage? id=0190854d-7f9f-78fc-b9bc-598867ebf39a)
A big serial starting at a high number can't provide the time information.