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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
