ミツカリ技術ブログ

株式会社ミツカリの開発チームのブログです

DBのプライマリーキーはUUIDか整数か？実際に検証してみた

DB

こんにちは、ミツカリCTOの塚本こと、つかびー(@tsukaby0) です。

先日、こんなツイートが少し話題になっていました。

要約すると

  • 3000万ユーザー・10億APIヒット/日のBaaSでpkeyは意図的に連番だった
  • UUIDによるキャッシュヒット率の低下は大規模システムでは恐怖
  • 128bit vs 32bitの差は最終的に8倍16倍に増幅される
  • 外部公開用には別途 public_id カラムを作るのがおすすめ

こういったpkeyをintegerにするか、UUIDにするかは割とよく聞く話ではあります。私は過去アドテクノロジー・Web広告界隈にいましたが、そのような環境では採番の頻度が一般的なSaaSのアクセス量の比ではないので、UUIDが用いられることが多かったです。ただ、そうではないほとんどのユースケースではintegerが一般的です。

どうしてintegerにすべきか、詳細は元ツイートを読んでいただくとして、知識や経験としてはある程度わかっていたつもりですが、実際にUUIDをpkeyにして困ったというケースには遭遇していません。そのため、UUIDにするとどの程度不利なのか検証してみることにしました。

結論

  • Kenn Ejimaさんの主張に同意であり、pkeyはintegerでよく、必要に応じて公開用のpublic_idを追加すればいい
  • public_idではなく、関数による導出もありだが、外部環境を考慮するとpublic_idとして用意した方が良い
  • 今回の検証ではinteger vs UUID v7でキャッシュヒット率が 96.93% vs 2.78% となった
    • (全体的にかなり簡略化した検証方法であり、メモリや実際のクエリ等にかなり左右されます。一概にこれだけ大きな差が出るわけではないので注意です)
  • 厳しい環境ではキャッシュヒット率が致命的になりうる

検証環境

Dockerコンテナ上でPostgreSQLを動かし、ホスト側からクエリを実行します。キャッシュミスを発生させるため、メモリを厳しく制限しています。

コンテナの起動

# CPU 1コア、メモリ 128MB に制限したPostgreSQLコンテナを起動
# データサイズ（約300MB）に対してキャッシュが小さいため、キャッシュミスが発生しやすい環境
#
# PostgreSQLのメモリ関連パラメータ:
# - shared_buffers: PostgreSQLがデータキャッシュに使う共有メモリ領域
# - work_mem: ソートやハッシュ操作で使う作業メモリ（クエリごと）
docker run -d \
  --name pg-bench \
  --cpus="1" \
  --memory="128m" \
  -e POSTGRES_PASSWORD=postgres \
  -p 15432:5432 \
  postgres:18 \
  -c shared_buffers=16MB \
  -c work_mem=4MB

# 以降のコマンドで使う環境変数を設定
export PGPASSWORD=postgres

テーブル作成

ホスト側から psql でDBに接続し、検証のセットアップを行います。PostgreSQL 18から uuidv7() がネイティブでサポートされたので嬉しいですね。

# 接続確認
psql -h localhost -p 15432 -U postgres --pset pager=off -c "SELECT version();"

--pset pager=off は結果をlessなどのページャーで表示せず、標準出力に直接出力するオプションです。スクリプトでの利用やログ取得時に便利です。

以下のSQLでテーブルを作成します。

psql -h localhost -p 15432 -U postgres --pset pager=off -f - <<'SQL'
-- integer版
CREATE TABLE users_int (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(255),
  created_at TIMESTAMP DEFAULT NOW()
);

-- UUID v7版（PostgreSQL 18からネイティブサポート）
CREATE TABLE users_uuid (
  id UUID PRIMARY KEY DEFAULT uuidv7(),
  name VARCHAR(100),
  email VARCHAR(255),
  created_at TIMESTAMP DEFAULT NOW()
);
SQL

検証

準備ができたので、データを入れつつ検証を開始していきます。

まずはひたすら実行して、結果の考察は後述します。

