We definitely store data case-sensitively, we just never want to search on it case-sensitively. That's what citext gives us. Many databases perform this way as a default. Postgres does not, but it offers alternatives. The OP is coming from MySQL which, if I remember correctly, treated non-binary-like text containers as case-insensitive in searches. That's why I mentioned the Postgres behavior, it's a gotcha if you're assuming something else will happen.
More to the point, users never want case-sensitive searches, it's just confusing for them.
There are places where we've got data where byte-level/code page differences are significant. But a lot of that is binary-like data. These are rare, and I'm willing to do a bit of extra work for them. I can't even think of such a case off the top of my head.
UUIDs as a type are an interesting case in Postgres. They're stored as a large numeric for efficiency (good!), but are presented by default in the 36-byte format with the dashes. However, you can also search using the dashes 32-character format....and it all works. Case-insensitively. Postgres converses 36/32 char strings of any case combination back into the relevant number and then searches. Anything else would be pointlessly hard to deal with.
There are also cases where case-sensitivity is not optional. For example, we save and generate JSON (like everyone else) for various tasks. JSON element names are case-sensitive. Not our call, just the way it is. Personally, I think that case-sensitive language element names are one of the stupidest design choices in history...but no one asked me. There are solid arguments in favor of the idea (Dijkstra himself argued in their favor), and it's an unchangeable fact of life. So in those cases, yeah, case-sensitivity matters. Namely, if the data itself is case-sensitive. The truth is, I rarely have a reason to use a 0NF packed field type like JSON....so the issue doesn't come up in our Postgres searches. But if I did plan to store JSON, say API response logs, I'd want those searches to be case-sensitive and would use JSONB and the necessary operators.
