From: Andrew Dunstan <andrew@dunslane.net>
To: Łukasz Walkowski <lukasz.walkowski@homplex.pl>
Cc: Vitalii Tymchyshyn <tivv00@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Varchar vs foreign key vs enumerator - table and index size
Date: Mon, 02 Sep 2013 13:02:56 -0400
Message-ID: <5224C4C0.2020702@dunslane.net> (raw)
In-Reply-To: <9F6FD605-91FC-4633-B393-75503463C7E7@homplex.pl>
References: <3AAC8AFE-E908-4AF7-ACDE-F9461B4A62D1@homplex.pl>
<CABWW-d28Y9HiLAvnj5BDm-yeMXr61aWv3HiUQtKj1_V-+DoYnA@mail.gmail.com>
<9F6FD605-91FC-4633-B393-75503463C7E7@homplex.pl>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 09/02/2013 05:53 AM, Łukasz 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 enums). 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 suboptimal plans for join in offset/limit queries.
>> b) Store small varchar values as keys (up to "char" type if you really want to save space) and do user display mapping in application. It's different 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 than (a).
>> c) Do mixed approach with mapping table, loaded on start into application memory. This would be an optimization in case you get into optimizer troubles.
>>
>> 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 queries 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
off in this thread. Looking at the original problem description they
look like quite a good fit, despite the OP's skepticism. What exactly is
wanted that can't be done with database enums? You can add new values to
the type very simply. You can change the values of existing labels in
the type slightly less simply, but still without any great difficulty.
Things that are hard to do include removing labels in the set and
changing the sort order, because those things would require processing
tables where the type is used, unlike the simple things. But neither of
these is required for typical use cases. For most uses of this kind they
are very efficient both in storage and processing.
cheers
andrew
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andrew@dunslane.net, lukasz.walkowski@homplex.pl, tivv00@gmail.com
Subject: Re: Varchar vs foreign key vs enumerator - table and index size
In-Reply-To: <5224C4C0.2020702@dunslane.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox