プライマリーキーをUNSIGNED BIGINT型にしたテーブルはいつオーバーフローするのか


## はじめに
僕の経験では、テーブルのプライマリーキーはUNSIGNED BIGINT型になっていることが多いです。この値にはいちおう最大値があるので、レコードを増やしていけば理論上いつかはオーバーフローしてしまいます。それがいつになるのか気になったので、調べてみました。


## UNSINGED BIGINTとは
ここではMYSQLのUNSINGED BIGINTについて書きますが、主なリレーショナルデータベース製品はどれも同じようなものだと思います。

https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

BIGINTは整数を扱うデータ型であり、INT型に比べて大きな値を扱えます。その中でもUNSIGNED BIGINTは符号なし、つまり正の値のみを扱うので、より大きな値を扱うことができます。その最大値はズバリ、
```math
2^{64}-1=18446744073709551615\fallingdotseq 1.8\times 10^{19}
```
です。日本語で言えば約1800京ですね。京は兆の一万倍です。

UNSIGNED BIGINTはテーブルのプライマリーキーのデータ型として用いられることが多いです。たとえばRuby on RailsやLaravelで何も考えずにテーブルを新規作成すると`id`というプライマリーキーが作成され、そのデータ型はUNSIGNED BIGINTです。レコードが増えるたびに自動でidの値が1ずつ増えていきます（AUTO INCREMENT）。

https://railsguides.jp/active_record_basics.html#%E3%82%B9%E3%82%AD%E3%83%BC%E3%83%9E%E3%81%AE%E8%A6%8F%E7%B4%84

https://laravel.com/docs/11.x/migrations#column-method-id

プライマリーキーは重複が許されませんから、UNSIGNED BIGINTの最大値がそのままテーブルに格納できるレコード数の最大値となります。つまりそれ以上のレコードを格納しようとするとオーバーフローになり、サービスが正常に動作しないということになります。

## UNSIGNED BIGINTはいつオーバーフローするか
では実際に、UNSIGNED BIGINTをプライマリーキーに指定したテーブルがオーバーフローするのはどんなときでしょうか？

### ケース1：1秒に1回レコード追加
単純に、1秒に1回レコードが追加されるとします。するとオーバーフローが起きるのは当然、UNSIGNED BIGINTの最大値である$2^{64}-1$秒後です。これを年に直すと、
```math
\dfrac{2^{64}-1}{60\times60\times24\times365 }\fallingdotseq5.8\times10^{11}
```
つまり約5800億年後です。現在の宇宙の年齢が140億年くらいなので、しばらく心配しなくてよさそうですね。

### ケース2：実在する大規模サービス
もちろん実際には、1秒に1回以上レコードが追加されるサービスは存在します。

どの程度正確な数字か分かりませんが、2015年におけるFacebookの全世界のいいね数は1分あたり約420万回だったそうです。1秒あたり約7万回ですね。
https://www.lifehacker.jp/article/151118internet_one_minute/
1つのいいねをプライマリーキーがUNSIGNED BIGINTのテーブルの1レコードとして保存すれば、オーバーフローするのは約840万年後です。

Twiter（現X）においては、金曜ロードショーで「天空の城ラピュタ」が放送される際、劇中の台詞に合わせて「バルス」とツイートするいわゆる「バルス祭り」という文化があります。2013年のバルス祭りの際は、1秒間に14万3199ツイートが記録されたそうです。
https://ascii.jp/elem/000/000/814/814653/
1ツイートをプライマリーキーがUNSIGNED BIGINTのテーブルの1レコードとして保存するとします。バルス祭りの状態が一時的でなく恒久的に続くと仮定すれば、オーバーフローするのは約410万年後です。つまり410万年間ぶっ通しでバルス祭りができます。
人類（猿人）がアフリカで誕生したのが約500万年前だそうなので、ほぼ人類の歴史と同じ期間ですね。

### ケース3：100億人の位置情報を0.1秒ごとに保存
ユーザーが100億人いるサービスを考えます。Googleのユーザー数は43億人だそうなので、あり得ない数字ではないですね。
https://simplique.jp/strongpoint-of-google/
現在の世界の人口を超えていますが、人口が100億人を超えた未来を考えてもいいし、1人が複数アカウントを持っていると考えてもよいです。

そして、すべてのユーザーの位置情報を0.1秒ごとに保存することを考えます。実際、Googleマップにはユーザーの移動履歴を保存する「タイムライン」というサービスがあります。さすがに0.1秒ごとという高頻度で取得しているとは思えませんが、まったくあり得ない想定ではないですね。
https://support.google.com/maps/answer/6258979?hl=ja&co=GENIE.Platform%3DAndroid

すると、1秒間に1000億の位置情報が取れることになります。これをプライマリーキーがUNSIGNED BIGINTのテーブルに格納すると、オーバーフローするのはたったの5.8年後です。一気に現実的な数字になりました。

## UUIDの場合
UNSIGNED BIGINTの代わりにUUIDを使う場合について考えてみます。

UUIDとはたとえば`30b08803-f43b-4ca4-a89a-e43566b1704a`のような形式のランダムに生成された文字列で、衝突の心配がないとされているためよくIDに利用されます。
実体としては128bitのデータであり、それを16進数の文字列にしたものです。
最もメジャーと思われるUUID v4においては、128bitのうち6bitが定数として割り当てられ、残りの122bitがランダムに生成されます。つまり$2^{122}\fallingdotseq 5.3\times 10^{36}$通りの値を表現できます。
https://tex2e.github.io/rfc-translater/html/rfc9562.html#5-4--UUID-Version-4

