Every time I see a job post mentioning mysql I realize they just haven't discovered postgres, or they have some really gross problem. :/
There's a whole lot of documentation and an ecosystem of operations tooling (think Percona) and MySQL experts are much more numerous.
MariaDB's Galera cluster is really solid and had years of production use now.
Postgres is catching up, but for now, MySQL/MariaDB win in that regard.
Logical replication is frequently a requirement which reduces your options to not Postgres until now.
I tend to think the HN groupthink is strong on this subject, and 90%+ wouldn't ever see an effect beyond placebo from switching. MySQL (or MariaDB, which I actually use these days) has also changed drastically since those 200x-years where most of today's folk wisdom originates.
As much as postgres is overall better than MySQL in so many ways, it's still ridiculously difficult to set things up such that
SELECT id FROM users WHERE email='foo@example.com'
SELECT id FROM users WHERE email='Foo@example.com'https://www.postgresql.org/docs/9.6/static/functions-matchin...
Can't you just do this in PostgreSQL?
SELECT id FROM users WHERE email = lower('Foo@example.com') create table user_email (
email text not null
);
-- create a index on the lowercase form
-- of the email
create unique index user_email_case_idx
on user_email (lower(email));
-- select using the index, with the lowercase form.
select 1
from user_email
where lower(email)=lower('Foo@foo.com');SELECT 1 FROM user_email WHERE email ILIKE 'Foo@Foo.coM';
WHERE lower(email) = lower('foo@example.com')
I'm not sure ILIKE can hit an index in your example.
solution immediately came up at SO:
SELECT id FROM users WHERE LOWER(email)=LOWER('Foo@example.com')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?
1. first install the following (be sure to replace [your schema]:
CREATE EXTENSION pg_trgm with schema extension;
CREATE EXTENSION unaccent with schema extension;
CREATE OR REPLACE FUNCTION insensitive_query(text) RETURNS text AS $func$ SELECT lower([your schema].unaccent('[your schema].unaccent', $1)) $func$ LANGUAGE sql IMMUTABLE;
2. then in your query you can use:
where insensitive_query(my_table.name) LIKE insensitive_query('Bob')
It's discouraged by RFC5321 whilst also being defined by it.