Representing Enums in PostgreSQL

The debate between using native enums or regular string columns with CHECK constraints in PostgreSQL has been settled in favor of the latter. While native enums come with ordering, type safety, and space efficiency, they also have limitations, such as the inability to remove an existing value. Updating an enum would require creating a new one and swapping existing columns to use the new type, which could have serious performance implications due to the restrictive ACCESS EXCLUSIVE lock. String columns with CHECK constraints, on the other hand, allow for easier updates and database consistency enforcement, but are less space efficient as the actual values are stored in the tuples themselves. The decision was ultimately based on the less restrictive locking requirements and manageable update procedures of CHECK constraints.

To top