TL;DR
PostgreSQL の B-tree インデックスは各エントリを約 2712 バイトに制限しています — これは 8 KB ページの 1/3 です。text や varchar カラムにそれより長い値を格納した状態で CREATE INDEX を実行すると、即座に失敗します：
ERROR: index row size 3496 exceeds maximum 2712 for index "table_column_idx"
DETAIL: Values larger than 1/3 of a buffer page cannot be indexed.
HINT: Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.
最短の解決策：
- 最初の N 文字だけインデックスを作成する：
CREATE INDEX ON t (left(col, 255)); - 完全一致検索用に MD5 ハッシュをインデックス化する：
CREATE INDEX ON t (md5(col)); =比較のみが必要な場合はHASHインデックスに切り替える。LIKE/ 類似検索にはpg_trgmを使った GIN インデックスを使用する。
根本原因
PostgreSQL のインデックスページはすべて 8 KB です。ページのオーバーヘッドを差し引くと、B-tree の 1 エントリは floor((8192 − overhead) / 3) = 2712 バイト に制限されます。この制限は PostgreSQL のページサイズにコンパイルされており、実行時や postgresql.conf で変更することはできません。
text カラムは TOAST を通じてヒープに大きな値を格納でき、1 行あたり数 MB に達することもあります。これ自体は問題ありません。しかし、その行に対応する B-tree インデックスエントリは、1 ページに一度に収まらなければなりません。UTF-8 バイト長が約 2704 バイトを超えるカラム値を持つ行が 1 行でも存在すれば、CREATE INDEX 全体が中断されます。
この問題が発生しやすい典型的な 3 つのシナリオ：
- すでに長い行を含む既存の
textカラムにUNIQUE制約を追加する場合。 - URL、JSON ブロブ、またはフリーテキストフィールドに
CREATE INDEXを実行する場合。 - MySQL から移行する場合（MySQL では DDL レベルでプレフィックスインデックス長の扱いが異なる）。
修正方法
方法 1 — プレフィックスへの関数インデックス（最も一般的な修正）
多くの場合、一意性チェックや等値チェックには最初の数百文字があれば十分です。値全体ではなくプレフィックスにインデックスを作成しましょう：
-- 最初の 255 文字をインデックス化（必要に応じて調整）
CREATE UNIQUE INDEX table_column_prefix_idx
ON my_table (left(column_name, 255));
注意点があります：PostgreSQL がインデックスを使用するには、クエリで同じ式を使用する必要があります。
-- このクエリはインデックスを使用する
SELECT * FROM my_table WHERE left(column_name, 255) = left($1, 255);
-- このクエリはインデックスを使用しない（カラム全体を参照）
SELECT * FROM my_table WHERE column_name = $1;
検索を高速化するための非ユニークインデックスであれば、データ内でプレフィックスの重複が少ない限り、プレフィックスアプローチは有効です。
方法 2 — MD5 ハッシュインデックス（完全一致のみ）
値の完全一致や重複排除が必要な場合はカラムをハッシュ化します。MD5 は常に 32 文字の16進数文字列を生成するため、2712 バイトの制限を大幅に下回ります：
CREATE UNIQUE INDEX table_column_md5_idx
ON my_table (md5(column_name));
クエリのパターン：
SELECT * FROM my_table WHERE md5(column_name) = md5($1);
注意点：MD5 には理論上（天文学的に稀な）衝突リスクがあります。信頼できないユーザー入力など、セキュリティ上重要な重複排除処理では、インデックス検索で候補行を特定した後にアプリケーションレベルでの等値チェックを追加してください。
方法 3 — HASH インデックスタイプ
PostgreSQL ネイティブの HASH インデックスタイプは、インデックス対象の値にサイズ制限がありません。= 比較のみサポートしていますが、PostgreSQL 10 以降はクラッシュセーフになっています。
CREATE INDEX table_column_hash_idx
ON my_table USING hash (column_name);
以下のような場合に選択してください：
- すべてのクエリが
=を使用する —LIKE、ORDER BY、範囲スキャンなし。 - インデックスで一意性を強制する必要がない。
方法 4 — pg_trgm を使った GIN インデックス（LIKE / 類似検索）
長いテキストカラムで LIKE '%keyword%' 検索を行う場合、トライグラム拡張機能がまさにこの用途向けです：
-- データベースごとに一度だけ拡張機能を有効化
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX table_column_trgm_idx
ON my_table USING gin (column_name gin_trgm_ops);
このインデックスは以下の 3 つのクエリパターンすべてをカバーします：
SELECT * FROM my_table WHERE column_name LIKE '%search_term%';
SELECT * FROM my_table WHERE column_name ILIKE '%search_term%';
SELECT * FROM my_table WHERE column_name % 'approximate_match';
方法 5 — カラム型を短くする
長い値がデータ品質の問題であり、意図的な設計ではない場合もあります。その場合はカラム自体を修正してください：
-- 型を変更する前に既存の違反を確認する
ALTER TABLE my_table
ADD CONSTRAINT column_name_max_len CHECK (char_length(column_name) 2704) AS over_limit_rows
FROM my_table;
over_limit_rows が 0 であれば、問題はカラム値そのものではなく、複合インデックスのオーバーヘッドやエンコーディングの不一致が原因である可能性があります。0 より大きければ、上記のいずれかの方法が必要です。
検証
修正を適用した後、インデックスが正常に作成されたことを確認してください：
-- インデックスの存在と定義を確認する
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'my_table';
-- INVALID なインデックスがないことを確認する（部分的に構築されたインデックスはここに表示される）
SELECT relname, indisvalid
FROM pg_index
JOIN pg_class ON pg_class.oid = pg_index.indexrelid
WHERE indrelid = 'my_table'::regclass;
次に、実際のクエリで EXPLAIN を実行して、プランナーが実際にインデックスを使用していることを確認します：
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM my_table WHERE left(column_name, 255) = left('some_long_value', 255);
出力に Index Scan または Index Only Scan が表示されれば成功です。Seq Scan が表示された場合はプランナーがインデックスを無視しています — クエリの式が CREATE INDEX で使用した式と一致しているか再確認してください。
判断基準のクイックガイド
- 値全体に一意制約が必要？ → MD5 関数インデックス + アプリケーションでの二重チェック
- 高速な等値検索が必要で一意性は不要？ → HASH インデックス
- LIKE / あいまい検索が必要？ → GIN + pg_trgm
- プレフィックスでの ORDER BY や範囲クエリが必要？ →
left(col, N)の B-tree インデックス - カラム値が誤って長すぎる？ → カラム型に制約を設ける