検証1：INSERT性能

300万件のINSERTにかかる時間を計測します。UUID v7もintegerも時系列順でB-treeに追記されるため、ページ分割の問題は発生しません。差が出るとすればUUID生成コストとキーサイズによるインデックス書き込み量の違いです。なお、UUID v4は時系列でないという問題を持っており、よりパフォーマンス面で深刻な問題が発生しますが、今回は検証対象としません。

# integer版
psql -h localhost -p 15432 -U postgres --pset pager=off -c "\timing" -c "
INSERT INTO users_int (name, email)
SELECT
  'user_' || i,
  'user_' || i || '@example.com'
FROM generate_series(1, 3000000) AS i;
"

# UUID版
psql -h localhost -p 15432 -U postgres --pset pager=off -c "\timing" -c "
INSERT INTO users_uuid (name, email)
SELECT
  'user_' || i,
  'user_' || i || '@example.com'
FROM generate_series(1, 3000000) AS i;
"
方式 所要時間
integer 9,309 ms
UUID v7 4,888 ms

検証2：ストレージサイズ

テーブルとインデックスのサイズを比較します。UUIDは128bit（16バイト）、integerは32bit（4バイト）なので、キーサイズだけで4倍の差があります。今回は他にテーブルを用意していませんが、実運用上では外部キーなどがあるため、4倍ではなく8倍、16倍と増えていく部分です。

psql -h localhost -p 15432 -U postgres --pset pager=off -c "
SELECT
  relname as table_name,
  pg_size_pretty(pg_total_relation_size(relid)) as total_size,
  pg_size_pretty(pg_relation_size(relid)) as table_size,
  pg_size_pretty(pg_indexes_size(relid)) as index_size
FROM pg_stat_user_tables
WHERE relname LIKE 'users_%'
ORDER BY relname;
"
テーブル テーブルサイズ インデックスサイズ 合計 備考
users_int 234 MB 64 MB 298 MB
users_uuid 266 MB 90 MB 357 MB +20%

検証3：SELECT性能（単一行ランダム取得）

全データへの均一ランダムアクセスを行い、キャッシュ効率の差を検証します。

シンプルにpsqlでランダムなIDを指定してSELECTを繰り返します。UUID版は事前にID一覧をファイルに出力し、shufでランダム選択します。psql接続のオーバーヘッドは両者同じなので公平に比較できます。ただし、psqlを都度呼び出すのはかなり遅いので、理想的にはpgbenchなどを使った方が良いです…が今回はとある理由により使えないのでこの方法で行きます。理由は後述します。

# UUID一覧をファイルに出力
psql -h localhost -p 15432 -U postgres -tAc "SELECT id FROM users_uuid" > /tmp/uuids.txt
# コンテナ再起動でキャッシュクリア
docker restart pg-bench

# 統計情報をリセット
psql -h localhost -p 15432 -U postgres -c "SELECT pg_stat_reset();"

# integer版: ランダムIDで10000回SELECT
time for i in {1..10000}; do
  id=$((RANDOM % 3000000 + 1))
  psql -h localhost -p 15432 -U postgres -tAc "SELECT * FROM users_int WHERE id = $id" > /dev/null
done

# integer版のキャッシュヒット率を確認
psql -h localhost -p 15432 -U postgres --pset pager=off -c "
SELECT
  relname,
  heap_blks_hit as hits,
  heap_blks_read as reads,
  ROUND(heap_blks_hit::numeric / NULLIF(heap_blks_hit + heap_blks_read, 0) * 100, 2) as hit_ratio
FROM pg_statio_user_tables
WHERE relname = 'users_int';
"
# コンテナ再起動でキャッシュクリア
docker restart pg-bench

# 統計情報をリセット
psql -h localhost -p 15432 -U postgres -c "SELECT pg_stat_reset();"

# UUID版: ランダムUUIDで10000回SELECT
time for i in {1..10000}; do
  uuid=$(shuf -n 1 /tmp/uuids.txt)
  psql -h localhost -p 15432 -U postgres -tAc "SELECT * FROM users_uuid WHERE id = '$uuid'" > /dev/null
