PostgreSQLの部分UNIQUEインデックス：論理削除しても重複登録を防ぐ設計

ユーザー名を変更・退会できるサービスでは、データを物理的に削除せず、削除日時を設定する場合があります。これは**論理削除（ソフトデリート）**と呼ばれる設計です。

たとえば、次の要件があるとします。

利用中のユーザー同士で同じユーザー名を登録できない
退会済みのユーザー名は再利用できる
2人が同時に登録しても、同じユーザー名を持つ現役ユーザーを作らない

PostgreSQLの部分UNIQUEインデックスなら、こうしたルールをアプリ側の事前チェックだけに依存せず、データベースで保証できます。

普通のUNIQUE制約ではなぜ困る？

CREATE TABLE app_users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  handle TEXT NOT NULL,
  deleted_at TIMESTAMPTZ,
  UNIQUE (handle)
);

この定義は、退会済みの行にも一意性を要求します。退会後の再登録を許可したい要件には合いません。

では UNIQUE(handle, deleted_at) にすればよいのでしょうか。これも違います。通常のPostgreSQLのUNIQUEでは、NULL同士は異なる値として扱われます。deleted_at がNULLの現役行を複数作れてしまうため、肝心の重複防止になりません。

解決策：現役ユーザーだけを対象にする

以下は最初から作る例です。前のテーブル定義とは別に実行してください。

CREATE TABLE app_users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  handle TEXT NOT NULL,
  deleted_at TIMESTAMPTZ
);

CREATE UNIQUE INDEX app_users_active_handle_uq
  ON app_users ((lower(handle)))
  WHERE deleted_at IS NULL;

WHERE deleted_at IS NULL はインデックスに含める行の条件です。この条件に当てはまる現役行だけにUNIQUE（一意性）が適用されます。

lower(handle) は大文字・小文字をそろえる式です。たとえば Alice と alice を同じユーザー名と扱いたい場合に使えます。これは式インデックスと呼ばれる仕組みです。大小文字を区別する仕様なら ON app_users(handle) に変更できます。

退会後の再登録を試す

INSERT INTO app_users(handle) VALUES ('Alice');

-- 行は残し、削除日時だけを設定
UPDATE app_users
SET deleted_at = now()
WHERE lower(handle) = 'alice' AND deleted_at IS NULL;

-- 退会済みなので同じユーザー名で再登録できる
INSERT INTO app_users(handle) VALUES ('alice');

-- 現役ユーザーと衝突するためエラー（SQLSTATE 23505）
INSERT INTO app_users(handle) VALUES ('ALICE');

23505 はUNIQUE違反のSQLSTATE（DBのエラー種別を表すコード）です。APIでは、このコードを検出して「そのユーザー名は使用中です」といった業務上のエラーに変換します。

アプリから「SELECTで空きを確認→INSERT」と2回問い合わせるだけでは、別リクエストが確認と挿入の間に割り込むことがあります。同時実行時の最終的な保証はDB制約に任せるのが重要です。

ON CONFLICTと組み合わせる

重複時にエラーとせず挿入を省略したい場合、次のように書けます。

INSERT INTO app_users(handle)
VALUES ('ALICE')
ON CONFLICT ((lower(handle))) WHERE deleted_at IS NULL
DO NOTHING
RETURNING id;

ON CONFLICT はUNIQUE違反時の代替処理です。DO NOTHING は挿入をやめ、RETURNING は実際に挿入された行の値を返します。衝突した場合、結果は0行です。

今回は部分UNIQUEインデックスのキー式と条件を指定する必要があります。部分UNIQUEインデックスは通常のUNIQUE制約名ではないため、インデックス名を ON CONFLICT ON CONSTRAINT に指定する方法とは区別してください。

検索時にもWHERE条件をそろえる

PREPARE find_active_user(text) AS
SELECT id, handle
FROM app_users
WHERE deleted_at IS NULL
  AND lower(handle) = lower($1);

EXECUTE find_active_user('Alice');

PREPARE はパラメーター付きSQLを準備する操作、$1 は最初の引数です。JavaのJDBCでは PreparedStatement に ? を書いて値を設定することに相当します。

部分インデックスは、クエリのWHERE条件がインデックスの対象条件を満たすとPostgreSQLが計画時に判断できる場合に利用候補となります。そのため deleted_at IS NULL を明示しましょう。検索するユーザー名の値をパラメーター化するのは問題ありませんが、インデックスの条件自体をパラメーター化すると対応を推論できない場合があります。

実際に使われるかどうかは表の件数や統計情報にも左右されます。

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM app_users
WHERE deleted_at IS NULL AND lower(handle) = 'alice';

EXPLAIN は実行計画を見る命令です。ANALYZE を付けるとSQLを実際に実行して測定するため、更新系SQLに使用するときは注意してください。

本番データへ導入する際のハマりどころ

1. 既存の重複を確認する。 既に現役ユーザーに重複があるとUNIQUEインデックスは作成できません。

SELECT lower(handle) AS normalized_handle, count(*) AS active_count
FROM app_users
WHERE deleted_at IS NULL
GROUP BY lower(handle)
HAVING count(*) > 1;

2. 大きなテーブルではロックを考慮する。 通常のインデックス作成は書き込みを待たせる場合があります。サービス運用中に追加するなら、次の CONCURRENTLY が候補です。最初のCREATE INDEXとの二重実行ではなく、既存テーブルへ導入するときの代替手順です。

CREATE UNIQUE INDEX CONCURRENTLY app_users_active_handle_uq
  ON app_users ((lower(handle)))
  WHERE deleted_at IS NULL;

CONCURRENTLY は書き込みを止めにくい一方、処理に時間がかかり、追加の負荷が発生することがあります。トランザクションブロック内では実行できません。 マイグレーションツールが自動でトランザクションを張る場合は設定を確認します。また、構築に失敗すると INVALID 状態のインデックスが残ることがあるため、エラー原因の解消後に状態を確認して復旧します。

3. 退会取り消しにも衝突がある。 退会済みのユーザーを復活させようと deleted_at をNULLに戻すとき、既にそのユーザー名を他の現役ユーザーが使っていればUNIQUE違反になります。

使い分けの結論

要件	選び方
全行で同じ値を禁止	通常のUNIQUE制約
現役行だけで同じ値を禁止	部分UNIQUEインデックス
大文字小文字を無視	`lower()`などの式インデックス（要件を確認）
同時登録の競合を防ぐ	DBの一意性制約＋23505のハンドリング
大規模な既存表に追加	事前重複チェック＋CONCURRENTLYの検討

「どの行の、どの値を一意にするか」をDBの定義に表現すると、アプリの実装と制約のズレを減らせます。

🐘 PostgreSQLの部分UNIQUEインデックス：論理削除しても重複登録を防ぐ設計