Timeline for How to handle primary keys and UUIDs in a database
Current License: CC BY-SA 4.0
12 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Oct 7 at 21:33 | comment | added | Bergi |
@DavidT No, you should absolutely not store UUIDs as varchars. uuid, if your dbms supports it, or some uint128 type otherwise. Afaict, JoryGeerts was trying to hint at problems when using inappropriate data types, Ewan was joking about it, and gidds didn't get the sarcasm (or was expanding on it, and I didn't get it).
|
|
| Oct 7 at 16:36 | comment | added | Pablo H | Re: human entry. There're alias systems, whereby you replace a string of numbers (or hex) by something more amenable. E.g. a sequence of 3 or 4 words (taken from a fixed list). | |
| Oct 7 at 15:48 | comment | added | DavidT |
Just to clarify I had made the assumption that the application developer would use the correct type information UUID in both the database and the application. If a UUID was stored as a VARCHAR most of my logic would not hold. Was there any valid reason to store a UUID as a VARCHAR ?
|
|
| Oct 7 at 13:39 | comment | added | gidds | @Ewan That's a benefit of Unicode, not of any particular way of encoding it. (UTF-8, UTF-16, UTF-32, CESU-8, BOCU-1, and all the other variants can all store the full Unicode set, just with different storage efficiency, processing efficiency, robustness, etc.) | |
| Oct 7 at 10:40 | comment | added | Ewan | i store all my guids as varchar(50). UTF16 is best, youve got to think about international guids | |
| Oct 7 at 9:21 | comment | added | user1937198 | @BartvanIngenSchenau Isn't this specifically a question about databases? | |
| Oct 7 at 8:13 | comment | added | Jory Geerts | "At the end of the day a UUID is just a 128 bit integer" which is great, until somebody stores it as a UTF-8 VARCHAR(255). | |
| Oct 7 at 7:43 | comment | added | Bart van Ingen Schenau | @user1937198, keep in mind that the considerations regarding locality of inserts can be vastly different for memory resources and database resources. The arguments on the one do not apply (automatically) to the other. | |
| Oct 6 at 23:28 | comment | added | DavidT | I think the standard answer to this is benchmark multiple UUID generation functions, as long as time is the most significant component of the UUID, records that are generated around the same time will be close in the index. However that may reverse the previous benefit about not having the end of the index become hot. Trade-offs and benchmarking required. | |
| Oct 6 at 22:55 | comment | added | user1937198 | Whilst you list the non-locality of inserts as a performance advantage, a lot of sources I have seen regard this as a major disadvantage in most cases where it matters at all, because it means you are much more likely to miss cache and need to fetch pages from disk. Which is a lot more significant than issues around lock contention unless your inserts are perticuarly hot. | |
| Oct 6 at 22:13 | history | edited | DavidT | CC BY-SA 4.0 |
added 2 characters in body
|
| Oct 6 at 22:00 | history | answered | DavidT | CC BY-SA 4.0 |