PostgreSQL "index row size exceeds maximum" — Why Long Text Cannot Be Directly Indexed?

Root causes of PostgreSQL btree 1/3 page limit (2704 bytes), byte vs character calculations, expression indexes, MD5 hash indexing, and GIN trigram alternatives.

Reviewed by:Principal Database Reliability Architect

Tested Versions: PostgreSQL 18.6 & 17.11 (Alpine Linux / Docker 29.7) | Tarihsel: PostgreSQL 14, 15, 16

Operational Verification & Reliability Metadata

Last Technical Review: 29 Ekim 2026

Tested VersionsPostgreSQL 18.6 & 17.11 (Alpine Linux / Docker 29.7) | Tarihsel: PostgreSQL 14.10, 15.6, 16.4

Required PrivilegesCREATE INDEX or ALTER TABLE privileges on target relation

Risk Assessment

MediumSelecting improper index architecture degrades query latency or incurs high write amplification

Expected OutputZero-error creation of MD5, expression prefix, or GIN trigram index after octet_length() verification

Rollback Procedure:DROP INDEX CONCURRENTLY to remove experimental index and restore original schema constraints

PostgreSQL Docs: B-Tree Implementation Limits

First 60 Seconds Emergency Protocol:

The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.

▸Symptoms

plaintext

ERROR:  index row size 3112 exceeds btree version 4 maximum 2704 for index "idx_makale_ozet"
DETAIL:  Index row references tuple (0,14) in relation "makale".
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.

This error surfaces during CREATE INDEX or subsequent INSERT/UPDATE operations. The root cause is architectural: in PostgreSQL B-Trees, an index tuple cannot exceed 1/3 of an 8 KB buffer page (~2,704 bytes net of page headers). This invariant guarantees that every internal B-Tree page accommodates at least three index tuples to preserve balance.

▸Diagnostic Query

Identify tuples exceeding the boundary:

sql

SELECT id, length(ozet) AS char_length, octet_length(ozet) AS byte_length
FROM makale
ORDER BY octet_length(ozet) DESC
LIMIT 10;

WARNING

Bytes vs Characters: The limit is in bytes, not character count. UTF-8 multi-byte characters and emojis consume up to 4 bytes each. A varchar(2000) column can expand to 4,000 bytes.

▸Resolution Patterns

1. Exact Equality Lookup → MD5 / SHA-256 Hash Index

sql

CREATE INDEX idx_makale_ozet_md5 ON makale (md5(ozet));
SELECT * FROM makale WHERE md5(ozet) = md5('query text...');

2. Prefix Matching → Functional Expression Index

sql

CREATE INDEX idx_makale_ozet_prefix ON makale (left(ozet, 200));
SELECT * FROM makale WHERE left(ozet, 200) = left('query text...', 200);

3. Substring & ILIKE Searches → GIN Trigram

sql

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_makale_ozet_trgm ON makale USING gin (ozet gin_trgm_ops);

4. Natural Language Search → tsvector

sql

ALTER TABLE makale ADD COLUMN arama tsvector
  GENERATED ALWAYS AS (to_tsvector('english', coalesce(baslik,'') || ' ' || coalesce(ozet,''))) STORED;
CREATE INDEX idx_makale_arama ON makale USING gin (arama);

▸Index Key Limit Comparison: PostgreSQL vs MySQL

Criteria	PostgreSQL (B-Tree)	MySQL InnoDB (DYNAMIC)	MySQL InnoDB (COMPACT)
Max Key Size	2,704 bytes	3,072 bytes	767 bytes
utf8mb4 Chars	~676 chars	768 chars	191 chars
Companion Guide	This Guide (H-21)	MySQL Error 1071 (H-22)	MySQL Error 1071 (H-22)