Message-ID: <4833C285.5000603@postnewspapers.com.au>
Date: Wed, 21 May 2008 14:34:45 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Thunderbird 2.0.0.14 (Windows/20080421)
MIME-Version: 1.0
To: Robins Tharakan <tharakan@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Varchar pkey instead of integer
References: <36af4bed0805202240v5fbdd6bcv75643a6488cd1daf@mail.gmail.com>
In-Reply-To: <36af4bed0805202240v5fbdd6bcv75643a6488cd1daf@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Robins Tharakan wrote:
> Hi,
> 
> I am currently designing a database and wanted to know something that may
> sound trivial, but I thought its still good to confirm before dumping
> millions of rows in it.
> 
> The design requires a few master tables with very limited rows, for e.g.
> currency_denomination table could at the max have a few records like million
> / billion / crore (used in india) / lacs (india specific) and so on.
> 
> Now what I wanted to ask was whether its any different to have the
> primary-keys in such master tables as text/varchar rather than integer ?

As far as I know it's just slower to compare (ie for fkey checks, index 
lookups, etc) and uses more storage. However, if you're only using the 
other table to limit possible values in a field rather than storing 
other information and you can avoid doing a join / index lookup by 
storing the string directly in the master table then that might well be 
worth it. It's a tradeoff between the storage cost (seq scan speed, 
index size, etc) of using the text values directly vs the savings made 
by avoiding having to constantly hit a lookup table.

I have several places in the database I'm presently working on where I 
store meaningful integers directly in a "main" table and reference a 
single-field table as a foreign key just to limit acceptable values. It 
works very well, though it's only suitable in limited situations.

One of the places I'm doing that is for in-database postcode validation. 
My current app only needs to validate Australian post codes (as per the 
spec) and other post/zip codes are just stored in the address text. I 
store the integer representation of the post code directly in address 
records but use a foreign key to the single-field "aust_post_code" table 
to enforce the use of only valid postcodes. There's an ON DELETE SET 
NULL cascade on the fkey because for this app's purpose a postcode 
that's no longer accepted by the postal service is bad data.

This means that the postcode list can't be updated by a TRUNCATE and 
repopulate. No big deal; I prefer to do a compare between the current 
database contents and the latest postcode data and insert/delete as 
appropriate anyway; especially as the app needs to be able to record and 
flag tentative entries for postcodes that the user *insists* exist but 
the latest (possibly even weeks old) australia post data says do not.

You could reasonably do the same sort of thing with a text postcode if 
your app had to care about non-numeric postal codes.

It's nice being able to work on something that doesn't have to handle 
pedal-post in some awful corner of the earth where they identify postal 
regions by coloured tags. OK, not really, but sometimes addressing seems 
almost that bad.

> i.e. Can I use a character varying(10) and use the text 'million' /
> 'billion' instead of a serial / integer type ?

If you're looking at a small set of possible values an enumeration 
*might* be an option. Be aware that they're painful and slow to change 
later, though, especially when used in foreign keys, views, etc.

I certainly wouldn't use one for your currency denomination table, which 
is likely to see values added to it over time.

> p.s.: I am not as much concerned with the size that it'd take on the data
> tables, as much as the fact that the select / insert performances shouldn't
> suffer. However, if that increase in size (per data record) may make a
> considerable impact on the performance, I would certainly want to take that
> into account during design phase.

I suspect it's just another tradeoff - table size increase (and thus 
scan performance cost) from storing the text vs avoiding the need to 
access the lookup table for most operations.

--
Craig Ringer