Skip site navigation (1) Skip section navigation (2)

Re: Is a randomized default value primary key

From: Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net>
To: PgSQL Novice ML <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Is a randomized default value primary key
Date: 2002-05-20 00:18:28
Message-ID: 1021853908.10284.123.camel@rebel (view raw or flat)
Thread:
Lists: pgsql-novice
On Sun, 2002-05-19 at 17:19, April L wrote:
> I made the primary key
> 
> "authkey"	bigint DEFAULT trunc(random()*10^15) NOT NULL
> 
> Does that seem reasonable? bigint is 8 bytes. I came up with this prior to
> receiving your reply. 
> 
> 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.

You are right that more indexes means more work.  Another 
benefit of pseudo-random numbers is that keys will be inserted
into the tree in, well, pseudo-random order...  So what?  Keys
that are inserted into the tree in ascending order all get 
inserted into the right side of the tree.  Therefore, postgres
must do extra work to keep the tree balanced.  (That's the B in
b-tree).  Random numbers get inserted all over the tree, thus
minimizing the work needed to keep the tree balanced.

If your transactions are SERIALIZABLE, then, since ascending 
order keys all get inserted into the right side of the tree,
all users are trying to insert into the same nodes, thus causing
rollbacks.  Keys that go all over the tree will minimize this
problem.

-- 
+---------------------------------------------------------+
| Ron Johnson, Jr.        Home: ron(dot)l(dot)johnson(at)cox(dot)net     |
| Jefferson, LA  USA      http://ronandheather.dhs.org:81 |
|                                                         |
| "I have created a government of whirled peas..."        |
|   Maharishi Mahesh Yogi, 12-May-2002,                   |
!   CNN, Larry King Live                                  |
+---------------------------------------------------------+


In response to

pgsql-novice by date

Next:From: Henshall, Stuart - WCPDate: 2002-05-20 08:47:38
Subject: Re: Accessing table attributes from within PlPgSQL
Previous:From: Ron JohnsonDate: 2002-05-19 23:33:48
Subject: Accessing table attributes from within PlPgSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group