NEW ARTICLE
docker『Permission Denied』の原因と対処法
  1. HOME
  2. その他
  3. PostgreSQL『ERROR: index row size exceeds maximum』の原因と対処

PostgreSQL『ERROR: index row size exceeds maximum』の原因と対処

  • 作成日 2025.09.24
  • 更新日 2025.10.06
  • その他
PostgreSQL『ERROR: index row size exceeds maximum』の原因と対処

B-treeインデックスに格納される1レコード（インデックスタプル）が上限サイズを超えたときに出る。8KBブロックの環境では1タプルあたり約2.7KB前後が実質上限で、巨大なtext/varcharや多列インデックス、INCLUDE列の盛り過ぎ、jsonbのB-tree化などで発生しやすい。即効回避（関数/部分/代替インデックス）、恒久対策（設計見直し・プーリング戦略ではなく“キーを小さく”する戦略）、用途別ベストプラクティスを整理した。

目次

エラーメッセージの読み方と概要

ERROR:  index row size X exceeds maximum Y for index "idx_xxx"
HINT:  Values larger than Y bytes are not stored directly in index tuples.

・「X > Y」になった行が原因
・Yはビルド時ブロックサイズ等で変わるが、デフォルト8KBでは約2700バイト前後

発生条件の早見表（まずここを疑う）

□ text/varchar/jsonb など可変長列を含む多列B-tree
□ INCLUDE で大きい列を“カバリング”しすぎ
□ 並び替え用に text をそのままB-tree化（ICUコレーションの影響で巨大化）
□ jsonb/bytea のB-treeを安易に作成（格納値が大きい）
□ アプリ由来で列が肥大化（長文、HTML、Base64等）

まず現状確認：サイズと犯人列を特定

-- インデックス定義
SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE tablename = 'your_table';

-- 典型：可変長列の実サイズ分布（text など）
SELECT
  max(octet_length(col)) AS max_bytes,
  percentile_disc(0.99) WITHIN GROUP (ORDER BY octet_length(col)) AS p99_bytes
FROM your_table;

-- 多列キーの概算（単純加算。実際はオーバヘッドあり）
SELECT
  max(octet_length(col_a) + octet_length(col_b) + octet_length(col_c)) AS max_combined
FROM your_table;

・text/jsonb はテーブルではTOASTされても、B-treeインデックスには“生の値”が入るためサイズが直撃する

即効回避1：関数インデックスでキーを縮める（等値/重複排除）

-- 等値検索しか要らない → ハッシュ化（衝突回避のため二段チェック）
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE INDEX idx_items_body_sha256 ON items (encode(digest(body, 'sha256'), 'hex'));

-- 使い方（衝突安全の二段絞り込み）
SELECT *
FROM items
WHERE encode(digest(body, 'sha256'), 'hex') = encode(digest($1, 'sha256'), 'hex')
  AND body = $1;

・巨大文字列の“そのままB-tree”をやめ、固定長のハッシュに置き換える
・用途が等値のみなら効果が高い（範囲/ORDER BYは不可）

即効回避2：LIKE/ILIKE/部分一致なら GIN + pg_trgm

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 部分一致/前方一致/類似検索向け（巨大textにも強い）
CREATE INDEX idx_items_body_trgm ON items USING GIN (body gin_trgm_ops);

-- 使い方
SELECT * FROM items WHERE body ILIKE '%postgres%';
SELECT * FROM items WHERE body LIKE 'error:%';

・大きな文字列の全文・部分検索でB-treeを諦め、トークン（3-gram）ベースに切替

即効回避3：プレフィックスだけを索引（LEFT/サブ文字列）

-- 先頭 N 文字で選別できる要件なら
CREATE INDEX idx_items_body_prefix ON items ((left(body, 256)));

-- 使い方（必ず同じ式に揃える）
SELECT * FROM items WHERE left(body, 256) = left($1, 256);

・“長文同士の完全一致”でなく“十分短い先頭キーで選別→本体照合”の二段構え

即効回避4：部分インデックスで“入る行だけ”に限定

-- 巨大値はまれ → そこだけ全表走査を許容
CREATE INDEX idx_items_body_small ON items (body)
WHERE octet_length(body) <= 2000;

・大多数の行をインデックスでカバーし、レアケースは別戦略に逃がす

設計見直し1：INCLUDE/多列インデックスの痩身化

-- NG（大きい列をINCLUDEに大量添付）
CREATE INDEX idx_orders ON orders (customer_id, created_at) INCLUDE (comment, memo);

-- 代替（INCLUDEを外し、必要なら別途point lookup）
CREATE INDEX idx_orders_core ON orders (customer_id, created_at);
-- 読み取りパターン次第で「別テーブルに長文を分離」も有効

・INCLUDEは“葉”だけとはいえ、1エントリのサイズ制約に掛かる
・「読み取り1往復減」のために上限にぶつかるなら本末転倒

