Its a run-time error too so the index may get created no problem then later someone tries to insert a >900 bytes value and it throws.
Looking at the doc[0], I see this:
> PostgreSQL uses a fixed page size (commonly 8 kB), and does not allow tuples to span multiple pages. Therefore, it is not possible to store very large field values directly. To overcome this limitation, large field values are compressed and/or broken up into multiple physical rows. This happens transparently to the user, with only small impact on most of the backend code.
So, yeah, large values are stored differently and there's an impact on performance.
Sorry, a blanket statement like "you should use varchar with no length limit" seems extremely ill advised to me. Like, sure, you don't want to use varchar(30) for last names. But varchar(255) is probably going to do you just fine.
[0]: https://www.postgresql.org/docs/current/storage-toast.html
That said, I still don't get why they didn't just change the behavior for TEXT/NTEXT vs adding (MAX) options.
edit: I am mistaken, when setting an index, the index max is now the insert limit (from response below).
(off the top of my head, the most likely use case for 2+kb text fields would be things you would be using pg's full text index on anyway, not the usual btree type indexes as you'd want to speed up a join or whatever.)
I am not that familiar with PG full text index types, but in SQL server the full text index doesn't really work well with exact/begins with searches like the b-tree so your kinda stuck in that situation.
With SQL server I like to stick with varchar(255) as it gives head room for unicode indexing and at one point there where some internal optimizations for less <=255 due to the legacy max being 255, not sure anymore.
Beyond 255 its much less likely to need a btree and becomes more of a full text issue.