Re:Why do database columns have a character length of 191?
by jzelinskie on Monday May 17, 2021 @04:00PM
(#27187718)
It's true that Postgres does this. But the problem I was highlighting was the developer-error of lacking to explicitly declare an encoding. It's only really related to the OP link insofar-as when you fail to do so on MySQL, you'll receive error messages that inform you of the dreaded 767-byte index.
Re:Why do database columns have a character length of 191?
by masklinn on Monday May 17, 2021 @04:11PM
(#27187856)
You can actually have a similar error in postgres, it's just that the index limit is at 2730 bytes, and it's unlikely you'll have indexed a column which can get content more than 2.5k.
It does happen though, as I've discovered (repeatedly, because I'm a slow learner).
That's… not actually really relevant to the issue. And you can configure postgres to use a non-utf8 encoding as its default.
Postgres avoids the issue by automatically moving large value outside of the row itself ("TOAST" storage). That's also why you can have an unbounded vachar in postgres, and there's no real difference between varchar, varchar(n) and text. And char(n) is slower than the others: char(n) will always pad to (n), while varchar and text will only use whatever they need (+ some overhead).
TOAST works in terms of bytes, so your UTF8 might move to the next storage location earlier than if you'd use a fixed-size single-byte encoding. It just doesn't matter that much (it'll just be a bit slower) unless you actually reach the limit of TOAST storage itself (1GB per value).
And Postgres has its own issue with fixed-size storage e.g. identifiers (names of tables, columns, indexes, constraints, etc…) can't be more than 63 bytes, they'll be silently and implicitly truncated to that.