Skip site navigation (1) Skip section navigation (2)

Re: How to modify ENUM datatypes?

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: "D(dot) Dante Lorenso" <dante(at)lorenso(dot)com>
Cc: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>, Andreas 'ads' Scherbaum <adsmail(at)wars-nicht(dot)de>, pgsql-general(at)postgresql(dot)org
Subject: Re: How to modify ENUM datatypes?
Date: 2008-04-22 22:11:36
Message-ID: 1208902296.14025.39.camel@dogma.ljc.laika.com (view raw or flat)
Thread:
Lists: pgsql-general
On Tue, 2008-04-22 at 16:34 -0500, D. Dante Lorenso wrote:
> I see this might be a 
> problem with storage since you will need to store the TEXT value for 
> every row in the 'mystuff' table instead of just storing the reference 
> to the lookup table as an INTEGER.  Over millions of rows, perhaps this 
> would become a concern?

It does use additional storage to store the full text value, rather than
a fixed-size integer. However, the difference is not much when the
average string length is short.

If you store an integer reference instead, joins are not necessarily
expensive. If the number of distinct values is small (which is the
normal use case for ENUM), I would expect the joins to be quite cheap.
Beware of running into bad plans however, or making the optimizer work
too hard (if you have a lot of other joins, too).

I don't think the performance concerns are major, but worth considering
if you have millions of rows.

> What is the general consensus by the community about this approach?  Is 
> this de-normalization frowned upon, or is there a performance advantage 
> here that warrants the usage?

This is not de-normalization, at all. Normalization is a formal process,
and if this were de-normalization, you could find a specific rule that
is violated by this approach.

Look here:
http://en.wikipedia.org/wiki/Third_normal_form

If you go to higher normal forms, you will not find any violations
there, either. There is nothing about normalization that requires the
use of surrogate keys.

The approach suggested by Scott Marlowe is normalized as well as being
quite natural and simple. I think often this is overlooked as being "too
simple", but it's a quite good design in many cases.

Regards,
	Jeff Davis


In response to

Responses

pgsql-general by date

Next:From: David WilsonDate: 2008-04-22 22:46:04
Subject: Re: Rapidly decaying performance repopulating a large table
Previous:From: Tom LaneDate: 2008-04-22 22:10:55
Subject: Re: Rapidly decaying performance repopulating a large table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group