done

# UUID版のキャッシュヒット率を確認
psql -h localhost -p 15432 -U postgres --pset pager=off -c "
SELECT
  relname,
  heap_blks_hit as hits,
  heap_blks_read as reads,
  ROUND(heap_blks_hit::numeric / NULLIF(heap_blks_hit + heap_blks_read, 0) * 100, 2) as hit_ratio
FROM pg_statio_user_tables
WHERE relname = 'users_uuid';
"
方式 クエリ数 ヒット数 ディスク読み取り ヒット率 1クエリあたりreads
integer 10,000 9,693 307 96.93% 0.03
UUID v7 6,300 180 6,286 2.78% 1.00

※ psql経由だとかなり遅いので、UUID v7方式は途中で打ち切りました。そのため6,300回しかクエリできていません。ただキャッシュヒット率の計算にはそれほど問題ないはずです

クリーンアップ

検証が終わったらコンテナを削除します。

docker stop pg-bench && docker rm pg-bench
rm -f /tmp/uuids.txt

結論と考察

今回の検証結果をまとめると：

検証項目 integer UUID v7 差
INSERT (300万件) 9.3秒 4.9秒 UUIDが速い（※後述）
ストレージサイズ 298 MB 357 MB +20%
キャッシュヒット率 96.93% 2.78% 約35倍の差
1クエリあたりディスク読み取り 0.03回 1.00回 33倍

INSERTでUUIDが速かったのは計測や実行環境の問題がありそうです。今回の検証は何回かやり方を変えながら実施したのですが、他の実行ではintegerの方が若干速かった（20%程度）です。

ストレージサイズは +20% です。UUIDの方が大きいのは当たり前ですね。ただ、これは検証方法がよくありませんでした。nameやemailカラムを入れてしまったので、その分の影響で正確な差を出せていません。それらの列がない方が良かったですね。

本命であるキャッシュヒット率には劇的な差が出ました。

UUIDは1クエリあたり約35倍のディスク読み取りが発生しています。 これはKenn Ejimaさんが指摘していた「UUIDによるキャッシュ効率の低下」を裏付ける結果です。

UUIDのキーサイズ（128bit）はinteger（32bit）の4倍あるため、同じページに格納できる行数が少なくなります。その結果、ランダムアクセス時にキャッシュに必要なデータが乗っている確率が下がり、ディスクI/Oが増加します。

推奨構成について

Kenn Ejimaさんのツイートでは推奨の構成が投稿されていましたが、これに同意します。

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,           -- 内部用：64bit連番
  public_id VARCHAR(16) UNIQUE        -- 外部用：random_hex(16)
);

この構成であればツイートの通り、シャーディングを回避して長く構成を変えずに維持できると思います。

public_idを関数等で都度導出するか、固定で入れるか

public_idカラムを用意せずに、integerのpkeyから何らかのハッシュ関数や暗号化で都度public_idを導出する方法もあります。

しかし、DBにpublic_idカラムとして値を持たせる方が実務上は便利です。

  • BIツールやデータ分析基盤での利用: Metabase、Redash、BigQueryなどでデータを参照する際、関数で導出する方式だとツール側で同じ関数を実装する必要があります。カラムとして存在すれば単純なJOINやフィルタで済みます。
  • 外部システム連携: Webhook、API連携、ログ解析などで外部システムがpublic_idを参照する場合、DBに値があればSQLで直接検索できます。
  • デバッグのしやすさ: 本番障害時に「このpublic_idのユーザーを調べたい」となった時、カラムがあれば即座にクエリできます。
  • インデックスの効率: カラムに対してインデックスを貼れば、public_idでの検索も高速です。関数インデックスも可能ですが、管理が複雑になります。

