9

I notice that some databases like Postgres add Enum but the question is,

At what point integer/string based enum becomes a performance issue?

Following this answer about differences Postgresql enum what are the advantages and disadvantages?

Because when you use Rails or in this case Ecto from Elixir language, there is a level of abstraction so the one of the two advantages listed there becomes no an problem at all so there is only missing performance concern.

So when that advantage is really important that you dropped the disadvantages listed there

Yordis Prieto Lazo
  • 729
  • 12
  • 24

1 Answers1

8

Enum in PostgreSQL

  1. supported by PostgreSQL for Create Data Type.

  2. static data with ordered set values (Which is can be sort by PostgreSQL)

  3. case sensitive 'happy' is not same with 'HAPPY'
  4. Cannot input other values if not created in data type.

For Performance issue: PostgreSQL never has performance issue cause by data-type. most of them cause by indexing, configuration, and bad database design.

For Enum: in PostgreSQL They are equivalent to the enum types supported in a number of programming languages. An example of an enum type might be the days of the week, or a set of status values for a piece of data.

The translations from internal enum values to textual labels are kept in the system catalog pg_enum. Querying this catalog directly can be useful.

Note: both Native or String/Integer based type enum can't cause performance issue.

Adrian Hartanto
  • 435
  • 2
  • 7
  • 1
    At what point integer/string based enum becomes a performance issue? Most of the bullet points can be addressed by some ORM – Yordis Prieto Lazo Jul 05 '17 at 05:47
  • @YordisPrietoLazo why do you think it becomes a performance issue? – zerkms Jul 05 '17 at 05:49
  • @YordisPrietoLazo Enum Performance is better - Refference: https://stackoverflow.com/questions/2318123/postgresql-enum-what-are-the-advantages-and-disadvantages – Adrian Hartanto Jul 05 '17 at 05:59
  • Sorry but you are missing the sentences point: `At what point becomes a performance issue` – Yordis Prieto Lazo Jul 05 '17 at 06:01
  • @YordisPrietoLazo Enum never cause performance issue and never become issue in application also. PostgreSQL is never has performance issue caused by data type except encoding. – Adrian Hartanto Jul 05 '17 at 06:06
  • I am focus more in `string/integer` based side of view. Meaning that, if I am good using `string` based Enum instead of use the Native enum type – Yordis Prieto Lazo Jul 05 '17 at 06:11
  • 1
    @YordisPrietoLazo i suggest you using string/integer based on PotgreSQL and you can check the object more detail in pg_enum. anyway both of them is same and never cause performance issue – Adrian Hartanto Jul 05 '17 at 06:23