[Engine-devel] short research on postgresql enums

[Laszlo Hornyak]

----- Original Message -----
> From: "Itamar Heim" <iheim at redhat.com>
> To: "Maor" <mlipchuk at redhat.com>
> Cc: engine-devel at ovirt.org, "Laszlo Hornyak" <lhornyak at redhat.com>
> Sent: Sunday, December 11, 2011 4:57:57 PM
> Subject: Re: [Engine-devel] short research on postgresql enums
> 
> On 12/11/2011 05:54 PM, Maor wrote:
> > On 12/09/2011 10:43 PM, Laszlo Hornyak wrote:
> >> Hi,
> >>
> >> I looked into Mike's database patch (
> >> http://gerrit.ovirt.org/#change,500 ) today and read Yaniv's
> >> comment on it. I have seen another patches related to enums and
> >> how they are stored in the database. I made a quick test to
> >> compare between varchar and enum and the results are here:
> >> http://dummywarhead.blogspot.com/2011/12/postgresql-enums-vs-varchar.html
> >>
> >> IMO enums could be a good solution, but changing enums could be a
> >> pain under postgres 9.1. So what if we could use varchar now and
> >> migrate to enum once postgres 9.1 replaces the older
> >> installations :)
> >>
> >> Laszlo
> >> _______________________________________________
> >> Engine-devel mailing list
> >> Engine-devel at ovirt.org
> >> http://lists.ovirt.org/mailman/listinfo/engine-devel
> > +1 liked the article.
> > I wonder, how common are database enums among different DB
> > providers,
> > for supporting DB migration in the future if needed.
> 
> that's a very good point, considering we are still on the path of
> moving
> to hibernate.

I did not do a test with hibernate yet, but since a postgresql enum column is read/written as character data, I believe hibernate would not see a difference between enum and varchar. On other RDBMS' these columns could be just plain varchar columns. So we can look at this as a PostfgreSQL-specific optimization.

JPA supports ordinal or name ( http://docs.oracle.com/javaee/6/api/javax/persistence/EnumType.html ). The current JDBC DAO's persist using the custom logic written into the enums. I am not sure this is portable to JPA. So if we move to JPA, we will have a choice:
- persist using ordinal - I think we would want to avoid this, since the code we have in enums and the DAO classes is to avoid this
- persist using name - in this case the column can be an enum on postgresql, which actually has the same performance as the ordinal values (and the custom DAO code), or it can be a varchar (if enum is not supported by the rdbms) and it can be plain varchar, which performs somewhat under the integers.

> 
> >
> > I also searched a little bit on the subject and found the following
> > article:
> >    http://www.designmagick.com/article/29/PostgreSQL-Enum-Types
> > Postgres 8.4.7 supports the CHECK function, which validate specific
> > values. (not sure how is the performance).
> > but maybe we can use it, if we choose to adapt the enum idea later.
> >
> >
> > _______________________________________________
> > Engine-devel mailing list
> > Engine-devel at ovirt.org
> > http://lists.ovirt.org/mailman/listinfo/engine-devel
> 
>