A new order status is a one-word change to a spec and, on MySQL, potentially a full table rewrite. The team's reaction to that scar is almost always the same: drop enums, store a tinyint, decode it in the app. That reaction costs more than the rebuild did. This is the situation, why it happens, what Postgres does instead, and the price of each option - told honestly, because Postgres enums have real limits too.
👋 Hi, I'm Anton - a software engineer working mostly in PHP/Symfony and Go, usually writing about breaking a monolith into services. This one is smaller and self-contained: a database-design decision people get wrong because they're carrying a MySQL scar into Postgres, and I carried it too for a while. Running notes live on my GitHub: github.com/brilliant-almazov. Maybe you landed somewhere else on this - I'd like to hear where.
What broke
The change request is boring: orders need one more status. partially_refunded, say, and it belongs next to refunded, not tacked on at the end - the status column is ordered and people read it in order.
On MySQL, that "belongs next to" is the expensive word.
An enum in MySQL is welded to the column. Its members are stored by position, so putting a new one in the middle renumbers the existing members. Renumbering means every row has to be written again, which means the change is not metadata - it's a full table COPY. The table gets rebuilt. It locks. And it needs as much free disk as the table itself, which is its own kind of surprise at 2 a.m.
The variant that is cheap - appending at the end - is cheap only under conditions: INSTANT, metadata-only in modern MySQL 8, if the storage size doesn't change. Cross a storage-size boundary and you get a copy anyway. And historically, before 5.6.16, even the "fast" append rebuilt the whole table.
So the real shape of the incident is not "one migration was slow". It's that an enum change in MySQL is position-, size-, and version-dependent: three conditions someone has to reason about correctly, every time, forever.
What it cost - the part that outlives the incident
Nobody wants to run that reasoning twice. So the team does what teams do: gives up on enums entirely and stores an int/tinyint plus a code map.
That's the expensive decision, and it's paid every day afterwards:
- The database holds
3. To know that3meanspartially_refundedyou either keep a lookup table - a JOIN on every read - or an app-side map you must keep in lockstep with the DB. - Every ad-hoc query, every support ticket, every dashboard goes through a decoding step that lives in someone's head.
- When the map drifts from the database, nothing breaks loudly. It just becomes quietly wrong.
Magic numbers in every row. Remembering ints is no fun, and the drift is a source of quiet bugs.
The rebuild was one bad night. The tinyint is a tax with no end date.
Which is where my own preference comes from, and I'd rather name it than let it leak: I like it when a set of values is typed, because then the type checks the thing a person would otherwise have to check in review - and in that story nobody was checking, which is why the map drifted. It's a preference, not a law. What follows is what it costs to hold it, on each database, with the price attached.
Why Postgres doesn't have this failure mode
Postgres doesn't weld the enum to a column. An enum is an independent type living in the catalog (pg_type / pg_enum), and your column just references it. That single design difference is the whole story:
MYSQL POSTGRES
enum is part of the COLUMN enum is an independent TYPE
ALTER TABLE ... MODIFY enum ALTER TYPE order_status
│ ADD VALUE 'refunded'
▼ │
middle/reorder/size-cross ▼
│ catalog change - a row in pg_enum
▼ │
full table COPY → rewrite + LOCK ▼
(as big as the table, downtime) no table rewrite, no table lock
ALTER TYPE ... ADD VALUE writes one row to the catalog. It does not rewrite the table the column lives in, and it doesn't lock that table's data. Since Postgres 12 it's even transactional - you can run it inside a transaction, with one caveat: the new value can't be used until the transaction commits. On PG < 12 you couldn't ADD VALUE inside a transaction at all.
And the part that makes the tinyint escape pointless here: a Postgres enum value is stored as a 4-byte OID - exactly as compact as an integer - but type-safe, human-readable, and with no JOIN to decode. The int's footprint, the string's readability, the type system's guarantee, in one column.
None of that asks you to take my word for it. The enum being its own catalog object, the 4 bytes read straight out of the row, the rollback that takes the added value back out since 12 - each one is a statement you can check on your own instance in a few minutes. That's why the preference survives here and didn't survive on the other side of the table.
Adding a value is transactional - and that's the whole point
The catalog row is the cheap part. The part that removes a whole class of deploy anxiety is when that row becomes visible.
Before Postgres 12, ALTER TYPE ... ADD VALUE could not run inside a transaction block at all - the command simply failed. Since Postgres 12 it can, and that changes what you can guarantee: roll the transaction back, and the added value vanishes with everything else in it. There is no half state left behind - no "the type gained the value, but the dictionary row never landed".
That matters because of a very ordinary setup. A system keeps a dictionary of values and an enum type that are required to agree with each other; the dictionary is what people and other services read, the type is what the column is constrained by. Drift between the two is the bug you find weeks later. So you add the dictionary row and run ALTER TYPE ... ADD VALUE in the same transaction. Commit - both exist. Roll back - neither does. The agreement is held by the transaction itself, not by the ordering of deploy steps and not by a retry loop that someone has to get right.
BEGIN;
INSERT INTO dictionary.order_status (code, title)
VALUES ('partially_refunded', 'Partially refunded');
ALTER TYPE orders.order_status ADD VALUE IF NOT EXISTS 'partially_refunded';
COMMIT;
-- the value can be written into data from the next transaction onward
ADD VALUE IF NOT EXISTS makes the whole thing idempotent, so a migration that runs twice - or a message that gets redelivered - changes nothing the second time.
Now the caveat, said out loud rather than buried: a new value cannot be used in the same transaction that added it, unless the type itself was created in that transaction. Try to insert a row carrying the new value before the commit and Postgres refuses with an error of the form unsafe use of new value of enum type. So the working order is two transactions: the first adds the value (and the dictionary row, if there is one), the second writes data with it. That's a sequencing rule, not a blocker - but you want to know it before you write the migration, not during it.
MySQL has no equivalent path. Changing an enum there can rebuild the table, which is exactly why the projects that got burned reach for an int. It works - and it leaves the database full of numbers that mean nothing on their own. To learn what 3 stands for you need either a JOIN to a dictionary or an app-side map kept in sync by hand. A Postgres enum reads straight out of the row, no JOIN. That's the difference in one line.
The honest caveats - what an enum still costs you
I'd be selling you something if I stopped at the good news. Over a long stretch since that night this has held up on my side and I haven't been burned by it - which is an observation from one system, not a promise about your next incident. The limits are real, they're what decide fit, and they belong here in the middle of the text rather than in a footnote under it:
-
You can't easily remove a value. There is no
DROP VALUE. Retiring one means recreating the type (rename old → create new → migrate the column → drop old) or living with a dead value. Enums are add-mostly by nature. -
Non-end inserts can be slightly slower. Placing a value with
BEFORE/AFTERrather than at the end can make comparisons on that type marginally slower than on the original members. - A new value can't be used in the transaction that added it - unless the type was created there too. One transaction adds it, the next writes data with it.
- They're for smallish, stable-ish vocabularies. Status, kind, channel - not a high-churn set, not something unbounded. There's a practical ceiling on how many values stay sensible.
- They don't travel. A Postgres enum is Postgres-specific; an int moves to another engine and an enum doesn't.
One sentence of trade-off: an enum is lightweight (4 bytes) and readable, and for a small, mostly-append vocabulary I take that over the "can't easily remove" limits. When the set churns hard, needs deletion, or carries metadata, I don't - and there I reach for a lookup table or an int. On purpose, not by reflex, and with the scar accounted for rather than driving.
The fix, and the tool that came out of it
Even on Postgres, managing enum values by hand is fiddly: ALTER TYPE … ADD VALUE IF NOT EXISTS, getting idempotency right, handling schema-qualified type names, keeping the set in sync between migrations and the values the app actually produces. Small, repetitive, easy to get slightly wrong - exactly the kind of thing that deserves a helper rather than a convention.
So I wrote one: pgenum - a zero-dependency Go library (only database/sql; works with pgx, lib/pq, anything). It's small on purpose:
-
Idempotent - every call is
ADD VALUE IF NOT EXISTS, safe to repeat. -
Concurrent-safe - no shared state; Postgres serialises
ALTER TYPEitself. -
Schema-qualified -
orders.order_status/audit.event_typework out of the box. - Injection-safe - identifier validation + quoting on every name and value.
import "github.com/brilliant-almazov/pgenum"
// the whole surface you usually need:
type Ensurer interface {
EnsureValue(ctx context.Context, typeName, value string) error
EnsureValues(ctx context.Context, typeName string, values ...string) error
}
func New(db *sql.DB) Ensurer
// plus helpers: SyncEnumFromColumn(...), EnumValues(...), HasValue(...)
Deploy-time (static): declare the vocabulary in YAML, apply it once at startup, right after migrations. Deterministic and reviewable.
type Config map[string][]string
func (c Config) ApplyDB(ctx context.Context, db *sql.DB) error
func seedEnums(ctx context.Context, db *sql.DB, path string) error {
data, err := os.ReadFile(path)
if err != nil {
return err
}
var cfg pgenum.Config
if err := yaml.Unmarshal(data, &cfg); err != nil {
return err
}
return cfg.ApplyDB(ctx, db) // ensures every declared value exists
}
Runtime (event-driven): inject the Ensurer, and when an entity introduces a value, make sure the type knows it.
type OrderService struct {
db *sql.DB
enums pgenum.Ensurer
}
func (s *OrderService) OnOrderCreated(ctx context.Context, order Order) error {
if err := s.enums.EnsureValue(ctx, "orders.order_status", order.Status); err != nil {
return fmt.Errorf("sync enum: %w", err)
}
// ... rest of the handler
return nil
}
Because Ensurer is a one-method-ish interface, it mocks trivially in tests - no database needed to assert "the handler tried to ensure this value."
The decision the library refuses to make for you
Runtime, deploy-time, or both - there's no single right answer, and that's the interesting part.
- Deploy-time fits when the vocabulary is known ahead of time and you want it in version control, reviewed in a PR, applied deterministically on release. Most enums are like this.
- Runtime fits when values genuinely emerge - a third-party integration, a partner's status codes, user-defined-ish categories - and you don't want to ship a deploy just to accept a new one.
I run both on the same system, and each mode was built for one thing. The static dictionary gets applied at startup, right after migrations, because after that incident I wanted the set of values to live in version control and go through review like any other change - adding a status should be a diff someone reads, not a statement someone runs at 2 a.m. The event-driven EnsureValue is there for the handful of values that genuinely arrive from data; running it again is harmless, since every call is ADD VALUE IF NOT EXISTS. And on the hot path I don't call it at all - it's idempotent but still a catalog round-trip, so it's gated: ensure only on a value not seen before, or cache the known set (EnumValues / HasValue) and ensure once. How aggressively to cache follows from write load, which is exactly why it stays a judgment call and not a rule.
Your scenarios are the part I can't see from here. Which of your value sets sit in a dictionary, which sit in a type, and what pushed each one to that side?
enum vs int vs lookup table
| Postgres enum | int / tinyint | lookup table | |
|---|---|---|---|
| Storage | 4 bytes (OID) | 1-4 bytes | int FK + the table |
| Readable in the DB | yes | no (magic number) | via a JOIN |
| Type-safe | yes | no | FK only |
| Read cost | none | none (opaque) | a JOIN |
| Add a value | cheap catalog change | trivial | one INSERT |
| Remove a value | hard (no DROP VALUE) | trivial | one DELETE |
| Values carry metadata | no | no | yes (label, i18n, flags) |
| Cross-database | no (PG-specific) | yes | yes |
Read it as a fit test, not a winner - and here's how I read it for myself. I take a Postgres enum when I'm on Postgres, the set is smallish and mostly-append, and I want compact + readable + type-safe with no JOIN. I take a lookup table when the values carry their own metadata (labels, translations, feature flags). I take an int when I have no choice - cross-database portability, or a set that churns and needs real deletion. That's my reading of the same rows; yours can come out different.
That's my scar, that's the choice I made after it, and those limits are the price I pay for the choice. The bit I'm least sure about is where the line sits between "stable enough for an enum" and "churny enough for a lookup table" - that line moved for me more than once.
So: if you do this better, especially the retire-a-value path, I want to see how. If you've been through this - a middle-of-the-enum change on a big MySQL table, or years of decoding tinyints - the details are the useful part. And if you look at it differently and think native enums are never worth the add-mostly constraint, that's a reading I'd take seriously. How is it solved on your side, and what actually broke when you changed it?
Sources: MySQL online DDL operations · MySQL bug #72997 (enum ALTER full rebuild) · Postgres ALTER TYPE · pgenum
Top comments (0)