Fix PostgreSQL 'index row size exceeds maximum 2712' Error on Long Text Columns

TL;DR

PostgreSQL B-tree indexes cap each entry at roughly 2712 bytes — that's 1/3 of an 8 KB page. Store anything longer in a text or varchar column and CREATE INDEX dies immediately:

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.

Your fastest exits:

• Index only the first N characters: CREATE INDEX ON t (left(col, 255));
• Index the MD5 hash for exact-match lookups: CREATE INDEX ON t (md5(col));
• Switch to a HASH index if you only need = comparisons.
• Use a GIN index with pg_trgm for LIKE / similarity searches.

Root Cause

Every PostgreSQL index page is 8 KB. After accounting for page overhead, a single B-tree entry is limited to floor((8192 − overhead) / 3) = 2712 bytes. That limit is compiled into PostgreSQL's page size — you cannot change it at runtime or in postgresql.conf.

A text column stores large values in the heap via TOAST, sometimes reaching several MB per row. Fine. But the B-tree index entry for that row must fit on one page in one shot. One row with a column value whose UTF-8 byte length exceeds ~2704 bytes is enough to abort the entire CREATE INDEX.

This hits most often in three scenarios:

• Adding a UNIQUE constraint to an existing text column that already holds long rows.
• Running CREATE INDEX on a URL, JSON blob, or free-text field.
• Migrating from MySQL, where prefix index lengths are handled differently at the DDL level.

Fix Approaches

Option 1 — Functional index on a prefix (most common fix)

Most of the time, uniqueness or equality checks only need the first few hundred characters. Index a prefix instead of the full value:

-- Index first 255 characters (adjust to your needs)
CREATE UNIQUE INDEX table_column_prefix_idx
  ON my_table (left(column_name, 255));

There's a catch: your queries must use the same expression for PostgreSQL to use the index.

-- This will use the index
SELECT * FROM my_table WHERE left(column_name, 255) = left($1, 255);

-- This will NOT use the index (full column)
SELECT * FROM my_table WHERE column_name = $1;

For non-unique indexes meant to speed up lookups, the prefix approach works well — as long as duplicate prefixes are rare in your data.

Option 2 — MD5 hash index (exact equality only)

Need to match or deduplicate full values? Hash the column. MD5 always produces a 32-character hex string, well under the 2712-byte limit:

CREATE UNIQUE INDEX table_column_md5_idx
  ON my_table (md5(column_name));

Query pattern:

SELECT * FROM my_table WHERE md5(column_name) = md5($1);

One caveat: MD5 has a theoretical (astronomically rare) collision risk. For security-sensitive deduplication — say, untrusted user input — add an application-level equality check after the index lookup confirms a candidate row.

Option 3 — HASH index type

PostgreSQL's native HASH index type has no size limit on the indexed value. It supports only = comparisons, but it's been crash-safe since PostgreSQL 10.

CREATE INDEX table_column_hash_idx
  ON my_table USING hash (column_name);

Reach for this when:

• Every query uses = — no LIKE, no ordering, no range scans.
• You don't need the index to enforce uniqueness.

Option 4 — GIN index with pg_trgm (LIKE / similarity searches)

Doing LIKE '%keyword%' searches on a long-text column? The trigram extension is built for this:

-- Enable extension once per database
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX table_column_trgm_idx
  ON my_table USING gin (column_name gin_trgm_ops);

This index covers all three of these query patterns:

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';

Option 5 — Shorten the column type

Sometimes long values are a data quality bug, not an intentional design. If that's the case, fix the column itself:

-- Catch any existing violations before changing the type
ALTER TABLE my_table
  ADD CONSTRAINT column_name_max_len CHECK (char_length(column_name)  2704) AS over_limit_rows
FROM my_table;

If over_limit_rows comes back as 0, the issue is likely multi-column index overhead or an encoding mismatch — not the raw column values. If it's greater than 0, you need one of the options above.

Verification

After applying your fix, make sure the index was created cleanly:

-- Check the index exists and see its definition
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'my_table';

-- Confirm no INVALID indexes (a partially-built index shows up here)
SELECT relname, indisvalid
FROM pg_index
JOIN pg_class ON pg_class.oid = pg_index.indexrelid
WHERE indrelid = 'my_table'::regclass;

Then run EXPLAIN on a real query to confirm the planner actually uses it:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM my_table WHERE left(column_name, 255) = left('some_long_value', 255);

Index Scan or Index Only Scan in the output means you're good. Seq Scan means the planner ignored the index — double-check that your query expression matches the one used in CREATE INDEX.

Quick Decision Guide

• Need unique constraint on full value? → MD5 functional index + application double-check
• Need fast equality lookups, no uniqueness? → HASH index
• Need LIKE / fuzzy search? → GIN + pg_trgm
• Need ordering or range queries on prefix? → left(col, N) B-tree index
• Column values are genuinely too long by mistake? → Constrain the column type