導出方式だと不便な具体例として、以下のようなシナリオが考えられます（実際に近いものを体験しています）。

  • 営業「ID public_id=foo のお客様からバグの問い合わせがあったのですが、確認お願いできますか？」
  • 開発者1「分かりました、調べます。（えーと fooってどのIDだっけ・・・デコード関数を実行しないと・・・）」
  • PdM「GA4のデータでUserID public_id=barを記録していると思うのだけど、そのユーザーのDBのデータとGA4のデータをJOINして分析するレポートをBI環境に作ってください」
  • 開発者2「分かりました。（frontendにはpublic_idを返しているからidは返していないんだよね。BIにあるDB等のデータをJOINしないと...ああ、BI環境でdecodeしないと・・・）」
  • （後日）
  • 開発者1＆2「KPTでこの問題を共有します！調査やデバッグがやりづらいです！」
  • 開発者3「弊社の運用管理システム上でdecodeする機能を作ろう！あと、いちいちdecodeしなくていいようにpublic_idを表示する機能を作ろう！BI環境はBIにデータを入れる前のETLでdecodeする処理を入れよう！」

想像すると面倒ですね。初めからpublic_idがあれば解決する話です。

UUIDを使う場面

冒頭でWeb広告という話をしましたが、UUIDがpkeyでも良いケースは一定存在します。例えば関連ツイートで以下が参考になります。

必ずしもUUID pkeyが悪というわけではないですが、ほとんどのユースケースでは不要なので、もし利用する場合は同僚に説明したり、コードにコメントを残してなぜUUIDが必要なのかを明記すると良いでしょう。

検証で苦労したこと

今回の検証では、「キャッシュヒット率の差」を明確に示すまでに何度も方法を変更しました。あまり誰かの役に立つ気はしませんが、一応苦労した点を記載しておきます。

1. メモリ設定の調整

最初は memory=512MB や memory=256MB で実行していたのですが、OSのページキャッシュが効きすぎてキャッシュヒット率が両者とも100%になってしまいました。次に memory=64MB, shared_buffers=16MB で試しましたが、OOMが発生しました。最終的に memory=128MB, shared_buffers=16MB に落ち着きました。

こういうケースもあるので、我々が保守するDBで不要にUUID pkeyが使われていたとしても、致命的でないケースもあり、無視しても良い場合もあると思います。

2. pgbenchの制約

pgbenchはPostgreSQL標準のベンチマークツールで、カスタムSQLスクリプトを高速に繰り返し実行できます。integer版では \set id random(1, 3000000) のように変数にランダムな整数をセットできるため、純粋なPKルックアップの性能を簡単に測定できます。

しかし、pgbenchには外部ファイルから値を読み込む機能がありません。UUID版では300万件のUUID一覧から1つを選んで変数にセットしたいのですが、これができません。

そのため、pgbenchを諦めシンプルにpsqlでループする方式に変更しました。

  • integer版: $((RANDOM % 3000000 + 1))で直接ID生成
  • UUID版: 事前にファイル出力したUUID一覧からshufでランダム選択

psql接続のオーバーヘッドは大きいですが、両者同条件なので公平に比較でき、純粋なPK検索のキャッシュ効率を計測できました。

おわり

今回の検証は完璧ではないです。例えば実際にはデータに対して満遍なくランダムアクセスされるケースよりは、一部のデータにアクセスが集中するケースが考えられます。そのため、UUIDでもキャッシュヒット率が問題にならないケースはあります。また、そもそもアクセスは多いがデータ量が少なく、十分にキャッシュヒットする可能性もあります。より厳密な検証をする場合は、今回のような都度psqlコマンドを実行する方法ではなく、何らかのシナリオベースの負荷テストツールを使って検証した方が良いでしょう。

今後においても問題ない場合は無理に今あるUUIDカラムをintegerカラムに変える必要はないでしょうが、新しくシステムを作る場合は基本的には特別な理由がない限りはUUIDは避けるべきと言えるでしょう。

今回の検証のおかげで、この知識は私に刻まれたと思うので、今後も自信を持ってintegerのpkeyをベースにして行きたいと思います。

現在、ミツカリではITエンジニアを募集しています。興味のある方はぜひお気軽にご連絡ください！

herp.careers