Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Representing Enums in PostgreSQL

making.close.com

Post reply on HN
Print view View original

41–50 of 76 posts

Re: Representing Enums in PostgreSQL

#41

Post by ledgerdev »

SPBS wrote: post #28
rpcope1 wrote: post #5

After having suffered through the consequences of "type" enums on MySQL, and see some things go through a long life that used "enums" in the database (in multiple different databases, include Postgres), I'm not convinced that either of these are the right choice for representing enumerations. The string with check constraint seems dumb if for no other reason than if the table that uses it winds up having many rows, y…

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 A…

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?

Re: Representing Enums in PostgreSQL

#42

Post by BulgarianIdiot »

cryptonector wrote: post #26

TFA is all about how to make changes where you drop elements of an enum, and how hard that is. The obvious thing though is not covered: don't do that! Instead you should: a. Add CHECK() constraints on columns of the relevant ENUM type checking that the value is one of the "live" values. b. RENAME "dead" ENUM values to indicate that they're dead. c. UPDATE ... SET column_of_that_enum_type = ... WHERE column_of_that_en…

I find it odd how many schema changes in modern RDBMS must be done on the whole table at once. You can split a table in chunks and recode each chunk gradually in a way which doesn't change the data in it (so no downtime) but removes dead entries like updated enums.

In a way you describe how we can emulate this process. The question is why the heck wouldn't databases do this themselves? Same with adding and dropping columns.

Consider how PostgreSQL encodes null for example, by skipping them in the row as fields, and adding them in a null bitmap in front of the row. Meaning... rows are not uniformly sized, there's no math like offset = row * rowsize + field_offset; kind of addressing for reading a field in PG where recoding some of the rows breaks the entire table.

And yet we have all those huge monolithic operations that need to be done atomically. So weird.

Re: Representing Enums in PostgreSQL

#43

Post by jpmelos »

rollulus wrote: post #40

When would one use this approach versus a domain type [1]? How does it differ? [1]: https://www.postgresql.org/docs/current/domains.html

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.

Re: Representing Enums in PostgreSQL

#44

Post by jpmelos »

akersten wrote: post #20

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? Seems like the only drawback highlighted for native enums is that you have to lock the whole table if you completely swap out the type of one of the columns, which... Yes, that's true, but also very rare? I guess TFA mentions removing a value from an enum, but you shouldn't do that imo - leave the…

> 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.

Re: Representing Enums in PostgreSQL

#45

Post by jpmelos »

jensenbox wrote: post #30

What is the difference in size on disk between the two options?

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.

Re: Representing Enums in PostgreSQL

#46

Post by jpmelos »

out_of_protocol wrote: post #6

smallint looks like a good alternative, with dictionary in the app or separate table. So far i've only seen storing dictionary in app source code approach

I don't like this approach much because getting cryptic integers when you do a `SELECT` in the database is really cumbersome.

Re: Representing Enums in PostgreSQL

#47

Post by aidos »

wojcikstefan wrote: post #33
aidos wrote: post #29

Earlier quoted context omitted.

Heyya! Close customer here. > When False, uses VARCHAR + check constraint for all backends Is this behaviour new? Or is it that alembic still doesn't pick up changes to the enum to recreate the constraint? We implemented our own alembic hooks to automate the migrations for us. We've found the check constraint model to be fairly effective.

Hiya Aidan! :D > Is this behaviour new? Don't think so. Even the docs for SQLA v1.3 ([0]) mention it: > native_enum – Use the database’s native ENUM type when available. Defaults to True. When False, uses VARCHAR + check constraint for all backends. The VARCHAR length can be controlled with Enum.length [0]: https://docs.sqlalchemy.org/en/13/core/type_basics.html#sqla... -- > Or is it that alembic still doesn't pick u…

Happy to ping you guys over the code if you want to look. It’s probably fairly tweak-able to handle your specific cases.

Re: Representing Enums in PostgreSQL

#48

Post by faangsticle »

hn_throwaway_99 wrote: post #35
cryptonector wrote: post #26

TFA is all about how to make changes where you drop elements of an enum, and how hard that is. The obvious thing though is not covered: don't do that! Instead you should: a. Add CHECK() constraints on columns of the relevant ENUM type checking that the value is one of the "live" values. b. RENAME "dead" ENUM values to indicate that they're dead. c. UPDATE ... SET column_of_that_enum_type = ... WHERE column_of_that_en…

Glad to see this as the top comment, completely agree. After reading the article, was thinking that the only real downside of using enum types (but there is one more issue not mentioned, more on that below) is when you need to remove values. In reality, I've found removing enum values to be a very rare occurrence in prod. Removing an enum value fundamentally breaks backwards compatibility, so usually a better option…

> BQ doesn't support enums

Sounds like a reason to use views to work around the limitations of BigQuery

Re: Representing Enums in PostgreSQL

#49

Post by cryptonector »

hn_throwaway_99 wrote: post #35
cryptonector wrote: post #26

TFA is all about how to make changes where you drop elements of an enum, and how hard that is. The obvious thing though is not covered: don't do that! Instead you should: a. Add CHECK() constraints on columns of the relevant ENUM type checking that the value is one of the "live" values. b. RENAME "dead" ENUM values to indicate that they're dead. c. UPDATE ... SET column_of_that_enum_type = ... WHERE column_of_that_en…

Glad to see this as the top comment, completely agree. After reading the article, was thinking that the only real downside of using enum types (but there is one more issue not mentioned, more on that below) is when you need to remove values. In reality, I've found removing enum values to be a very rare occurrence in prod. Removing an enum value fundamentally breaks backwards compatibility, so usually a better option…

> Note one other thing I have found is that postgres enums are less "portable" than plain text columns. For example, if you're using BigQuery to query postgres tables as an external connection, BQ doesn't support enums, so your external connection query has to cast any enum return values (e.g. some_enum_column::text) before returning them to BQ, which can be a pain if you want to do "SELECT * FROM ..." some table that returns enum columns.

Yeah, one has to write more adapter queries to add those casts to text (or from text, for DMLs), but it's OK, and ENUMs are just really nice. And as u/faangsticle says, use VIEWs for that.

Re: Representing Enums in PostgreSQL

#50

Post by faangsticle »

SPBS wrote: post #28
rpcope1 wrote: post #5

After having suffered through the consequences of "type" enums on MySQL, and see some things go through a long life that used "enums" in the database (in multiple different databases, include Postgres), I'm not convinced that either of these are the right choice for representing enumerations. The string with check constraint seems dumb if for no other reason than if the table that uses it winds up having many rows, y…

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 A…

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