ERROR: index row size exceeds btree version 4 maximum

A single value is too large to fit in a B-tree entry. The tree needs several entries on every page to be a tree at all, so the ceiling is roughly a third of a page, which on the default page size is a little over two and a half kilobytes. The column itself has no such limit and will store values up to a gigabyte quite happily. The rejection comes from the index, not from the type.

Why the column and the index disagree

Large values in a table are moved out of line automatically, compressed and split into pieces stored elsewhere, with only a pointer left in the row. That is the out-of-line storage mechanism, and it is why a text column can hold a whole document without the page overflowing.

An index entry cannot use that path. The key has to be present and comparable in the index itself, because the whole structure depends on ordering keys against each other. So the index is subject to a limit the table is not, and the gap between them is where this error lives.

The timing is what makes it dangerous. The check happens when a row is inserted or updated, not when the index is created, so a unique constraint on a text column can pass every test, run for a year on ordinary values, and then reject a write the day somebody pastes something long into a form. It arrives as an application error in production with no preceding change.

Indexing long values anyway

If the queries are equality tests, index a digest of the value rather than the value. The index becomes small and fixed width, and equality still works by comparing digests and then the original. Uniqueness enforced this way is uniqueness of the digest, which is a different promise; for most purposes it is close enough, and it should be a deliberate decision rather than an accident.

If the queries are looking inside the text, an index built for text search is the right tool and the length limit does not apply to it in the same way, because it indexes the extracted terms rather than the whole value.

If the value is long only because several columns were combined into one index, splitting the index is usually both smaller and more useful, since the leading column is what most queries filter on anyway. Unused and missing indexes covers deciding which of them earns its place.

A last practical note: adding the replacement index on a live table should be done concurrently, with the failure behaviour described in is CREATE INDEX CONCURRENTLY safe.