181–190 of 345 posts
Earlier quoted context omitted.
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.
As uuid v7 hold time information, they can help bad actors for timing attacks or pattern recognition because they contain a time information linked to the record. 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 t…
Are you then not doing security by randomness if that is the thing that worries you?
Earlier quoted context omitted.
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.
As uuid v7 hold time information, they can help bad actors for timing attacks or pattern recognition because they contain a time information linked to the record. 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 t…
1. The UUIDs should be ordered internally, for B-tree performance
2. The UUIDs should not be ordered externally, for security reasons
Why not use encryption? The unencrypted ID is a sequential id, but as soon as it leaves the database, it's always encrypted. Like, when getting it out:
SELECT encrypt(id) FROM table WHERE something = whatever;
UPDATE table SET something = whatever WHERE id = decrypt()
Earlier quoted context omitted.
So it can’t use the internal id index, result: slow lookups for external ids.
> secondary indices on the external_id columns in both tables pick your poison, slower lookup or more disk usage
In fact if your external ids are evenly distributed you can do that anyway, only index the few leading / trailing bytes of the id. That will increase lookup costs a bit but significantly decrease storage.
Earlier quoted context omitted.
Not optimally.
It is not optimal to use 8-byte integers instead of 4-bytes. CPU works just as fast with both, however your CPU cache is limited and you'll put more 4-byte integers into your L1. I don't really understand what you want to convey. CPU is very fast with any kind of integer size. There's no performance penalty to use 1-byte integer compared to 8-byte integer. And there's performance penalty when your L1 or L2 or L3 cach…
As individual variables, they take exactly the same space as 64-bit integers, both in registers and in memory (i.e. in the stack used for automatic variables or in the heap used for dynamically-allocated variables), because of the alignment requirements.
Therefore it never makes sense to use other integers than 64-bit, except as members of aggregate data types.
A database table is an aggregate data type, so the use of small integers in some columns may be justified, but such types shall not be used for variables in the programs that process data from the tables, where misuse of the small integers may cause overflows and there is no advantage from using smaller integer sizes.
Earlier quoted context omitted.
As uuid v7 hold time information, they can help bad actors for timing attacks or pattern recognition because they contain a time information linked to the record. 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 t…
Big serial is sequential and it’s very easy to guess the next number. So you got the problem of sequential key attack… If you use only uuid in your outwards facing api then you still have the problem of slow queries. Since you need them to find the object (as mentioned below) UUIDv7 has a random part, can be created distributedly, and indexes well. It’s the best choice for modern application that support distributed…
Earlier quoted context omitted.
Take all the Roman alphabet apart from the vowels - 21 characters and length 6 gives you 100 million possibilities which is plenty for most applications. 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.
Removing Y turns out to be important, as well...
Earlier quoted context omitted.
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?
Do you ensure that your software does not form ridiculous numbers? Imagine that some christian person gets "666" number. What a scandal. Do you ensure that your software does not form ridiculous words in every language? Or just another US-centric thing? The idea of avoiding identifiers to be ridiculous is ridiculous to me, honestly...
Earlier quoted context omitted.
How would it be fine, e.g. for e commerce which is arguably very large portion of the use cases? You would be immediately leaking how many orders a day your business is getting with sequential id.
> You would be immediately leaking how many orders a day your business is getting with sequential id. Which is fine for almost all of them. All brick and mortar stores "leak" this too; it's really not that hard to guess number of orders for most businesses, and it's not really a problem for the overwhelming majority. And "Hi, this is Martin, I'd like to ask a question about order 2bf8aa01-6f4e-42ae-8635-9648f70a9a05"…