Why database schemas often contain 32, 64, 128, etc.

Question

I understand that 2, 4, 8, 16, 32, 64, 128, 256 ... are the decimal equivalents of binary digits.

Is there a reason why they are used in databases? For example, VARCHAR fields often contain 255 characters. Since (I assume) each character is one byte, why is there a difference between using 255 characters and using 257 characters?

Answer 1

With varchar columns, the length is stored with data using unsigned integers in the leading data bytes. Least bytes used; one byte can store lengths from 0 to 255, two bytes from 0 to 65535, etc. Having made a length of 255, you will get the "largest value" from the minimum byte of length.

In the past days, single disk bytes stored in a string were worth saving. Although the drive is cheap now, thinking has remained, especially with gray-haired administrators.

It makes no sense to choose a length equal to 2, for example varchar(64) is just a habit / convention (I even follow it - and I don’t know why!).

Answer 2

Not just database schemas, but pretty much any programming artifact will contain many numbers of the form 2 ^ N or 2 ^ N-1. Although some of these applications make sense (for example, 2 ^ 32-1 is the largest number represented as a standard unsigned integer in many machine architectures), most uses of degrees 2 are less necessary. In practice, old hackers view powers 2 as saints and regard them as such.

Answer 3

Data in databases is often organized in pages . These pages are almost universally aligned with memory boundaries for memory and cache management. Choosing 2 ^ n sizes for your data allows you to optimize the use of space in your database.

Note. Depending on the mechanism, RDBMS 256 may not be the best choice for variable-length strings from the perspective of memory alignment, since the length of the string also takes up space, i.e. varchar(256) takes up 258 bytes.

Answer 4

This is more a habit than anything. There is nothing magic in varchar (32) or varchar (64), nor is there anything magic in the default values ​​that visual tools try to force you to use (e.g. varchar (50)). Many of these upper bounds were rooted in people's heads, since 640k will have enough memory for everyone, and we really need to worry about every byte.

In many cases, it comes down to a general concept. In the previous system, I worked in product managers, and had no idea what their requirements were. They wanted to keep the name, but they did not know what the name domain consists of, but one of them said that they had heard about the surname> 50 characters, so he knew that there should be more than 32 and more than 50. We returned from 64, he agreed that this is enough, and that AFAIK still exists.

Although we had a technical reason for email (varchar (320)), which at the time the standard dictated 320 characters, because 64 characters for the username / local part, 255 characters for the domain name and 1 character for @, Most other solutions were based on priority (for example, all subsequent names corresponded to the nvarchar (64) model, as was decided above) or logic (for example, URLs should not be nvarchar (max), but depending on the standard and browser capabilities on ( 2048) or varchar (4096), in this case not because it was power 2 but because other software or standards built their things to use power 2.