pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedenum data type vs table
3+ messages / 2 participants
[nested] [flat]
* enum data type vs table
@ 2011-05-18 04:23 Seb <spluque@gmail.com>
2011-05-25 22:23 ` Re: enum data type vs table Peter Koczan <pjkoczan@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Seb @ 2011-05-18 04:23 UTC (permalink / raw)
To: pgsql-sql
Hi,
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.
--
Seb
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: enum data type vs table
2011-05-18 04:23 enum data type vs table Seb <spluque@gmail.com>
@ 2011-05-25 22:23 ` Peter Koczan <pjkoczan@gmail.com>
2011-05-25 23:08 ` Re: enum data type vs table Seb <spluque@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Peter Koczan @ 2011-05-25 22:23 UTC (permalink / raw)
To: Seb <spluque@gmail.com>; +Cc: pgsql-sql
On Tue, May 17, 2011 at 11:23 PM, Seb <spluque@gmail.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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: enum data type vs table
2011-05-18 04:23 enum data type vs table Seb <spluque@gmail.com>
2011-05-25 22:23 ` Re: enum data type vs table Peter Koczan <pjkoczan@gmail.com>
@ 2011-05-25 23:08 ` Seb <spluque@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Seb @ 2011-05-25 23:08 UTC (permalink / raw)
To: pgsql-sql
On Wed, 25 May 2011 17:23:26 -0500,
Peter Koczan <pjkoczan@gmail.com> wrote:
> On Tue, May 17, 2011 at 11:23 PM, Seb <spluque@gmail.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.
These are great guidelines, thanks.
--
Seb
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-05-25 23:08 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-18 04:23 enum data type vs table Seb <spluque@gmail.com>
2011-05-25 22:23 ` Peter Koczan <pjkoczan@gmail.com>
2011-05-25 23:08 ` Seb <spluque@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox