index row size exceeds btree maximum
Why Postgres refuses to index long text values with "index row size exceeds btree version 4 maximum 2704", and the hash, expression and trigram indexes to use instead.
What you see
An insert or update fails, or create index fails on existing data:
ERROR: index row size 3416 exceeds btree version 4 maximum 2704 for index "notes_body_key"
DETAIL: Index row references tuple (812,4) in relation "notes".
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.
Through the Data API the same message comes back in the message field of the error, usually from a .insert() or .update() call.
Why it happens
A B-tree index (the default kind, and the one behind every unique and primary key constraint) stores a copy of the indexed value in each index entry. An entry has to fit in about a third of an 8 KB page, so roughly 2.7 KB after compression. Short values are fine. A long text column, a big jsonb document, or several wide columns together can go past that limit, and the first row that does is rejected.
Often the index was added without much thought, for example unique on a body or url column that "should" be short.
How to fix it
Pick the option that matches what the index was for.
1. You need equality lookups (where body = ...). Use a hash index. It stores a 4-byte hash, not the value:
drop index if exists notes_body_idx;
create index notes_body_hash_idx on public.notes using hash (body);
2. You need uniqueness. Hash indexes can't be unique, but an exclusion constraint on a hash index enforces the same rule:
alter table public.notes drop constraint if exists notes_body_key;
alter table public.notes
add constraint notes_body_unique exclude using hash (body with =);
Or index a digest of the value. Queries must use the same expression to benefit:
create unique index notes_body_md5_key on public.notes ((md5(body)));
select id from public.notes where md5(body) = md5('Buy oat milk');
3. You need search (like, ilike, words). A B-tree on the full value never helped there anyway. Use full-text search or trigrams:
create extension if not exists pg_trgm with schema extensions;
create index notes_body_trgm_idx on public.notes
using gin (body extensions.gin_trgm_ops);
4. You only need sorting or prefix matches. Index a prefix instead of the whole value:
create index notes_body_prefix_idx on public.notes ((left(body, 200)));
After any of these, find and drop the old index. A long-running create index blocks writes to the table; on a busy table use create index concurrently (outside a transaction).
On SupaHost
Nothing here differs from standard Postgres. Run the SQL in Studio's SQL editor (Dashboard → your project → Database tab → Open Studio), or through your migrations so every environment gets the same index.