If I had to pick the single difference that generates the most post-migration bug tickets, it would not be replication, partitioning, or the optimizer. It would be case. MySQL and PostgreSQL disagree about case in two independent places, identifiers and data, and both disagreements are invisible until a query returns the wrong rows or a unique constraint fires on data that used to be "the same."
Having operated both engines, I do not think either choice is wrong. MySQL optimizes for forgiveness; PostgreSQL optimizes for predictability. But you have to know exactly where the forgiveness was, because your application has been leaning on it for years without anyone deciding to.
How MySQL decides what matches
In MySQL, string comparison behavior belongs to the collation, and the defaults are case-insensitive: the ci in utf8mb4_0900_ai_ci means exactly that (and ai means accent-insensitive). This applies everywhere the collation applies: WHERE equality, GROUP BY, DISTINCT, ORDER BY, and, critically, unique indexes. On a default MySQL 8 setup, 'Alice' and 'alice' are equal, and a unique index on username will refuse to store both.
That last property is load-bearing. Applications on MySQL get case-insensitive uniqueness for free, so they never normalize. The database was the normalization layer, and nobody wrote that down.
Collation is also configurable at four levels, server, database, table, and column, so a long-lived MySQL schema often mixes behaviors: a bin-collated token column here, a latin1 legacy table there, and defaults everywhere else. Part of the migration audit is simply discovering which comparisons in your own schema were case-sensitive all along, because those must not become case-insensitive by accident either. The information_schema COLUMNS view tells you per column; read it before you write the conversion mapping rather than assuming the defaults apply uniformly.
lower_case_table_names and identifier case
Identifiers are a separate mechanism with its own knob: lower_case_table_names. At 0 (the Linux default) table names are stored and compared case-sensitively, following the filesystem. At 1 (the Windows default) names are stored lowercase and compared insensitively. At 2 (macOS) they are stored as written but compared lowercased. In MySQL 8 this setting is fixed at data directory initialization and cannot be changed afterward.
The practical consequence: a codebase developed on macOS or Windows can freely mix SELECT * FROM Users and FROM users, and it all works until the schema lands somewhere with different settings. Many teams discover their real identifier casing for the first time during a migration dump, which is not the ideal moment.
What PostgreSQL does instead
PostgreSQL's identifier rule is simple and uniform on every platform: unquoted identifiers fold to lowercase. Users, USERS, and users all mean users. But a quoted identifier preserves case exactly, and once a table is created as "Users", it must be quoted as "Users" forever. There is no configuration knob; the escape hatch is quoting, and the best practice is to never use it. Lowercase, snake_case, unquoted, always.
For data, PostgreSQL's standard collations are deterministic and case-sensitive: 'Alice' and 'alice' are different values, everywhere, including in unique indexes. PostgreSQL 12+ supports nondeterministic ICU collations that can compare case-insensitively, but they come with real limitations (pattern matching operators like LIKE are restricted, and text-search interactions get subtle), so for most application schemas they are not the first tool I reach for. The idiomatic PostgreSQL answers are lower(), ILIKE, and citext, covered below.
The classic migration bugs
These are the tickets, in roughly the order they arrive.
- Login failures: the user registered as Alice@Example.com, logs in as alice@example.com, and the equality lookup that matched on MySQL matches nothing on PostgreSQL.
- Duplicate accounts: with case-insensitive uniqueness gone, the same person signs up twice with different casing, and downstream joins, billing, and analytics all quietly fork.
- Import failures the other direction: if the MySQL data already contains rows differing only by case in a column you now want case-insensitively unique, creating the index on lower(email) fails, and you have a data-cleaning project before the constraint can exist.
- Quoted-identifier hell: a schema conversion tool preserves MySQL's CamelCase table names by quoting them, and now every hand-written query in every report needs quotes forever.
- Reordered lists: collation changes ORDER BY results for mixed-case data, which breaks pagination assumptions and the occasional snapshot test.
What makes these bugs expensive is not their difficulty but their latency. Nothing fails at cutover; the login failures trickle in as support tickets, the duplicate accounts accumulate for weeks before analytics notices, and by the time the lower(email) unique index is ready, the data no longer qualifies for it. Every one of these is preventable if you audit for case-dependence before cutover rather than after, and it deserves an explicit line on your migration review checklist. The audit itself is mundane: list every column with a ci collation in MySQL, list every equality lookup against it in the codebase, and decide per column which of the three fixes below applies.
Choosing a fix: lower(), ILIKE, or citext
There are three idiomatic PostgreSQL approaches, and they compose. The first is normalization plus expression indexes: store what the user typed, compare through lower(), and back the comparison with an index on the expression so it stays fast and can enforce uniqueness.
CREATE UNIQUE INDEX users_email_ci ON users (lower(email));
SELECT id FROM users WHERE lower(email) = lower('Alice@Example.com');The second is ILIKE, PostgreSQL's case-insensitive pattern match, right for search boxes and ad-hoc filtering rather than for identity lookups; note that a plain btree index will not accelerate it, so pair it with pg_trgm indexes when it needs to be fast.
The third, and the closest emulation of MySQL's behavior, is the citext extension: a text type whose comparisons are case-insensitive by definition, so equality, unique constraints, and GROUP BY all behave the way your MySQL-era code expects without touching the queries.
CREATE EXTENSION IF NOT EXISTS citext;
ALTER TABLE users ALTER COLUMN email TYPE citext;
-- A plain unique constraint is now case-insensitive:
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);For migrations with a large surface of case-dependent lookups, citext on the handful of identity columns (email, username) plus lower() indexes elsewhere is the pragmatic split. I wrote up the tradeoffs in detail in the citext guide, including the places citext costs you (per-comparison lower() overhead, and pattern-match caveats).
Identifier hygiene during schema conversion
On the identifier side, the winning move is boring: convert every table and column name to lowercase snake_case during the schema conversion, and never quote an identifier again. Fight the conversion tool if it tries to preserve CamelCase with quotes. If a rename is truly impossible, quarantine the quoted names behind views with lowercase names so new code never touches them. And remember the folding rule cuts both ways: MySQL code that relied on case-insensitive table matching will work fine unquoted in PostgreSQL, as long as the objects themselves were created unquoted.
The rename has a second benefit for teams coming from lower_case_table_names=1 environments: it makes the schema portable by construction. There is no per-server setting to reproduce, no initialization-time decision to document, and a dump restored anywhere behaves identically. After years of debugging environment drift between developer laptops and Linux production, I consider that uniformity one of the quieter quality-of-life wins of the move.
How MonPG helps after you land on PostgreSQL
Case bugs have a monitoring signature. A lookup that lost its index because the code says lower(email) and the index is on email turns into a sequential scan, and a query family's mean time and block reads jump. Duplicate-account writes show up as constraint-violation error spikes. A citext conversion changes the cost profile of the hottest lookups. These are exactly the shifts you want visible in the first weeks on the new engine.
MonPG tracks PostgreSQL query families over time, calls, latency, rows, and block behavior, and surfaces the sequential scans, missing-index patterns, and error trends that case fixes leave behind. It monitors PostgreSQL only, and it is most valuable when the baseline starts before cutover traffic does. The PostgreSQL monitoring guide covers what that baseline should include.