Re: Varchar vs foreign key vs enumerator - table and index size

On Sat, Aug 31, 2013 at 10:06 AM, Łukasz Walkowski <
lukasz(dot)walkowski(at)homplex(dot)pl> wrote:

> > I think the main "pro" of this approach is that it doesn't use any
> > nonstandard SQL features, so you preserve your options to move to some
> > other database in the future. The main "con" is that you'd be buying
> into
> > fairly significant rewriting of your application code, since just about
> > every query involving these columns would have to become a join.
>
> Well, I don't really think I will move from Postgresql anytime soon. It's
> just the best database for me. Rewriting code is one of the things I'm
> doing right now but before I touch database, I want to be sure that the
> choices I made are good.
>

If your applications are read-heavy and only have a small-ish amount of
code that inserts/updates the table, it may not be that much of a rewrite.
You can create a integer/varchar table of key/values, use its key to
replace the current varchar column, rename the original table, and create a
view with the original table's name. Code that only reads the data won't
know the difference. And it's a portable solution.

I did this and it worked out well. If the key/value pairs table is
relatively small, the planner does an excellent job of generating efficient
queries against the big table.

Craig