はじめに

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

UNSINGED BIGINTとは

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

BIGINTは整数を扱うデータ型であり、INT型に比べて大きな値を扱えます。その中でもUNSIGNED BIGINTは符号なし、つまり正の値のみを扱うので、より大きな値を扱うことができます。その最大値はズバリ、

2^{64}-1=18446744073709551615\fallingdotseq 1.8\times 10^{19}

です。日本語で言えば約1800京ですね。京は兆の一万倍です。

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

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

UNSIGNED BIGINTはいつオーバーフローするか

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

ケース1：1秒に1回レコード追加

単純に、1秒に1回レコードが追加されるとします。するとオーバーフローが起きるのは当然、UNSIGNED BIGINTの最大値である$2^{64}-1$秒後です。これを年に直すと、

\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をプライマリーキーに用いてレコードを保存しようとしたとき、たまたま保存済みのレコードの中にその値があれば、プライマリーキー重複のエラーになります。

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

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

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}$となります。

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

\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億ですね。

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年でオーバーフローしてしまいました。

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