Message-ID: <5224C4C0.2020702@dunslane.net>
Date: Mon, 02 Sep 2013 13:02:56 -0400
From: Andrew Dunstan <andrew@dunslane.net>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20130110 Thunderbird/17.0.2
MIME-Version: 1.0
To: =?ISO-8859-2?Q?=A3ukasz_Walkowski?= <lukasz.walkowski@homplex.pl>
CC: Vitalii Tymchyshyn <tivv00@gmail.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Varchar vs foreign key vs enumerator - table and index
 size
References: <3AAC8AFE-E908-4AF7-ACDE-F9461B4A62D1@homplex.pl>
 <CABWW-d28Y9HiLAvnj5BDm-yeMXr61aWv3HiUQtKj1_V-+DoYnA@mail.gmail.com>
 <9F6FD605-91FC-4633-B393-75503463C7E7@homplex.pl>
In-Reply-To: <9F6FD605-91FC-4633-B393-75503463C7E7@homplex.pl>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


On 09/02/2013 05:53 AM, =A3ukasz Walkowski wrote:
> On 1 wrz 2013, at 05:10, Vitalii Tymchyshyn <tivv00@gmail.com> wrote:
>>
>> Well, there are some more options:
>> a) Store int keys and do mapping in the application (e.g. with java enum=
s). This can save you a join, that is especially useful if you are going to=
 do paged output with limit/offset scenario. Optimizer sometimes produce su=
boptimal plans for join in offset/limit queries.
>> b) Store small varchar values as keys (up to "char" type if you really w=
ant to save space) and do user display mapping in application. It's differe=
nt from (a) since it's harder to mess with the mapping and values are still=
 more or less readable with simple select. But it can be less efficient tha=
n (a).
>> c) Do mixed approach with mapping table, loaded on start into applicatio=
n memory. This would be an optimization in case you get into optimizer trou=
bles.
>>
>> Best regards, Vitalii Tymchyshyn
> I'd like to leave database in readable form because before I add some new=
 queries and rest endpoints to the application, I test them as ad-hoc queri=
es using command line. So variant a) isn't good for me. Variant b) is worth=
 trying and c) is easy to code, but I still prefer having all this data in =
database independent of application logic.
>


I think the possible use of Postgres enums has been too easily written=20
off in this thread. Looking at the original problem description they=20
look like quite a good fit, despite the OP's skepticism. What exactly is=20
wanted that can't be done with database enums? You can add new values to=20
the type very simply.  You can change the values of existing labels in=20
the type slightly less simply, but still without any great difficulty.=20
Things that are hard to do include removing labels in the set and=20
changing the sort order, because those things would require processing=20
tables where the type is used, unlike the simple things. But neither of=20
these is required for typical use cases. For most uses of this kind they=20
are very efficient both in storage and processing.

cheers

andrew


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance