MySQL ENUM vs PostgreSQL ENUM: Migration Notes

ENUM is one of those features that looks identical across MySQL and PostgreSQL from thirty feet away and behaves differently in every detail that matters at 2 a.m. I have operated schemas with hundreds of ENUM columns in MySQL and inherited PostgreSQL databases with dozens of enum types, and my position after both experiences is unfashionable but consistent: the migration is a good moment to ask whether you want ENUM at all.

This article covers what each engine actually does under the hood, where the ALTER pain lives in each, how to map one to the other mechanically, and when a plain lookup table beats both.

What MySQL ENUM actually is

In MySQL, ENUM is a column-level string list stored as a 1- or 2-byte integer index into that list. It is compact and fast to compare, and that is where the good news ends for the unwary.

The classic traps are well documented but still bite. Sorting is by index position, not by string value, so ORDER BY on an ENUM column returns values in declaration order unless you cast. Comparing an ENUM to a number compares against the index, not the string, which makes a value list like ('0','1','2') a loaded weapon. And under non-strict SQL modes, inserting an invalid value historically stored the special empty string with index 0 instead of failing; strict mode, the default since 5.7, rightly turns that into an error.

-- MySQL: ORDER BY follows declaration order, not the alphabet
CREATE TABLE tickets (
  id BIGINT PRIMARY KEY,
  status ENUM('open', 'pending', 'closed') NOT NULL
);

SELECT status FROM tickets ORDER BY status;
-- open, pending, closed  (declaration order)
SELECT status FROM tickets ORDER BY CAST(status AS CHAR);
-- closed, open, pending  (what a new teammate expects)

The definition also lives on the column, not in a shared object. If ten tables have a status column, you maintain ten copies of the list, and nothing stops them drifting apart.

What PostgreSQL ENUM actually is

PostgreSQL enums are real types created with CREATE TYPE ... AS ENUM. The values live in the pg_enum catalog, occupy 4 bytes on disk per value stored, and the type can be shared by any number of columns, which fixes MySQL's copy-drift problem at the source. Sort order follows declaration order here too, but that is a documented design choice you can control: ALTER TYPE lets you add a value BEFORE or AFTER an existing one.

Invalid values are always an error; there is no PostgreSQL equivalent of the silent empty-string insert. Casting between the enum and text is explicit and predictable.

-- PostgreSQL: a shared type, extendable in place
CREATE TYPE ticket_status AS ENUM ('open', 'pending', 'closed');

CREATE TABLE tickets (
  id BIGINT PRIMARY KEY,
  status ticket_status NOT NULL
);

ALTER TYPE ticket_status ADD VALUE 'on_hold' BEFORE 'closed';

That ALTER TYPE ... ADD VALUE is cheap: it is a catalog change, not a table rewrite, regardless of how many billions of rows use the type.

The ALTER story is where the engines really diverge

Changing an ENUM is the operational moment of truth in both engines, and they fail in opposite directions.

In MySQL, adding a value to the end of the list is usually an in-place or even instant metadata change in 8.0, provided the storage size does not change. But inserting a value in the middle, removing one, or reordering the list forces a full table copy, because the stored integers must be remapped. On a large table under load, that is a scheduled maintenance event disguised as a one-line ALTER.

In PostgreSQL, adding values anywhere in the order is trivial, as shown above. The pain is on the other side: you cannot drop a value from an enum type, ever, short of creating a new type, rewriting every column that uses the old one, and dropping it. Renaming a value is supported (ALTER TYPE ... RENAME VALUE, since PostgreSQL 10), but removal is a genuine gap. There is also a transactional wrinkle: a value added inside a transaction cannot be used until that transaction commits, which occasionally surprises migration frameworks that add a value and insert rows using it in one step.

So MySQL punishes reordering and removal with rewrites, and PostgreSQL simply refuses removal. Neither engine makes value retirement graceful, and value retirement is exactly what happens to every status column that survives three product pivots.

Why lookup tables often win in both engines

A lookup table with a foreign key gives you everything ENUM promises with none of the ALTER cliff edges. Adding a value is an INSERT. Retiring one is an UPDATE to an is_active flag. Display order is a sort_order column you can change with an UPDATE instead of a type surgery. And the moment your "simple" status needs a label, a color, a description, or a permissions rule, you have a natural place to put it instead of a parallel constants file in three services.

The costs are real but modest: one join or an application-side cache for display, and slightly more verbose inserts. Modern PostgreSQL handles small reference-table joins so cheaply that I have never measured them as a problem, and the same is true of InnoDB. In both engines my rule of thumb is the same: ENUM is fine for value sets that are genuinely fixed by the domain, like card suits or days of the week; anything product-shaped belongs in a table. I wrote up the PostgreSQL side of that argument in more depth, including the CHECK-constraint middle ground, in PostgreSQL ENUM vs lookup tables.

There is also a middle option worth naming on both engines: a plain text or VARCHAR column with a CHECK constraint listing the allowed values. PostgreSQL has always enforced CHECK constraints, and MySQL has enforced them since 8.0.16, so the pattern is portable now in a way it was not for most of MySQL's history. It keeps validation in the database, makes additions a constraint swap rather than a type change, and needs no join; what it gives up is the metadata home that a real table provides.

Migration mapping: the mechanical part

If you decide to keep enums, the mapping is straightforward but has ordering traps. For each distinct MySQL ENUM definition, create one PostgreSQL enum type, declaring values in the same order the MySQL column did so that ORDER BY semantics survive the move. Deduplicate as you go: ten MySQL columns with the same value list should become one PostgreSQL type, and ten columns with almost the same list is a data-cleanup task you want to discover now, not in production.

Sweep the data first for MySQL's empty-string index-0 values left behind by old non-strict inserts; they have no PostgreSQL representation and should be mapped to NULL or a real value before transfer. Also check application code for numeric comparisons against ENUM columns, because that MySQL index arithmetic has no equivalent after the move.

If you decide to switch to lookup tables, the migration is the cheapest moment you will ever get: you are already rewriting every INSERT path and every schema definition, so replacing the enum with a smallint or text foreign key adds little marginal work. A migration is a rare chance to fix a modeling decision without a dedicated project, and general schema hygiene like this is covered in our broader PostgreSQL resources.

Living with enums after the cutover

Whatever you choose, write down the change procedure while it is fresh. For PostgreSQL enums: additions are safe and cheap, use BEFORE/AFTER to keep sort order sensible, never plan on removals, and remember the same-transaction restriction in migration scripts. For lookup tables: protect them with foreign keys, cache them in the application if the join ever shows up in profiles, and resist the urge to let services hardcode the IDs.

The schema decision is one afternoon. The operational habits around it are what your team lives with for years.

After you land on PostgreSQL: where MonPG fits

MonPG monitors PostgreSQL only, so it enters the story once your workload is on the other side of this migration. That is precisely when enum-adjacent problems surface: a migration script blocked behind a lock while altering a heavily used type, a lookup-table join that regressed after a deploy dropped an index, or a data backfill holding a transaction open long enough to stall autovacuum.

MonPG keeps that evidence in one workflow: query history from pg_stat_statements to catch the regressed join, lock chains to show which session is blocking the type change, and vacuum signals to flag the backfill before it becomes bloat. Post-migration is when a database is least understood by its own team; the PostgreSQL monitoring baseline is how you shorten that period from months to weeks.