Challenges with updating an Enum with additional types once we have records that scales to the size of millions

Hi all,

I have a country_code column in one of my tables of a phoenix API app and I’m currently considering on whether I should have it as an enum or a string that I could validate against for a wrongly specified country that would be saved in the DB via an API call to the app.

I basically want to prevent a wrong country name from being stored and I can validate this against a country_code enum type or basically make use of a country_code string to save the country type and validate that against a constant that has a list of allowed country names.

My question is as we update the enum type to add more ‘allowed’ countries, how does this potentially affect a live app in terms of updating the existing DB to introduce the new enum type? Let’s assume this DB already has millions of records. If it can get very challenging and result in potential downtime when we have to do this, would it be a better option to have the country_code as type string ? Does Erlang/Elixir/Phoenix/Ecto has a better way to handle such things which can allow me to easily use the enum type
without any necessary downtime? If so, I’d prefer to go with the enum type as the validations are given out of the box.

Would be great to hear your thoughts on this if there is something to help me with updating an enum type in seamless way.

Thank you.

Adding a type to an enum shouldn’t really touch any of the rows that use that enum, so I don’t think the number of rows really matters. In a migration you’d have:

execute("ALTER TYPE #{enum_name} ADD VALUE #{new_value}")

Ultimately this is more of a postgres question than an elixir or ecto question, and nothing I’ve seen in the postgres docs indicates that adding a value to a type is a problematic or potentially down-time inducing migration.

2 Likes

Thank you very much for your answer @benwilson512 :slight_smile: I do realize this is more of a postgres question by now.

Yeah adding a new type to an enum is entirely safe. However removing a type can be…interesting, see the PostgreSQL Enum type docs for details on how to work around that. :slight_smile:

2 Likes

Thank you for that insight @OvermindDL1 :slight_smile:

1 Like

If you are planning to use an Enum, you might check out https://github.com/gjaldon/ecto_enum. It provides some macros for defining the Enum and running Postgres migrations.

2 Likes

Thank you @axelclark :slight_smile: I had tried out the same package and it worked well for me.