RE: OID Perfomance - Object-Relational databases

<p><font size="2">Unsigned, I think you can double it, although I haven't applied much thought to this, so I could well
bewrong.</font><br /><p><font size="2">>>   -----Original Message-----</font><br /><font size="2">>>  
From:Mitch Vincent [<a href="mailto:mitch@venux.net">mailto:mitch@venux.net</a>]</font><br /><font size="2">>>  
Sent:03 October 2000 17:35</font><br /><font size="2">>>   To: Michael Ansley</font><br /><font
size="2">>>  Cc: sqllist</font><br /><font size="2">>>   Subject: Re: [SQL] OID Perfomance -
Object-Relationaldatabases</font><br /><font size="2">>>   </font><br /><font size="2">>>   </font><br
/><fontsize="2">>>   Aren't OIDs just integers? Isn't this limit just the limit </font><br /><font
size="2">>>  of the value an</font><br /><font size="2">>>   int4 can hold?</font><br /><font
size="2">>>  </font><br /><font size="2">>>   2,147,483,647 is the max for an int4 (I think) so at 500
</font><br/><font size="2">>>   million a day</font><br /><font size="2">>>   you're looking at more like 
4.29(and change) days</font><br /><font size="2">>>   </font><br /><font size="2">>>   If I'm correct in
allthe above, there wouldn't be any way </font><br /><font size="2">>>   to increase the</font><br /><font
size="2">>>  limit without the server running on a 64-bit machine </font><br /><font size="2">>>   (which
youcould do, I</font><br /><font size="2">>>   guess)..</font><br /><font size="2">>>   </font><br /><font
size="2">>>  *shrug* just some thoughts..</font><br /><font size="2">>>   </font><br /><font
size="2">>>  -Mitch</font><br /><font size="2">>>   </font><br /><font size="2">>>   ----- Original
Message-----</font><br /><font size="2">>>   From: "Michael Ansley"
<Michael.Ansley@intec-telecom-systems.com></font><br/><font size="2">>>   To: <josh@agliodbs.com>;
"sqllist"<pgsql-sql@postgresql.org></font><br /><font size="2">>>   Cc: "Jeff MacDonald"
<jeff@pgsql.com></font><br/><font size="2">>>   Sent: Tuesday, October 03, 2000 9:17 AM</font><br /><font
size="2">>>  Subject: RE: [SQL] OID Perfomance - Object-Relational databases</font><br /><font
size="2">>>  </font><br /><font size="2">>>   </font><br /><font size="2">>>   > Hi,
Josh,</font><br/><font size="2">>>   ></font><br /><font size="2">>>   > In fact, the last point
aboutOIDs is particularly </font><br /><font size="2">>>   pertinent, because we</font><br /><font
size="2">>>  are</font><br /><font size="2">>>   > expected to process up to 500 million records daily,
</font><br/><font size="2">>>   thus exhausting the</font><br /><font size="2">>>   > limit in, um,
eightdays.</font><br /><font size="2">>>   ></font><br /><font size="2">>>   > Is anybody aware of
whenthis limit will be raised.</font><br /><font size="2">>>   ></font><br /><font size="2">>>   >
Cheers...</font><br/><font size="2">>>   ></font><br /><font size="2">>>   > >>   -----Original
Message-----</font><br/><font size="2">>>   > >>   From: Josh Berkus [<a
href="mailto:josh@agliodbs.com">mailto:josh@agliodbs.com</a>]</font><br/><font size="2">>>   > >>  
Sent:03 October 2000 17:06</font><br /><font size="2">>>   > >>   To: sqllist</font><br /><font
size="2">>>  > >>   Cc: Jeff MacDonald</font><br /><font size="2">>>   > >>   Subject:
[SQL]OID Perfomance - Object-Relational databases</font><br /><font size="2">>>   > >></font><br /><font
size="2">>>  > >></font><br /><font size="2">>>   > >>   Folks,</font><br /><font
size="2">>>  > >></font><br /><font size="2">>>   > >>   Because it's a very elegant
solutionto my database</font><br /><font size="2">>>   > >>   structure issues,</font><br /><font
size="2">>>  > >>   I'm using OID's extensively as referents and foriegn keys.</font><br /><font
size="2">>>  > >>    However, I</font><br /><font size="2">>>   > >>   wanted to see if
othershad previous experience in this</font><br /><font size="2">>>   > >>   (answer as many</font><br
/><fontsize="2">>>   > >>   as you like):</font><br /><font size="2">>>   > >></font><br
/><fontsize="2">>>   > >>   1. Is there a performance loss on searches and joins when</font><br /><font
size="2">>>  > >>   I use the OID</font><br /><font size="2">>>   > >>   as a liniking
fieldas opposed to a SERIAL column?</font><br /><font size="2">>>   > >></font><br /><font
size="2">>>  > >>   2. Can I define my own index on the OIDs of a table?</font><br /><font
size="2">>>  > >></font><br /><font size="2">>>   > >>   3. What is the difference
betweenthese two DDL statements</font><br /><font size="2">>>   > >>   in terms of</font><br /><font
size="2">>>  > >>   data access and PG-SQL performance (assuming that </font><br /><font
size="2">>>  table clients has</font><br /><font size="2">>>   > >>   already been
defined):</font><br/><font size="2">>>   > >></font><br /><font size="2">>>   > >>  
CREATETABLE client_addresses AS (</font><br /><font size="2">>>   > >>   client_OID OID REFERENCES
clients,</font><br/><font size="2">>>   > >>   address1 VARCHAR (30),</font><br /><font
size="2">>>  > >>   address2 VARCHAR (30),</font><br /><font size="2">>>   > >>  
address3VARCHAR (30)</font><br /><font size="2">>>   > >>   )</font><br /><font size="2">>>   >
>>  and:</font><br /><font size="2">>>   > >>   CREATE TABLE client_addresses AS (</font><br
/><fontsize="2">>>   > >>   client clients,</font><br /><font size="2">>>   > >>  
address1VARCHAR (30),</font><br /><font size="2">>>   > >>   address2 VARCHAR (30),</font><br /><font
size="2">>>  > >>   address3 VARCHAR (30)</font><br /><font size="2">>>   > >>  
)</font><br/><font size="2">>>   > >></font><br /><font size="2">>>   > >>   (This is
Michael'squestions rephrased)</font><br /><font size="2">>>   > >></font><br /><font size="2">>>  
>>>   4. Int4 seems kinda small to me for a value that </font><br /><font size="2">>>   needs to
enumerate</font><br/><font size="2">>>   > >>   every single database object.  Within a couple of years
of</font><br/><font size="2">>>   > >>   heavy use, a</font><br /><font size="2">>>   >
>>  customer-transaction database could easily exceed 2 </font><br /><font size="2">>>   billion
objects</font><br/><font size="2">>>   > >>   created (and destroyed).  Are there plans to expand
</font><br/><font size="2">>>   this to Int8?</font><br /><font size="2">>>   > >></font><br
/><fontsize="2">>>   > >>   -Josh Berkus</font><br /><font size="2">>>   > >></font><br
/><fontsize="2">>>   > >>   P.S. My aplolgies if I've already posted these </font><br /><font
size="2">>>  questions; I never</font><br /><font size="2">>>   > >>   received them back from the
listmailer.</font><br /><font size="2">>>   > >></font><br /><font size="2">>>   >
>></font><br/><font size="2">>>   > >></font><br /><font size="2">>>   > >>  
--</font><br/><font size="2">>>   > >>   ______AGLIO DATABASE
SOLUTIONS___________________________</font><br/><font size="2">>>   >
>>                                          Josh Berkus</font><br /><font size="2">>>   > >>     
Completeinformation technology      josh@agliodbs.com</font><br /><font size="2">>>   > >>       and
datamanagement solutions       (415) 436-9166</font><br /><font size="2">>>   > >>      for law firms,
smallbusinesses       fax  436-0137</font><br /><font size="2">>>   > >>       and non-profit
organizations.      pager 338-4078</font><br /><font size="2">>>   > >>                           San
Francisco</font><br/><font size="2">>>   > >></font><br /><font size="2">>>   ></font><br
/><fontsize="2">>>   </font>