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

Re: ENUM type

From: Chris Travers <chris(at)travelamericas(dot)com>
To: Jeff Davis <jdavis-pgsql(at)empires(dot)org>,PostgreSQL advocacy <pgsql-advocacy(at)postgresql(dot)org>
Subject: Re: ENUM type
Date: 2005-07-27 05:33:25
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-advocacypgsql-hackers
Jeff Davis wrote:

>>Normalization is about a lot more than just saving space in your base
>>tables. But since that's the example you used, you a) can't assume it's
>>only a few bytes and b) can't assume that those few bytes won't start to
>>seriously add up over the span of a few hundred million rows.
>>Remember: while disk space might be cheap, disk I/O bandwidth costs a
First, just to be straight-- I see normalization as having two benefits 
neither have anything to do with disk access.
The first is that the database is easier to maintain when it is 
atomically defined.
The second is that it helps ensure that data is always maintained in a 
meaningful fashion.

Disk I/O is a different issue and in my mind not really connected to 

The varchar primary key idea (which I think is probably the best 
solution) is certainly normalized, but it is also certainly inefficient 

>First, I doubt there exists a single case in the universe where someone
>has 100 million rows of an enum type in MySQL, and they want to convert
>to PostgreSQL without redefining their tables.
>I would say the separate table is the way I would do it, but as far as a
>conversion from MySQL->PostgreSQL, why are we trying to normalize their
>tables along the way? Wouldn't the simple solution be the way to get
>them started?
The bigger question is do we really want to have braindead datatypes in 
the backend?

Also "simple" may be in the eye of the beholder here.  Just because 
something is opaque does not necessarily make it simple.

>Nobody is going to expect that much from a conversion. They get their
>app going on PostgreSQL, and slowly start to do things the right way. If
>we hide the fact that we're normalizing their data, how does that really
>help them?
It is not just a matter of helping them.  It is also a matter of trying 
to provide something that some people find useful in a way that is 
actually reasonable from a database perspective.

>However, it's fine with me if we do it that way. If there's additional
>effort I just don't know whether it's worth it.
I actually think it would be less work to do it this way.  Most of the 
work would already be done.  I.e. we are talking largely about 
automating existing pieces rather than building something new.  I 
personally don't think that this would be too hard.  I might even be 
willing to try at some point in the near future.

Best Wishes,
Chris Travers
Metatron Technology Consulting

In response to


pgsql-hackers by date

Next:From: Chris TraversDate: 2005-07-27 05:39:37
Subject: Re: [HACKERS] Enticing interns to PostgreSQL
Previous:From: brookDate: 2005-07-27 05:17:57
Subject: Re: psql as an execve(2) interpreter

pgsql-advocacy by date

Next:From: Chris TraversDate: 2005-07-27 05:39:37
Subject: Re: [HACKERS] Enticing interns to PostgreSQL
Previous:From: Jeff DavisDate: 2005-07-27 04:11:25
Subject: Re: ENUM type

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