MySQL utf8mb4 vs PostgreSQL UTF-8: Encoding Without the Asterisks

If you have operated MySQL for more than a few years, you have a utf8mb4 story. Maybe it was the emoji that failed to insert, maybe it was the index that would not build after the conversion, maybe it was the weekend spent running ALTER TABLE ... CONVERT TO CHARACTER SET across a fleet. The charset saga is one of MySQL's most educational pieces of history, and it is one of the first things that feels different when you move to PostgreSQL.

This is not a MySQL-bashing post. The MySQL team inherited a decision made when UTF-8 itself was young, and MySQL 8.0 largely closed the chapter by making utf8mb4 the default. But the two databases still treat text fundamentally differently, and if you migrate assuming "UTF-8 is UTF-8," the differences in comparison semantics will find you. Let me walk through the whole picture.

Why MySQL's utf8 was never UTF-8

MySQL's original utf8 character set, now explicitly named utf8mb3, stores at most three bytes per character. Real UTF-8 needs up to four bytes to cover the supplementary planes, which include emoji, many CJK extension characters, and various historic scripts. Insert a four-byte character into a utf8mb3 column and, depending on strictness settings, you get an error or a truncated string at the first offending character.

utf8mb4 arrived in MySQL 5.5 as the actually-correct UTF-8 implementation, and MySQL 8.0 made it the server default with utf8mb4_0900_ai_ci as the default collation. If you run MySQL 8.x with utf8mb4 everywhere, your encoding story is genuinely fine today. The pain lives in older schemas, mixed-charset databases, and the muscle memory of every engineer who learned to double-check charset settings on every table, column, connection, and client library because any layer could silently transcode.

The index prefix limit that shaped a decade of schemas

The subtler cost of utf8mb4 was the InnoDB index size limit. With the older COMPACT and REDUNDANT row formats, an index key could be at most 767 bytes. A VARCHAR(255) in utf8mb4 can need 1020 bytes, so the classic VARCHAR(255) indexed column stopped fitting the moment you converted. Teams responded with VARCHAR(191), prefix indexes, or a switch to the DYNAMIC row format, which raises the limit to 3072 bytes and has been the default since MySQL 5.7's innodb_large_prefix era ended and 8.0 standardized it.

If you have ever wondered why so many Rails and Django schemas from that period contain VARCHAR(191), that is the fossil record of this limit. It is worth naming because it illustrates the deeper difference: in MySQL, encoding choices leaked into schema design. In PostgreSQL they largely do not. PostgreSQL btree index entries have their own size ceiling (roughly a quarter of an 8KB page, around 2704 bytes), but because the database encoding is set once per database and UTF-8 is simply UTF-8, you never chose column lengths to dodge a charset multiplier. Indexing very long text is a bad idea in both systems; hash the value or use a different index type instead.

PostgreSQL's model: one encoding, explicit collations

In PostgreSQL, encoding is a database-level property. Create the database with UTF8 and every text value in it is UTF-8, four-byte characters included, validated on the way in. There is no per-table or per-column character set, no connection charset negotiation dance with three layers of defaults, and no mb3 versus mb4 distinction. Client encoding conversion exists for clients that need it, but the common case is UTF-8 end to end.

Collation, the rules for comparing and sorting text, is where PostgreSQL asks you to be deliberate. Collations come from a provider: the C library (libc), ICU, or the built-in provider added in PostgreSQL 17 with its C.UTF-8 locale. The provider choice matters operationally, because libc collation behavior can change when the operating system upgrades glibc, and a changed sort order silently invalidates btree indexes built under the old order. ICU collations version explicitly and PostgreSQL warns on version mismatch, which is one reason many teams standardize on ICU for user-facing text sorting.

-- An ICU collation with natural sort and case-insensitive comparison:
CREATE COLLATION natural_ci (
  provider = icu,
  locale = 'und-u-kn-ks-level2',
  deterministic = false
);

CREATE TABLE customers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL COLLATE natural_ci UNIQUE
);

That deterministic = false flag is doing real work: it makes equality itself case-insensitive for that column, so the UNIQUE constraint treats Alice@example.com and alice@example.com as duplicates. Nondeterministic collations have limitations (LIKE support only arrived in PostgreSQL 18, and pattern operations are generally restricted), so the long-standing alternative is the citext extension or explicit lower() expression indexes. All three approaches work; pick one per project and be consistent.

The migration pitfall nobody warns you about: comparison semantics

Here is the difference that actually breaks applications. MySQL's default collations are accent- and case-insensitive: utf8mb4_0900_ai_ci says so in the name. In MySQL, WHERE email = 'ALICE@EXAMPLE.COM' matches alice@example.com. In PostgreSQL, with default deterministic collations, string equality is byte-conscious and case-sensitive. The same query on the same data returns different rows.

This shows up as missing search results, failed logins, and duplicate accounts after migration, and it will not be caught by schema validation because the schema is fine. The fix is an audit: find every string comparison in the application that relied on MySQL's insensitivity, then choose citext, a nondeterministic collation, or lower() normalization for each. Unique constraints deserve special attention, because data that was impossible to duplicate under MySQL's collation becomes insertable under PostgreSQL's, and you want to catch collisions during the load, not from a support ticket.

-- Find case-insensitive duplicate candidates before adding constraints:
SELECT lower(email) AS normalized, count(*) AS occurrences
FROM customers
GROUP BY lower(email)
HAVING count(*) > 1
ORDER BY occurrences DESC;

Run checks like this against the migrated data before you enable the application. Every group returned is a decision to make: merge, rename, or deliberately allow.

Other edges worth checking before cutover

• Invalid bytes in old data. Long-lived MySQL databases sometimes contain latin1 bytes labeled as utf8, double-encoded strings, or truncated multi-byte sequences from the mb3 days. PostgreSQL validates UTF-8 on input and will reject them at load time. Better to find and repair these in a rehearsal than during the cutover window.
• Sorting changes. Even correct data can sort differently under a different collation. If any product feature depends on ordering (alphabetical listings, pagination cursors over names), verify against the new collation explicitly.
• Trailing spaces. Older MySQL collations ignored trailing spaces in comparisons on CHAR and, pre-8.0, VARCHAR semantics differed by collation pad attributes. PostgreSQL compares what is there. Trim on load if your data grew accidental padding.
• Length semantics. Both databases count VARCHAR length in characters, not bytes, so lengths carry over cleanly. Watch TEXT column mappings and any application logic doing byte-length math.

Treat this as an audit checklist, not a reason to delay. Every one of these has a mechanical fix, and the payoff is a text stack you stop thinking about. Once you are on PostgreSQL, the encoding layer becomes uninteresting in the best way, and your remaining text-related surprises tend to be about types and time zones instead; our writeup on timestamp vs timestamptz covers the equivalent trap in the time domain. For the broader system-level differences, the comparison pages are a good map.

After the migration: watching text-heavy workloads with MonPG

Collation choices have a performance dimension that only shows up under production load. ICU comparisons cost more than the C locale, nondeterministic collations disable certain index optimizations, and a lower() expression index that the migration forgot to create turns a fast login lookup into a sequential scan. These are exactly the regressions that hide until traffic arrives.

MonPG monitors PostgreSQL only, and it is built for this after-the-cutover phase: pg_stat_statements history shows which query families changed, plan context shows when an index stopped being used, and wait-event tracking separates CPU-bound collation cost from I/O problems. Set the baseline before cutover so the before-and-after comparison exists when you need it. The PostgreSQL monitoring guide describes the signals worth collecting from day one.