[SPBS]

I prefer directly using strings as enums, and using the foreign key constraint only to validate enum values.

    CREATE TABLE my_enum ( name TEXT PRIMARY KEY );

    CREATE TABLE foo ( my_enum TEXT REFERENCES my_enum (name) );

The reason is because a SELECT * FROM foo showing cryptic enum ordinals is a headache, and having to join the enum table every time is potentially slower than just reading from the column directly. An ASCII character only takes 1 byte, so an INT enum is just as space efficient as using 4 characters, which affords way more descriptiveness than a meaningless ordinal number.

[ledgerdev]

If you use longer named enums(eg. my_enum_xyz) in my_enum, does this store a full copy of the enum text bytes of 'my_enum_xyz' into table foo?

[SPBS]

Yeah the full text of the enum is stored in the table ('my_enum_xyz' is 11 ASCII characters so it takes up 11 bytes, plus 1 byte needed to store the length of the string).

[faangsticle]

The point of enums is so the names can be descriptive, which typically will be longer than 4 characters.

[SPBS]

Perhaps I was unclear, my underlying point is that short strings are cheap. And enum names are almost always short strings. 4 characters not enough? What about 8? That’s the size of a BIGINT, which everybody now uses for primary keys (they’re not slow). What about 16 characters? That’s the size of a UUID, which everyone now uses for primary keys (they’re not slow).

Even 4 characters (which perform just as fast as Postgres’ native enums) is enough if you develop a system of abbreviations. The airline industry gets by with just three characters!

[hn_throwaway_99]

Just wanted to let you know I really appreciate your comments. I hadn't thought about doing it the way you recommend (in my mind I was thinking "oh, storing enum values as text will be inefficient", but I never did the math as you did to realize storing anything up to 8 chars is no worse than bigint), but I think it's a pretty optimal solution. I also checked my DB where we heavily use enums, and basically every enum text value that is longer than 8 characters could easily be shortened/abbreviated to 8 chars and still be very easily understood. Thanks very much!