On 2013-04-02, Joe Van Dyk <joe@xxxxxxxxx> wrote: > On Tue, Apr 2, 2013 at 11:16 AM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: >> On Tue, Apr 2, 2013 at 10:34 AM, Joe Van Dyk <joe@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
