Re: Using varchar primary keys.

On 2013-04-02, Joe Van Dyk <joe(at)tanga(dot)com> wrote:
> On Tue, Apr 2, 2013 at 11:16 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> On Tue, Apr 2, 2013 at 10:34 AM, Joe Van Dyk <joe(at)tanga(dot)com> wrote:
>> >
>> > I've been wishing for a smaller uuid type for a while.
>> > I've been using a unique text column with a default of
>> > random_characters(12)
>>
>> This is pseudo random and can be guessed, which is maybe dangerous
>> depending on circumstance. For stronger random stream go to
>> pgcrypto.gen_random_bytes().
>
> Right, but it's better than using serial's as far as being guessable.
>
> The probability for collisions are fairly low, if you are using 12 or more
> characters (with 30 possible characters). Not sure what the math is on the
> probability of collisions (birthday problem) though..

for n samples of p possibles it's approximately

n(n-1)/2p

for n^2 < p


a alphabet of 30 symbols is almost 5 bits per symbol
so for 12 symbols you get about 60 bits which almost half as wide as a
UUID (128b)

> and you could have a
> trigger that checked for the existence of a matching key before
> inserts/updates.

And the associated race condition... no thanks.

you could encrypt a serial type using some reversible encryption
eg: there's a feistel cypher implemented in plpgsql in the wiki
somewhere

>> My historical comments in this debate are noted. To summarize, I
>> strongly believe that natural keys are often (but not always) better.

I'll use natural keys where they are short enough to be useful and
guaranteed constant.

--
⚂⚃ 100% natural