PostgreSQLで外部キーを使う際の注意点 #AdventCalendar2022

はじめに

こんにちは、HRBrainでバックエンドエンジニアをしている稲垣です。趣味は2年前から子供と一緒に始めた将棋です。

最近、サービス開発をする中で、PostgreSQLの外部キーがパフォーマンスのボトルネックなるケースがあることを知ったので、実際に検証しながら解説していきたいと思います。
今回使用するコードはこちらにおいてあります。

前提条件

以下のような外部キーをもつテーブルを考えてみたいと思います。
チームとそこに所属するユーザを管理する単純なテーブル構成です。

CREATE TABLE IF NOT EXISTS teams (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    team_id BIGINT,
    CONSTRAINT users_team_id_fk FOREIGN KEY (team_id) REFERENCES teams (id)
);

上記のテーブルに対し、以下のようなシナリオを実行します。

1チームに50万人いるチームを最大100チームまで作成した後、その中の5チームをランダムに削除する。

HRBrainで開発するサービスはGoを使っていますので、このシナリオをGoで実装したいと思います。
なお、データベースへのアクセスにはgormを使用しております。

構造体

type Team struct {
	ID   int64  `gorm:"column:id"`
	Name string `gorm:"column:name"`
}

type User struct {
	ID     int64  `gorm:"column:id"`
	Name   string `gorm:"column:name"`
	Age    int    `gorm:"column:age"`
	TeamID int64  `gorm:"column:team_id"`
}

チームとユーザを作成する関数

func createTeam(teamName string) int64 {
	team := &Team{Name: teamName}
	err := db.Create(team).Error
	if err != nil {
		log.Fatal(err)
	}
	return team.ID
}

func createUsers(teamID int64) {
	const max = 500000
	users := make([]*User, 0, max)
	for i := 0; i < max; i++ {
		name := gimei.NewName()
		age := rand.Intn(80)
		user := &User{Name: name.Kanji(), Age: age, TeamID: teamID}
		users = append(users, user)
	}
	err := db.CreateInBatches(users, 1000).Error
	if err != nil {
		log.Fatal(err)
	}
}

チームとユーザを削除する関数

func deleteUsers(teamID int64) {
	err := db.Where("team_id = ?", teamID).Delete(&User{}).Error
	if err != nil {
		log.Fatal(err)
	}
}

func deleteTeam(teamID int64) {
	err := db.Where("id = ?", teamID).Delete(&Team{}).Error
	if err != nil {
		log.Fatal(err)
	}
}

10チーム分のチームとユーザ作成

teamIDs := make([]int64, 0, teamNum)
for i := 0; i < 10; i++ {
    ctx := context.Background()
    now := time.Now()
    teamName := fmt.Sprintf("team%d", i)
    teamID := createTeam(ctx, teamName)
    createUsers(ctx, teamID)
    fmt.Printf("%s is created: %s\n", teamName, time.Since(now))
}

5チーム分のチームとユーザを削除

rand.Shuffle(len(teamIDs), func(i, j int) {
	teamIDs[i], teamIDs[j] = teamIDs[j], teamIDs[i]
})

// 外部キーが設定されているため、先にユーザを削除する
for i := 0; i < 5; i++ {
    deleteUsers(teamIDs[i])
}

now := time.Now()
wg := &sync.WaitGroup{}
for i := 0; i < 5; i++ {
    wg.Add(1)
    go (func(teamID int64) {
        defer wg.Done()
        deleteTeam(teamID)
    })(teamIDs[i])
}
wg.Wait()
fmt.Printf("5チーム削除終了: %s\n", time.Since(now))

1チーム50万人のデータをNチーム作ってから5チーム削除し、削除にかかった時間を計測しています。
外部キーが設定されているため、usersテーブルから先に対象のチームのユーザを削除し、その後チームを削除しています。

このとき、チームの削除は主キーを指定して行われるため、5チームを削除する時間はチーム数が増えてもある程度一定になることが予想されます。

検証結果

このコードを実行すると結果は以下のようになります。

team is created: とログがでている部分は1チームのデータと所属する50万人のデータを作成している部分であるため、9〜10秒程度とそれなりに時間がかかっています。

実行環境にもよりますが、まあこんなもんでしょう。

チーム削除のログに関しては、どうでしょうか。

