From: Craig Ringer <craig@postnewspapers.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: Varchar pkey instead of integer
Date: Wed, 21 May 2008 16:00:46 +0800
Message-ID: <4833D6AE.6060809@postnewspapers.com.au> (raw)
In-Reply-To: <4833D04E.8060403@Sheeky.Biz>
References: <36af4bed0805202240v5fbdd6bcv75643a6488cd1daf@mail.gmail.com>
<4833C285.5000603@postnewspapers.com.au>
<4833D04E.8060403@Sheeky.Biz>
Shane Ambler wrote:
> Size can affect performance as much as anything else. In your case of
> limited rows it will make little difference, though the larger table
> with millions of rows will have this key entered for each row and be
> indexed as the foreign key.
>
> The real question is how you want to use the column, if you wish to
> query for rows of a certain currency then you will notice the difference.
>
> You could use a smallint of 2 bytes each (or a varchar(1) with an int
> value instead of a real char) or an integer of 4 bytes, compared to your
> varchar(10)
... and if there are only a few records in the currency column, it
rarely changes, and you put a trigger in place to prevent the re-use of
previously assigned keys you may be able to cache that data in your
application.
That way you avoid a join or subquery on your lookup table to get the
text description of the currency AND get the storage/performance of a
small integer key.
It's something I'm doing in other places in my current DB where I have
essentially static lookup tables. You do have to watch out for lookup
table changes, though.
It's worth noting that my database is rather puny (the largest table has
500,000 records) and I'm very, very far from an expert on any of this,
so there might be some hidden downside to doing things this way that I
just haven't hit yet.
--
Craig Ringer
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: craig@postnewspapers.com.au
Subject: Re: Varchar pkey instead of integer
In-Reply-To: <4833D6AE.6060809@postnewspapers.com.au>
* 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