🚨 PostgreSQL Error Guide: 'index row size exceeds btree maximum' — Fix Oversized B-tree Keys

Overview

PostgreSQL raises this error when you try to insert or index a value whose key is too large to fit in a single B-tree index page entry:

ERROR:  index row size 2720 exceeds btree version 4 maximum 2704 for index "orders_notes_idx"
DETAIL:  Index row references tuple (42,7) in relation "orders".
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.

The SQLSTATE is 54000 (program_limit_exceeded). A B-tree index entry must fit several tuples per 8KB page, so PostgreSQL caps any single index key at roughly 2704 bytes (about one third of a page). The value itself is stored fine in the table — the error is specifically about indexing it. This can fire at CREATE INDEX time (an existing row is too big) or later at INSERT/UPDATE time (a new long value hits an index that was created when all existing values happened to be short enough).

Symptoms

• CREATE INDEX fails immediately with index row size N exceeds btree version 4 maximum 2704.
• INSERT or UPDATE on a table that previously worked suddenly fails with the same message when a long value arrives.
• The offending column is a text, varchar, bytea, jsonb, or a multi-column key whose combined width is large.
• The DETAIL line names the exact tuple, and the HINT suggests a hash function index or full-text indexing.
• Only some rows fail — the ones with unusually long values — while short-value rows insert fine.

Common Root Causes

• Indexing a long free-text column directly. A B-tree on a notes, description, url, or payload column that can exceed ~2704 bytes.
• Unique or composite keys that concatenate wide columns. A multi-column unique index whose columns together exceed the limit.
• Indexing large jsonb or bytea values. Storing documents or blobs and then B-tree indexing the whole value.
• A UNIQUE constraint used for deduplication on long strings. Trying to enforce uniqueness on full URLs, file paths, or large text.
• Data that grew over time. The index was created when values were short; a later row with a long value trips the limit.
• Wrong index type for the goal. Using a B-tree for substring/containment search where GIN + full-text or trigram indexing is the correct tool.

Diagnostic Workflow

First, confirm the exact index and the offending value’s length. The error names the index and tuple; measure the column widths of the longest rows (read-only):

-- Longest values in the column the index covers
SELECT ctid, length(notes) AS len
FROM orders
ORDER BY length(notes) DESC
LIMIT 5;

Inspect the index definition and the columns involved:

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';

For a composite key, sum the byte widths to see which combination overflows:

SELECT max(octet_length(col_a) + octet_length(col_b)) AS max_key_bytes
FROM orders;

Check whether the intent is exact-match/uniqueness (a hash of the value works) or search/containment (needs GIN):

-- How the column is actually queried drives the right index type:
-- exact match / uniqueness  -> hash the value, or use a hash index
-- substring / containment   -> GIN with pg_trgm or full-text (tsvector)
SELECT count(*) FILTER (WHERE notes = 'exact string') AS exact_lookups
FROM orders;  -- illustrative: match against your real query patterns

If the error appeared at insert time, find which incoming values breach the limit before they hit the index:

SELECT ctid, length(notes)
FROM orders
WHERE octet_length(notes) > 2704;

Example Root Cause Analysis

A team added a UNIQUE index on a source_url column to stop duplicate crawl records. It worked in testing and for weeks in production, then inserts started failing with index row size 2860 exceeds btree version 4 maximum 2704 for index "pages_source_url_key". The URLs were normally short, but a batch of pages carried very long query strings and tracking parameters, pushing a few values past 2704 bytes.

The column data was fine — the value stored without complaint. The problem was enforcing uniqueness with a B-tree on the full string. Because the real requirement was “no two rows with the same URL,” a hash of the value preserves uniqueness while keeping the index key tiny. The team replaced the direct unique index with a unique index on a hash expression:

-- Enforce uniqueness on the value without indexing the whole string
CREATE UNIQUE INDEX pages_source_url_uniq
ON pages (md5(source_url));

Queries that looked up by exact URL were rewritten to match md5(source_url) = md5($1) AND source_url = $1 so the index is used and the hash collision is disambiguated by the equality on the full text. After the change, the long-URL batch inserted cleanly. The root cause was choosing a B-tree over the entire long value when only equality/uniqueness was actually required.

Prevention Best Practices

• Don’t B-tree index long free-text columns directly. If you only need equality or uniqueness, index a hash: CREATE INDEX ... (md5(col)) (or a hash index for equality lookups).
• Use the right index type for the query. For substring, LIKE, or containment search, use GIN with pg_trgm (trigram) or full-text tsvector indexing — that is what the HINT means.
• Cap the indexed prefix. If a prefix is sufficient, index left(col, 100) or a generated shorter column instead of the whole value.
• Validate composite key width. When building multi-column unique indexes on text, check the worst-case combined byte length stays well under 2704.
• Constrain input length where the domain allows. A varchar(n) or a CHECK (length(col) <= n) on genuinely bounded fields prevents surprise oversized rows.
• Test with realistic maximum-length data. The failure only appears with long values, so seed tests with worst-case sizes, not just typical rows.

Quick Command Reference

-- Find the longest values in the indexed column
SELECT ctid, length(notes) FROM orders ORDER BY length(notes) DESC LIMIT 5;

-- Rows that exceed the B-tree key limit
SELECT ctid, octet_length(notes) FROM orders WHERE octet_length(notes) > 2704;

-- Inspect index definitions on the table
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';

-- Uniqueness/equality without indexing the whole value
CREATE UNIQUE INDEX orders_notes_hash ON orders (md5(notes));

-- Substring / containment search instead of a B-tree
CREATE INDEX orders_notes_trgm ON orders USING gin (notes gin_trgm_ops);  -- needs pg_trgm

Conclusion

index row size N exceeds btree version 4 maximum 2704 means the value is fine in the table but too large to fit in a B-tree index key, which is capped at about one third of an 8KB page. The fix is never to index the whole long value with a B-tree: hash it (md5(col)) when you only need equality or uniqueness, or use GIN with trigrams or full-text tsvector when you need substring and containment search. Diagnose it by measuring the longest values and matching the index type to how the column is actually queried, then test with worst-case-length data so a stray long row never breaks inserts again. The free incident assistant can turn this error into a concrete “hash vs GIN vs prefix” recommendation for your column.