では、UUIDをテーブルのプライマリーキーにするとどうでしょうか。

注意すべきなのは、UUIDは連番ではなくランダムに生成されるので、理論上はいつでも衝突する可能性があるということです。
生成したUUIDをプライマリーキーに用いてレコードを保存しようとしたとき、たまたま保存済みのレコードの中にその値があれば、プライマリーキー重複のエラーになります。

一般的には重複の心配はほとんどないと言われていますが、実際にはどうでしょうか？
それについて調べてくれている記事があります。

https://qiita.com/kiririmode/items/9ddf7f2aec6e8ba4dc7f

この記事によると、UUIDを$n$回生成したときに衝突が発生する確率はおよそ

```math
1-\exp\left(-\frac{n^2}{2^{123}}\right) 
```
だそうです。

この式の$n$にUNSIGNED BIGINTの最大値$2^{64}-1$を突っ込むと、その値は0.99を超え、ほぼ100％となります！
つまり、UNSIGNED BIGINTを用いた場合は410万年間安全にバルス祭りを続けられたのに、UUIDを用いた場合は410万年間バルス祭りをするとほぼ確実にUUIDの衝突が起き、エラーが発生するということになります。
ですから、「410万年間エラーを起こさずにずっとバルス祭りをしたい」という要件であれば、プライマリーキーのデータ型として選択すべきはUUIDよりもむしろUNSIGNED BIGINTということになります。

しかしながら、この問題には回避策があります。UUIDの衝突が起こるとたしかにエラーになりますが、それはテーブルの保存量の限界を意味しているわけではありません。UUIDの衝突によるプライマリーキー重複エラーが起きたら、単にリトライして別のUUIDで保存しなおせばいいだけの話です。
ただ、レコードの総数がUUIDの総数に近づいてくるとそれだけ高い確率で衝突が起きますので、使い物にならなくなってきます。たとえば衝突確率を50%まで許容するのならば、保存できるレコードの総数は$2^{121}\fallingdotseq 2.7\times10^{36}$となります。


その場合はどれくらいバルス祭りができるかというと、

```math
\left(\frac{2.7\times10^{36}}{1.43199\times10^5}\right)\times\left(\frac{1}{60\times60\times24\times365}\right)\fallingdotseq5.9\times10^{23}
```
つまり約5900垓年です！垓というのは京の1万倍ですね。宇宙の年齢が140億年ですから、その約40兆倍の期間にわたってバルス祭りをすることができます。これはもう我々の感覚ではほとんど永遠のようなものですね。

ちなみに、100億人の位置情報を0.1秒ごとに記録するケースでは、約84京年です。宇宙の年齢の6000万倍ですね。これもほとんど永遠のように感じます。

## UNSIGNED INTの場合
蛇足にも思えますが、ついでにBIGINTではない単なるINT型についても調べてみます。UNSIGNED INT型の最大値は$2^{32}-1 = 4294967295$です。約43億ですね。

https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

1秒に1レコードを追加するシナリオの場合、オーバーフローするのは約136年後です。かなり短くなりました。
バルス祭りは約8時間で終了します。儚いですね。
100億人の位置情報を0.1秒ごとに記録するケースでは、そもそも最初の0.1秒のデータを保存しきれません。

## まとめ
以上の結果を表にまとめておきます（本文に記載していない結果も含んでいます）。
| ケース | 1秒あたりのレコード増加数 | オーバーフローが起こるまでの時間（UNSIGNED BIGINT）|衝突確率が50%を超えるまでの時間（UUID）|オーバーフローが起こるまでの時間（UNSIGNED INT）|
|:-:|:-:|:-:|:-:|:-:|
| 1秒に1レコード  | 1  |約5800億年|約8.6穣年|約136年|
|  ずっとバルス祭り | 14万3199  |約410万年|約5900垓年|約8時間|
|  100億人の位置情報を0.1秒ごとに保存 | 1000億  |約5.8年|約84京年| - |

もし、バルス祭りを410万年以上続けたい、あるいは100億人の位置情報を0.1秒ごとに5.8年以上取得し続けたいという要件であれば、テーブルのプライマリーキーはUNSIGNED BIGINTにせずUUID型とし、重複が起きたら別のUUIDで保存しなおすようにしておくとよいでしょう。

## おわりに
というわけで、UNSIGNED BIGINTを使っていればオーバーフローの心配はほとんどない、というのが結論です。
ただ個人的には、オーバーフローまでの時間は想像不可能なほど長いわけでもないな、という感想も持ちました。たとえばずっとバルス祭りの場合の410万年は人類の歴史と同じくらいですが、宇宙の歴史から見れば一瞬のようなものです。もっと、宇宙を何回繰り返しても全然大丈夫、というスケール感を期待していたのかもしれません（UUIDを使うとそのような結果になりましたが）。実際、100億人の位置情報を0.1秒ごとに保存する場合ではたった5.8年でオーバーフローしてしまいました。

本稿ではオーバーフローだけを考えましたが、もちろん実際のプライマリーキーのデータ型の選択においては、様々な要素が絡んできます。このあたりの選択の基準については、世の中に色んな記事があるのでこの記事では触れません。たとえば以下のような記事が参考になります。

https://zenn.dev/s3i7h/articles/12f25ca61fe02a

https://techblog.raccoon.ne.jp/archives/1627262796.html

https://zenn.dev/miya1216/articles/904cf18a30a90f

https://www.whaletech.co.jp/blog/database-pk-datatype/