April,
> Since I do have to use the authkey to find records, it seems I would still
> benefit by having an index for it even if I had a separate 4 byte primary
> key - so I don't understand how it would save resources or increase
> performance to avoid making this column the primary key? Admittedly, I
> don't understand indexes in depth yet, I just assumed that every additional
> index means additional housekeeping activities each time a record is
> changed or added.
Oh, yeah, you're right. I should have added the caveat that having the two
keys makes sense if you are doing a lot of JOINing on the primary key of this
table. If this table does not have a lot of key-related data in other
tables, the dual keying does not make sense. Since you seemed to be talking
about a central data table, I just assumed that it would have child tables.
And Joel has a good point, which I made somewhat obliquely: you need to be
prepared for key violation messages, with your method. If you expect many
thousands of records, you should do the key-checking up front:
1. Create a PL/pgSQL trigger function called random_authkey(),
2. Have function generate a random number, scan the table for duplicates, and
if found generate a replacement number and return it as NEW.authkey;
3. Create a BEFORE trigger which calls this function for each INSERT.
This should automate the process for you, although it will slow down your
inserts significantly due to the extra index scan required for each insert.
Depending on your level of activity, though, you will probably not even
notice.
--
-Josh Berkus