Everyone's answer is "just lowercase everything".
I'll respond just a bit:
1. You don't always have control over all the queries that have been written against your database.
2. You would probably lose the ability to use ORMs without a moderate amount of customization.
3. If you're migrating from a different database, you may have checksums on your data that would all need to be recalculated if you change case on everything stored.
4. Doing runtime lowercase() on everything adds a bit of overhead, doesn't it?
citext on postgresql seems a decent option - the citext docs even mention drawbacks of some of the other recommended options.
And doing the runtime lower() on everything will generally not be slower than citext. If you look at the source for the citext comparison (https://github.com/postgres/postgres/blob/aa9eac45ea868e6dda...) you'll see it is internally converting the values to lowercase and comparing them. All it saves you is the overhead of a sql function invocation, and you'd have to do a lot of comparisons to make that difference measurable. But if you're doing a lot of those comparisons, unless you're just running the calculation on the same couple values over and over, the memory and disk latency will dominate performance, not the minimal overhead of the sql function invocation.
I agree you should probably use citext if you need case-insensitive unique or primary key values, but be aware of the drawbacks. https://www.postgresql.org/docs/current/static/citext.html
Maybe MySQL has special sauce for doing this comparison without lowercasing the query string? But there must be some overhead relative to exact search?