How do you work with database identifiers that get astronomically high

Question

This question is not related to the problem that I had (yet), but it keeps me up at night. Theoretically, in the end, as the number of rows in the table increases and increases, the identifier corresponding to each row "expires" from the numbers, right? I assume that if you continue to add depth to the value of the column, you can keep it up, but there should ultimately be an upper limit, right?

I heard about a technique called "sharding", but did not explain it in sufficient detail to satisfy my curiosity. It is the answer to the fact that you just continue to add more numbers or there is a smart template that allows you to "repeat" identifiers in their own sandbox. What if these sandboxes need to interact?

Answer 1

Sharding has nothing to do with the size of identifiers.

The right way to handle this: don't worry about it.

A 32-bit int has enough values ​​that you could add one line per second for more than 100 years without expiration.

And if that was not enough, with a 64-bit int you could add 1 billion lines per second and last more than 500 years.

So basically: don't worry about it. Choose an int size based on how many lines you expect from 100 and then move on.

Re sharding:

This is just a way to split a database into multiple servers, using the rule to decide which server should receive the data. The rules vary depending on what you store and how many servers you have.

Answer 2

From dev.mysql.com

Use a large enough integer data type for the AUTO_INCREMENT column to maintain the maximum sequence value you need. When the column reaches the upper limit of the data type, the next attempt to generate the sequence number fails. For example, if you use TINYINT, the maximum allowed sequence number is 127. For TINYINT UNSIGNED, the maximum size is 255.

It is really exciting. I think the simple (semi) answer is to use BIGINT - with a maximum value of 18,446,744,073,709,551,615. If you get a lot of columns - then ... wow ...