An enum stores a small reference to the enum value in the tuples, and the constraint approach stores the string itself. The constraint approach takes more space with (a) larger enum values, (b) more columns using them, and (c) more rows in the tables that use them. The difference depends on the characteristics of the database we're talking about.
> Is there a reason ALTER TYPE name ADD VALUE new_enum_value wouldn't Just Work for the first example for 99% of use cases?
I'm sorry, we don't have any examples in the article about adding values to enums, so I don't know to what you are referring here.
> Yes, that's true, but also very rare?
It is rare, indeed, but we just have a strong inclination towards having a clean schema, so we do prefer to clean up things that would otherwise be unused. But I understand that many people would prefer to leave them hanging there, even if they are not going to be used in any way.
That would be an improvement on the `CHECK` constraint approach if the same `CHECK` constraint is used in multiple columns, to DRY it up. The `DOMAIN` types offer the same commands for `CHECK` constraints, namely `NOT VALID` when updating the constraint, and `VALIDATE CONSTRAINT` to make sure it is valid after having updated it.
"Mindset: The New Psychology of Success". By Carol Dweck, PhD. This is a game-changer. It taught me what we believe is what really defines what we can or cannot do.