Our database has plenty of intermediate tables that are mainly containers for the foreign keys of others, especially in 1-to-many relations. Memory is getting cheaper, but whether disk or RAM, it’s limited. Not just on disk but during joins and sorts these keys need to live in memory. If you have a well normalized database, as we do at my current company, each use of the key as an FK starts adding up. So who cares if it’s twice as large as bigint or four times bigger than int? It’s just a few bytes, right? Primary keys get around in normalized databases Meanwhile, using one of several strategies, databases like PostgreSQL and SQL Server have a native type that is stored in 16 bytes. So bigint is needed in some cases and that uses 8 bytes. We have way more than 2 billion transactions in each of several databases. If our goal is to scale, and I mean really scale let’s first acknowledge that an int is not big enough in many cases, maxing out at around 2 billion, which needs 4 bytes. Doh! UUIDs are a painĭon’t underestimate how annoying it is to have to deal with values that are too big to remember or verbalize. When we converted to UTF-8 several of the compound-key indexes were not big enough to contain the larger strings. Things got really bad in one company where they had originally decided to use Latin-1 character set. 4 bytes for an int), strings don’t sort as fast as numbers because they rely on collation rules. Think twice - in two cases of very large databases I have inherited at relatively large companies, this was exactly the implementation. “Oh, pshaw”, you say, “no one would ever do such a thing.” Reasons UUIDs May Not be Good Don’t be naiveĪ naive use of a UUID, which might look like 70E2E8DE-500E-4630-B3CB-166131D35C21, would be to treat as a string, e.g. (But see below for a better alternative). If I am customer 12345678, it’s easy to guess that there are customers 123456569, and this makes for an attack vector.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |