Re: ENUM type - Mailing list pgsql-advocacy

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
>>fortune.
>>
>>
>>
>
>
>
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
normalization.

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

>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