設計見直し2：jsonb/配列はB-treeよりGIN/GiSTを選ぶ

-- jsonb 例：存在/一致検索用
CREATE INDEX idx_docs_json ON docs USING GIN (payload jsonb_path_ops);

-- 配列の包含/要素検索
CREATE INDEX idx_tags_gin ON posts USING GIN (tags);

・jsonb/配列のB-treeはキーが肥大化しやすい
・演算子とインデックス方式の組み合わせを正しく選ぶ

設計見直し3：コレーション/並び替え要件の整理

-- 並び替えのみ目的のtextインデックスで、ロケールの影響を避けたいなら
CREATE INDEX idx_items_body_c ON items ((body COLLATE "C"));

・ICUコレーションでソートキーが重いと、abbrev無効化等でコスト増
・“辞書順”ではなく“バイト順”でよい要件なら COLLATE “C” で軽量化（※サイズ上限そのものを増やすわけではない）

根本対策：巨大列をキーにしないスキーマへ

-- 例：長文を別テーブルに分離し、参照は短いIDに寄せる
CREATE TABLE articles(
  id bigserial PRIMARY KEY,
  title text NOT NULL,
  body_id bigint UNIQUE  -- 本文ID（短いキーで索引）
);

CREATE TABLE article_bodies(
  id bigint PRIMARY KEY,
  body text NOT NULL
);

CREATE INDEX idx_articles_title ON articles (title);  -- 短い列だけ索引

・“索引にしたい列”は短く/固定長を基本に
・長文・バイナリは別テーブル化 or ハッシュで代理

再現→解消の通し例（NG→OK）

-- 準備：巨大文字列を持つテーブル
DROP TABLE IF EXISTS t;
CREATE TABLE t(id bigserial PRIMARY KEY, body text NOT NULL);

-- 10KB超のダミーを投入
INSERT INTO t(body)
SELECT string_agg('x', '') FROM generate_series(1, 12000);

-- NG：そのままB-tree（多くの環境でエラー）
CREATE INDEX idx_t_body ON t(body);
-- ERROR: index row size exceeds maximum ...

-- 解消1：トライグラムGINで部分一致検索を実現
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_t_body_trgm ON t USING GIN (body gin_trgm_ops);
SELECT * FROM t WHERE body LIKE '%xxx%';

-- 解消2：等値専用ならハッシュ＋二段照合
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE INDEX idx_t_body_sha256 ON t (encode(digest(body, 'sha256'), 'hex'));
SELECT * FROM t
WHERE encode(digest(body, 'sha256'), 'hex') = encode(digest($1, 'sha256'), 'hex')
  AND body = $1;

-- 解消3：プレフィックスで索引
CREATE INDEX idx_t_body_prefix ON t ((left(body, 256)));
SELECT * FROM t WHERE left(body, 256) = left($1, 256) AND body = $1;

既存インデックスの棚卸しと危険予兆の検出

-- “危ない候補”の行（大きいtext）を抽出
SELECT id, octet_length(body) AS len
FROM t
ORDER BY len DESC
LIMIT 20;

-- 多列インデックスの場合は各列サイズを把握し、合算が2KB超なら要注意
SELECT
  max(octet_length(a)) AS a_max,
  max(octet_length(b)) AS b_max,
  max(octet_length(c)) AS c_max
FROM your_table;

・2KB級×複数列はほぼアウト
・INCLUDE列も加算対象という前提で見積もる

アンチパターンと代替

× max_connectionsを上げる/メモリを盛る → 解決しない（サイズ上限は構造上の制約）
× ALTER TABLE ... SET STORAGE をいじる → インデックスには無効
× B-treeを“とりあえず複合化” → 1行のタプルサイズが超過しやすい

○ 要件に合わせて方式を選ぶ：
  ・等値＝ハッシュ索引（md5/sha256）＋二段照合
  ・部分一致/全文＝GIN(pg_trgm/tsvector)
  ・JSON/配列＝GIN/GiST
  ・並び替え＝短い列に置換 or COLLATE "C" の検討

チェックリスト（上から順に適用）

□ 犯人列の octet_length 分布を確認（p99/最大）
□ B-treeに巨大可変長列を含めていないか（INCLUDE含む）
□ 目的（等値/部分一致/並び替え/包含）に合う方式を選択
□ 関数/部分/ハッシュインデックスでキーを縮める
□ 部分インデックスや“二段照合”で安全に成立させる
□ jsonb/配列はG*系（GIN/GiST）を基本とする
□ スキーマで長文を分離し、短いID/ハッシュをキー化

まとめ：上限を“増やす”のではなく“当て方を変える”

・B-treeの1タプル上限は構造的制約 → 設計で回避する
・巨大値はハッシュ/トライグラム/分離テーブルで扱う
・INCLUDE/多列は“痩身化”を徹底し、必要十分なキーだけに絞る
・要件ごとにインデックス方式を切替え、再現性のある運用に寄せる