5チーム削除終了：289.325542ms

こちらもこんなもんでしょうか。

次に、100チームに増やしてみます。先程、1チーム分（50万ユーザの作成を含む）のデータを作るのに10秒くらいかかっていたので、100チーム作成するには1000秒くらいかかります・・・

すみません、ちょっとコーヒーを淹れてきます。

余談ですが、2022年12月現在、弊社オフィスにはカプセルコーヒーメーカーやインスタントスープがあり、同じビルの1Fにはスターバックス様が入っております。コーヒー好きにはたまらない環境ですね。

...

さて、結果を確認してみましょう。

チーム作成部分のパフォーマンスは変わらないのでスルーします。

チーム削除の計測結果は、どうでしょうか。

5チーム削除終了：8.773368504s

・・ん？

だいぶ遅くなっています。
繰り返しになりますが、チームの削除は主キーを指定して行われるため、5チームを削除する時間はチーム数が増えてもある程度一定になることを期待していましたが、かなり遅くなっていますね。

実は、これが本件の主題である、外部キーを使う際の注意点、になります。

外部キーを使うときの注意点

PostgreSQLの公式ドキュメントには、外部キーに関して以下の一文が記載されています。

被参照列が頻繁に更新される場合、参照列にインデックスを付け、その外部キー制約に関連する参照動作がより効率的に実行できるようにする方が良いでしょう。

これは、PostgreSQLにおいて、外部キーには暗黙的なインデックスが設定されないということを示しています。

今回の例でいうと、usersテーブルのteam_idにはインデックスが設定されていません。
そのため、usersテーブルの外部キーを条件とするようなクエリを発行した場合に、usersテーブルがフルスキャンされてしまいます。
より具体的には、teamsテーブルから1レコード削除する際に、整合をチェックするためusersテーブルに、削除対象のteam_idを保持しているデータをフルスキャンで探しにいってしまうのです。

usersテーブルには、最初の例では10チーム分で500万レコード、次の例では100チーム分で5000万レコードを保持していたため、teamsテーブルから1レコードを削除しただけでも、usersテーブルのフルスキャンがオーバーヘッドとなって遅くなっていたのです。

ということは、usersテーブルのteam_id列にインデックス列を設定すれば高速化されるはずなので、確認してみましょう。

インデックスを設定して再実行

usersテーブルのteam_id列にインデックス列を設定します。

CREATE INDEX users_team_id_idx ON users (team_id);

実行して、100チーム作成時の結果を確認してみましょう。

5チーム削除終了：299.4445ms

早くなりましたね！

10チーム作成時の結果も確認してみます。

5チーム削除終了：176.2355ms

usersテーブルのレコード数に関係なく5チーム削除時のパフォーマンスが安定しました！

MySQLの場合

MySQLではどうなのか気になる方もいらっしゃるかと思いますので、触れておきたいと思います。
MySQLの公式ドキュメントより1.7.3.2 FOREIGN KEY の制約によると、

MySQL では、外部キーカラムにインデックスを付ける必要があります。外部キー制約はあるが所定のカラムのインデックスがないテーブルを作成する場合、インデックスが作成されます。

との一文がありますので、今回発生したような問題は起きなそうです。

自分のサービス開発経験ではMySQLの利用のほうが圧倒的に多いのですが、この仕様が暗黙的であったこともあり、今回調べてみて始めて知りました。自分のようにMySQLから経験して、PostgreSQLを使うような場合には今回紹介した事象には遭遇しやすいのかもしれません。

まとめ

外部キーはテーブル間のデータの整合性を保つために有効な制約ではありますが、PostgreSQLにおいては暗黙的なインデックスの設定が行われないため、明示的にインデックスを設定しない限り、特定の条件下ではパフォーマンスボトルネックになりうる、という検証をしてみました。

自分の過去の体験では、1時間以上かかっていた処理が1分で終わるような劇的な改善になるケースもあるため、もし心当たりがある場合、確認されてみると良いかもしれません。

なお、本コードをローカル環境で実行すると、DockerのLocal Volumesを簡単に数GB単位で消費していくため、定期的に

docker volume prune

を実行されることをおすすめします。

最後に

弊社ではエンジニアを募集しています！

それでは、良い年末をお過ごしください！