Re: enum data type vs table

On Tue, May 17, 2011 at 11:23 PM, Seb <spluque(at)gmail(dot)com> wrote:
> Are there any guidelines for deciding whether to 1) create an enum data
> type or 2) create a table with the set of values and then have foreign
> keys referencing this table?  Some fields in a database take a small
> number of values, and I'm not sure which of these routes to take.  The
> enum data type seems like a clean way to handle this without creating a
> constellation of tables for all these values, but if one wants to add a
> new label to the enum or make changes to it at some point, then the
> tables using it have to be recreated, so it's quite rigid.  Have I got
> this right?  Thanks.

I think your choice depends on a few things:

1 - How do you want to interact with the tables? What I mean is, are
you planning on querying, inserting, or updating data to those tables
via text or will you need to join to your reference table? If you
don't want to join, you'll either need to use enum types, use views
(which can be a pain if you want to update a view), or
duplicate/reference the text directly (which is slow and a bad idea
for several reasons).

2 - How much can you tolerate downtime or a busy database? Changing
types is a single transaction and requires an exclusive lock. On small
tables this is negligible, but on big tables it can require downtime.

3 - How often do you really expect changes to the enum type? If adding
a new value to an enum type is truly a rare event, it's . If it's
frequent or regular, you should probably have a table.

I've used both of these approaches and I've found enum types to be
well worth any trouble to drop/recreate types. The changes I've made
have been rare, and I've been able to schedule downtime pretty easily,
so it made the most sense for me.

Also, Postgres 9.1 allows adding values to enum types, so you could
always use that when it is finally released.

Hope this helps,

Cheers,
Peter