Re: OID Perfomance - another question

Re: OID Perfomance - another question

Aren't there a pretty big concerns when using OIDs as IDs to relate records
in different tables to each other? Wouldn't the OIDs be totally re-assigned
if you had to dump/restore your database?

Just a question to satisfy my own curiosity, thanks!

-Mitch

> Folks,
>
> Because it's a very elegant solution to my database structure issues,
> I'm using OID's extensively as referents and foriegn keys.  However, I
> wanted to see if others had previous experience in this (answer as many
> as you like):
>
> 1. Is there a performance loss on searches and joins when I use the OID
> as a liniking field as opposed to a SERIAL column?

Re: OID Perfomance - Object-Relational databases

Aren't OIDs just integers? Isn't this limit just the limit of the value an
int4 can hold?

2,147,483,647 is the max for an int4 (I think) so at 500 million a day
you're looking at more like  4.29 (and change) days

If I'm correct in all the above, there wouldn't be any way to increase the
limit without the server running on a 64-bit machine (which you could do, I
guess)..

*shrug* just some thoughts..

-Mitch

----- Original Message -----
From: "Michael Ansley" 
To: ; "sqllist" 
Cc: "Jeff MacDonald" 
Sent: Tuesday, October 03, 2000 9:17 AM
Subject: RE: [SQL] OID Perfomance - Object-Relational databases


> Hi, Josh,
>
> In fact, the last point about OIDs is particularly pertinent, because we
are
> expected to process up to 500 million records daily, thus exhausting the
> limit in, um, eight days.
>
> Is anybody aware of when this limit will be raised.
>
> Cheers...
>
> >>   -----Original Message-----
> >>   From: Josh Berkus [mailto:josh@agliodbs.com]
> >>   Sent: 03 October 2000 17:06
> >>   To: sqllist
> >>   Cc: Jeff MacDonald
> >>   Subject: [SQL] OID Perfomance - Object-Relational databases
> >>
> >>
> >>   Folks,
> >>
> >>   Because it's a very elegant solution to my database
> >>   structure issues,
> >>   I'm using OID's extensively as referents and foriegn keys.
> >>    However, I
> >>   wanted to see if others had previous experience in this
> >>   (answer as many
> >>   as you like):
> >>
> >>   1. Is there a performance loss on searches and joins when
> >>   I use the OID
> >>   as a liniking field as opposed to a SERIAL column?
> >>
> >>   2. Can I define my own index on the OIDs of a table?
> >>
> >>   3. What is the difference between these two DDL statements
> >>   in terms of
> >>   data access and PG-SQL performance (assuming that table clients has
> >>   already been defined):
> >>
> >>   CREATE TABLE client_addresses AS (
> >>   client_OID OID REFERENCES clients,
> >>   address1 VARCHAR (30),
> >>   address2 VARCHAR (30),
> >>   address3 VARCHAR (30)
> >>   )
> >>   and:
> >>   CREATE TABLE client_addresses AS (
> >>   client clients,
> >>   address1 VARCHAR (30),
> >>   address2 VARCHAR (30),
> >>   address3 VARCHAR (30)
> >>   )
> >>
> >>   (This is Michael's questions rephrased)
> >>
> >>   4. Int4 seems kinda small to me for a value that needs to enumerate
> >>   every single database object.  Within a couple of years of
> >>   heavy use, a
> >>   customer-transaction database could easily exceed 2 billion objects
> >>   created (and destroyed).  Are there plans to expand this to Int8?
> >>
> >>   -Josh Berkus
> >>
> >>   P.S. My aplolgies if I've already posted these questions; I never
> >>   received them back from the list mailer.
> >>
> >>
> >>
> >>   --
> >>   ______AGLIO DATABASE SOLUTIONS___________________________
> >>                                           Josh Berkus
> >>      Complete information technology      josh@agliodbs.com
> >>       and data management solutions       (415) 436-9166
> >>      for law firms, small businesses       fax  436-0137
> >>       and non-profit organizations.       pager 338-4078
> >>                           San Francisco
> >>
>

Re: OID Perfomance - Object-Relational databases

[ Charset ISO-8859-1 unsupported, converting... ]
> Tom,
> 
> > The trouble with pg_dump -o is that after reload, the OID
> > generator
> > will be set to max(any OID in the dumped data).  So a
> > dump & reload
> > doesn't do anything to postpone OID-wraparound Ragnarok.
> > 
> > As for the likelihood of overflow, figure 4G / tuple
> > creation rate
> > for your installation (not database, but whole
> > installation controlled
> > by one postmaster).  Unless your installation has just
> > one active
> > table, per-table sequence values look like a better bet.
> 
> Somebody (urgently) needs to tell all of the above to Bruce
> Momjian (I've cc'd him); his book-in-the-making points up
> OID's as a convenient and universal way to identify and link
> tuples (chapter 7) and doen't mention these problems.  Who
> can I bug about how useless the above makes OID's?
> 

Well, you know, everyone complains about wrap-around, but no one has
ever reported it happening.  It is like the Y2K thing where everyone
thought they would starve.  Please, someone tell me they have had had
OID rollover, and I will start doing something about it.

Also, 500 million transactions a day?  Seems impossible to me.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610) 853-3000 +  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: OID Perfomance - Object-Relational databases

[ Charset ISO-8859-1 unsupported, converting... ]
> Bruce, Tom, etc.:
> 
> > > This is what I mean.  Does the DB engine only recycle
> > > *unused* OIDs (that is, does it check for teh continued
> > > existance of a tuple with OID 198401)?  If that's the
> > > method, then there isn't really a problem even if I do
> > use
> > > OIDs as a primary index.  None of my OIDs still in use
> > will
> > > be touched.
> > 
> > 
> > No, it uses all oids, and can create duplicates.
> 
> Does this mean that Tom's "Wraparound Ragnarok" is the
> accurate scenario?

Yes.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610) 853-3000 +  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: OID Perfomance - Object-Relational databases

There is an FAQ item about oid's already on the web site.  It may not
be in 7.0.2.

> Tom, Bruce,
> 
> 	Thanks.  I think that gives me a pretty clear picture.  How can we
> submit this whole OID thing to the PGSQL FAQ?  Want me to write it up?
> 
> 					-Josh Berkus
> 
> P.S. BTW, my conclusion based on this discussion is that I will not use
> the OIDs for indexing/linking.  Instead, I will embrace Michael's
> earlier suggestion and have already created universal_sq and started
> migrating primary keys to that sequence.  
> 
> P.P.S. Thank you both for taking the time to hash out this issue.
> 
> 
> -- 
> ______AGLIO DATABASE SOLUTIONS___________________________
>                                         Josh Berkus
>    Complete information technology      josh@agliodbs.com
>     and data management solutions       (415) 436-9166
>    for law firms, small businesses       fax  436-0137
>     and non-profit organizations.       pager 338-4078
>                         		San Francisco
> 


--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610) 853-3000 +  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: OID Perfomance - Object-Relational databases

Michael Fork  writes:
> Just a thought, but couldn't you create a sequence that is referenced by
> multiple tables,

I was going to suggest exactly that.  It's not quite as simple as a
"serial" column declaration, but you can split the use of an ID sequence
generator over just as many tables as you need to have unique IDs
across.  That way you don't have a problem at dump/reload time,
and you don't exhaust your ID space any faster than you must.
		regards, tom lane

Re: OID Perfomance - Object-Relational databases

Josh Berkus  writes:
> SO I'm concerned about the problems you mentioned above.  pg_dump has a
> -o option; are there problems with this?  And how liekly are counter
> overflow problems?

The trouble with pg_dump -o is that after reload, the OID generator
will be set to max(any OID in the dumped data).  So a dump & reload
doesn't do anything to postpone OID-wraparound Ragnarok.

As for the likelihood of overflow, figure 4G / tuple creation rate
for your installation (not database, but whole installation controlled
by one postmaster).  Unless your installation has just one active
table, per-table sequence values look like a better bet.

BTW, there *is* talk of providing an 8-byte-OID option, but I'm not
holding my breath for it.
		regards, tom lane

Re: OID Perfomance - Object-Relational databases

Josh Berkus  writes:
> 1. Is there a performance loss on searches and joins when I use the OID
> as a liniking field as opposed to a SERIAL column?

> 2. Can I define my own index on the OIDs of a table?

There is absolutely no magic about OIDs, except that the system insists
on adding one to every row you store.  In particular, they don't offer
any magic fast way to find a tuple.  If you want fast retrieval by OID
in a particular table then you *MUST* define an index on the OID column,
like so:

CREATE TABLE foo ( ... );

CREATE INDEX foo_oid_index ON foo (oid);

The performance of an index on OID will be indistinguishable from the
performance of an index on an int4 or serial column.

By and large I'd recommend using a serial column in preference to OIDs,
though, for two reasons:

1. dump/restore is more practical that way (don't have to worry about  saving/reloading OIDs).

2. counter overflow problems hit you only per-table, not  per-installation.
		regards, tom lane

OID Perfomance - Object-Relational databases

Folks,
Because it's a very elegant solution to my database structure issues,
I'm using OID's extensively as referents and foriegn keys.  However, I
wanted to see if others had previous experience in this (answer as many
as you like):

1. Is there a performance loss on searches and joins when I use the OID
as a liniking field as opposed to a SERIAL column?

2. Can I define my own index on the OIDs of a table?

3. What is the difference between these two DDL statements in terms of
data access and PG-SQL performance (assuming that table clients has
already been defined):

CREATE TABLE client_addresses AS (client_OID	OID	REFERENCES clients,address1	VARCHAR (30),address2	VARCHAR (30),address3	VARCHAR (30))
and:
CREATE TABLE client_addresses AS (client		clients,address1	VARCHAR (30),address2	VARCHAR (30),address3	VARCHAR (30))

(This is Michael's questions rephrased)

4. Int4 seems kinda small to me for a value that needs to enumerate
every single database object.  Within a couple of years of heavy use, a
customer-transaction database could easily exceed 2 billion objects
created (and destroyed).  Are there plans to expand this to Int8?

-Josh Berkus

P.S. My aplolgies if I've already posted these questions; I never
received them back from the list mailer.


-- 
______AGLIO DATABASE SOLUTIONS___________________________                                       Josh Berkus  Complete information technology      josh@agliodbs.com   and data management solutions       (415) 436-9166  for law firms, small businesses       fax  436-0137   and non-profit organizations.       pager 338-4078                       		San Francisco

Re: OID Perfomance - Object-Relational databases

Tom,

> By and large I'd recommend using a serial column in preference to OIDs,
> though, for two reasons:
> 
> 1. dump/restore is more practical that way (don't have to worry about
>    saving/reloading OIDs).
> 
> 2. counter overflow problems hit you only per-table, not
>    per-installation.

Hmmm ... for some tables, switching to Serial would work.  However, one
of the things I've done is add universal mod_data (modification stats)
and notes tables, which have to relate via OID because they relate to
5-7 different tables.  To wit:

CREATE TABLE notes AS (ref_OID		OID,staff_OID	OID	REFERENCES staff,note_date	DATE,note_text	TEXT)

And the ref_oid relates to any of 5 different tables, thus allowing a
single table to hold notes on clients, candidates, bills, etc.  Very
elegant, and using serials instead of the OID not possible.

SO I'm concerned about the problems you mentioned above.  pg_dump has a
-o option; are there problems with this?  And how liekly are counter
overflow problems?
Josh Berkus


-- 
______AGLIO DATABASE SOLUTIONS___________________________                                       Josh Berkus  Complete information technology      josh@agliodbs.com   and data management solutions       (415) 436-9166  for law firms, small businesses       fax  436-0137   and non-profit organizations.       pager 338-4078                       		San Francisco

Re: OID Perfomance - Object-Relational databases

Tom, Bruce,
Thanks.  I think that gives me a pretty clear picture.  How can we
submit this whole OID thing to the PGSQL FAQ?  Want me to write it up?
				-Josh Berkus

P.S. BTW, my conclusion based on this discussion is that I will not use
the OIDs for indexing/linking.  Instead, I will embrace Michael's
earlier suggestion and have already created universal_sq and started
migrating primary keys to that sequence.  

P.P.S. Thank you both for taking the time to hash out this issue.


-- 
______AGLIO DATABASE SOLUTIONS___________________________                                       Josh Berkus  Complete information technology      josh@agliodbs.com   and data management solutions       (415) 436-9166  for law firms, small businesses       fax  436-0137   and non-profit organizations.       pager 338-4078                       		San Francisco

Re: OID Perfomance - Object-Relational databases

"Josh Berkus"  writes:
>> No, it uses all oids, and can create duplicates.

> Does this mean that Tom's "Wraparound Ragnarok" is the
> accurate scenario?

There's nothing particularly magic about OIDs.  If your programs
don't use OIDs to identify rows, then you don't care whether there
are duplicate OIDs or not.  If they do, you can still protect yourself
by creating a unique index on OID for the tables where you need OID to
be unique.  You might get unexpected insert failures due to OID conflict
after a wraparound, but the odds are in your favor (ie, retrying will
probably succeed).

Postgres itself assumes that OIDs are unique in some of the system
tables, but I believe we have unique indexes in place for the tables
where it matters.  So, again, transient insert failures seem like the
worst likely consequence ... annoying, but not exactly Ragnarok.

Transaction ID wraparound is a less pleasant scenario :-(.  You'd
probably be well advised to dump/reload your DB when you start to
approach 4G transactions through it.  I think we might be able to
fix this so that things are OK as long as you vacuum more frequently
than once per wraparound, but no one's looked at the problem yet.

As Bruce pointed out, we have yet to hear from anyone who's run a
Postgres DB long enough to run into either sort of wraparound, so
these issues don't seem all that pressing...
		regards, tom lane

RE: OID Perfomance - Object-Relational databases

Hi, Josh,
In fact, the last point about OIDs is particularly pertinent, because we are expected to process up to 500 million records daily, thus exhausting the limit in, um, eight days.
Is anybody aware of when this limit will be raised.
Cheers...
>>   -----Original Message-----
>>   From: Josh Berkus [mailto:josh@agliodbs.com]
>>   Sent: 03 October 2000 17:06
>>   To: sqllist
>>   Cc: Jeff MacDonald
>>   Subject: [SQL] OID Perfomance - Object-Relational databases
>>   
>>   
>>   Folks,
>>   
>>      Because it's a very elegant solution to my database 
>>   structure issues,
>>   I'm using OID's extensively as referents and foriegn keys. 
>>    However, I
>>   wanted to see if others had previous experience in this 
>>   (answer as many
>>   as you like):
>>   
>>   1. Is there a performance loss on searches and joins when 
>>   I use the OID
>>   as a liniking field as opposed to a SERIAL column?
>>   
>>   2. Can I define my own index on the OIDs of a table?
>>   
>>   3. What is the difference between these two DDL statements 
>>   in terms of
>>   data access and PG-SQL performance (assuming that table clients has
>>   already been defined):
>>   
>>   CREATE TABLE client_addresses AS (
>>      client_OID      OID     REFERENCES clients,
>>      address1        VARCHAR (30),
>>      address2        VARCHAR (30),
>>      address3        VARCHAR (30)
>>      )
>>   and:
>>   CREATE TABLE client_addresses AS (
>>      client          clients,
>>      address1        VARCHAR (30),
>>      address2        VARCHAR (30),
>>      address3        VARCHAR (30)
>>      )
>>   
>>   (This is Michael's questions rephrased)
>>   
>>   4. Int4 seems kinda small to me for a value that needs to enumerate
>>   every single database object.  Within a couple of years of 
>>   heavy use, a
>>   customer-transaction database could easily exceed 2 billion objects
>>   created (and destroyed).  Are there plans to expand this to Int8?
>>   
>>   -Josh Berkus
>>   
>>   P.S. My aplolgies if I've already posted these questions; I never
>>   received them back from the list mailer.
>>      
>>   
>>   
>>   -- 
>>   ______AGLIO DATABASE SOLUTIONS___________________________
>>                                           Josh Berkus
>>      Complete information technology      josh@agliodbs.com
>>       and data management solutions       (415) 436-9166
>>      for law firms, small businesses       fax  436-0137
>>       and non-profit organizations.       pager 338-4078
>>                                      San Francisco
>>   

RE: OID Perfomance - Object-Relational databases

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

RE: OID Perfomance - Object-Relational databases

RE: OID Perfomance - Object-Relational databases

I'm a little concerned about all this, because my understanding is that what makes an object database so fast is its ability to directly reference tuples, so that traversing relationships becomes like traversing pointers.
The achilles heel of relational databases is the inability to do exactly that.  Postgres has a sufficiently extended query language that traversing relationships in this manner can be dealt with, and doing this from a decent OO development tool should remove the general pain of dealing with OIDs.
Or is there something that I'm missing?  What I expect from an OR database is the speed of an object database (which seems to come mainly from it's OID mechanism), with the manipulative power of a traditional relational database, and then a whole truckload of extras, like rules, procedures, an OR query language, etc.  So, effectively, it's more than the sum of ODB and RDB.
In order to promote Postgres in the environment that I'm in now, I need it to attain at least the same order of speed as an ODB.  Are there any benchmarks anywhere that I can quote against ODBs?  I need some help here, because the general feeling that I'm up against is that we should be using either ODB or RDB, not ORDB as it is the worst of both worlds, being slow, not completely object-orientated, and not as flexible as ODBs, and less robust than RDBs, because of the object extensions.  I need some ammo.
MikeA


>>   -----Original Message-----
>>   From: Josh Berkus [mailto:josh@agliodbs.com]
>>   Sent: 04 October 2000 05:11
>>   To: pgsql-sql@postgresql.org
>>   Cc: pgman@candle.pha.pa.us
>>   Subject: Re: [SQL] OID Perfomance - Object-Relational databases 
>>   
>>   
>>   Tom,
>>   
>>   > The trouble with pg_dump -o is that after reload, the OID
>>   > generator
>>   > will be set to max(any OID in the dumped data).  So a
>>   > dump & reload
>>   > doesn't do anything to postpone OID-wraparound Ragnarok.
>>   > 
>>   > As for the likelihood of overflow, figure 4G / tuple
>>   > creation rate
>>   > for your installation (not database, but whole
>>   > installation controlled
>>   > by one postmaster).  Unless your installation has just
>>   > one active
>>   > table, per-table sequence values look like a better bet.
>>   
>>   Somebody (urgently) needs to tell all of the above to Bruce
>>   Momjian (I've cc'd him); his book-in-the-making points up
>>   OID's as a convenient and universal way to identify and link
>>   tuples (chapter 7) and doen't mention these problems.  Who
>>   can I bug about how useless the above makes OID's?
>>   
>>   Thanks for the warning, and thanks Michael for the
>>   suggestion; I'll use it and send you all back notes on how
>>   it affects performance.
>>   
>>   -Josh
>>   
>>   
>>   
>>   
>>   

RE: Re: OID Perfomance - Object-Relational databases

Given what we've been discussing over the past day or so, can anybody explain this to me:
dev=# explain select client.address.postcode from client;
NOTICE:  QUERY PLAN:
Seq Scan on client  (cost=0.00..1.01 rows=1 width=4)
EXPLAIN
dev=# select client.address.postcode from client;
ERROR:  init_fcache: Cache lookup failed for procedure 18935
Cheers...

Re: OID Perfomance - Object-Relational databases

Hi Josh,

I didn't get right tho this, because well.. you asked
the list, i figured i'd give them a chance first. and they've
answered the same as i would have..

> 	Because it's a very elegant solution to my database structure issues,
> I'm using OID's extensively as referents and foriegn keys.  However, I
> wanted to see if others had previous experience in this (answer as many
> as you like):
> 
> 1. Is there a performance loss on searches and joins when I use the OID
> as a liniking field as opposed to a SERIAL column?

yes, because indexes are automatcally created on serials.

> 2. Can I define my own index on the OIDs of a table?

sure, create index oid_idx on table(oid);


Jeff MacDonald,

-----------------------------------------------------
PostgreSQL Inc		| Hub.Org Networking Services
jeff@pgsql.com		| jeff@hub.org
www.pgsql.com		| www.hub.org
1-902-542-0713		| 1-902-542-3657
-----------------------------------------------------
Facsimile : 1 902 542 5386
IRC Nick  : bignose

Re: OID Perfomance - Object-Relational databases

Just a thought, but couldn't you create a sequence that is referenced by
multiple tables, ie:

CREATE SEQUENCE test_seq;

CREATE TABLE tblclients {client_id	default nextval('test_seq'::text),...}

CREATE TABLE tblbills {bill_id		default nextval('test_seq'::text),...}

and so on...

I have never tried this, but i don't see why it wouldn't work....

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

On Tue, 3 Oct 2000, Josh Berkus wrote:

> Tom,
> 
> > By and large I'd recommend using a serial column in preference to OIDs,
> > though, for two reasons:
> > 
> > 1. dump/restore is more practical that way (don't have to worry about
> >    saving/reloading OIDs).
> > 
> > 2. counter overflow problems hit you only per-table, not
> >    per-installation.
> 
> Hmmm ... for some tables, switching to Serial would work.  However, one
> of the things I've done is add universal mod_data (modification stats)
> and notes tables, which have to relate via OID because they relate to
> 5-7 different tables.  To wit:
> 
> CREATE TABLE notes AS (
> 	ref_OID		OID,
> 	staff_OID	OID	REFERENCES staff,
> 	note_date	DATE,
> 	note_text	TEXT
> 	)
> 
> And the ref_oid relates to any of 5 different tables, thus allowing a
> single table to hold notes on clients, candidates, bills, etc.  Very
> elegant, and using serials instead of the OID not possible.
> 
> SO I'm concerned about the problems you mentioned above.  pg_dump has a
> -o option; are there problems with this?  And how liekly are counter
> overflow problems?
> 	
> Josh Berkus
> 	
> 
> 
> -- 
> ______AGLIO DATABASE SOLUTIONS___________________________
>                                         Josh Berkus
>    Complete information technology      josh@agliodbs.com
>     and data management solutions       (415) 436-9166
>    for law firms, small businesses       fax  436-0137
>     and non-profit organizations.       pager 338-4078
>                         		San Francisco
> 

Re: OID Perfomance - another question

On Tue, 3 Oct 2000, Mitch Vincent wrote:

> Aren't there a pretty big concerns when using OIDs as IDs to relate records
> in different tables to each other? Wouldn't the OIDs be totally re-assigned
> if you had to dump/restore your database?

Not if you use the -o flag to pg_dump.
      -o     Dump object identifiers (OIDs) for every table.
-- 
Tod McQuillin

Re: OID Perfomance - Object-Relational databases

Tom,

> The trouble with pg_dump -o is that after reload, the OID
> generator
> will be set to max(any OID in the dumped data).  So a
> dump & reload
> doesn't do anything to postpone OID-wraparound Ragnarok.
> 
> As for the likelihood of overflow, figure 4G / tuple
> creation rate
> for your installation (not database, but whole
> installation controlled
> by one postmaster).  Unless your installation has just
> one active
> table, per-table sequence values look like a better bet.

Somebody (urgently) needs to tell all of the above to Bruce
Momjian (I've cc'd him); his book-in-the-making points up
OID's as a convenient and universal way to identify and link
tuples (chapter 7) and doen't mention these problems.  Who
can I bug about how useless the above makes OID's?

Thanks for the warning, and thanks Michael for the
suggestion; I'll use it and send you all back notes on how
it affects performance.

-Josh

Re: OID Perfomance - Object-Relational databases

ruce,
   > The oid counter is preserved with -o on reload. It is   > not reset.
   I'll let you and Tom duke this one out. :-) It's all
beyond   me. 
   > > 2. When OID's "wrap around" does the whole database
go   > > kablooie? If so, why hasn't it happened to anyone
yet?   > If   > > not, can you describe the system PGSQL uses to
allocate   > OIDs   > > once it gets to 2,147,xxx,xxx?   >    > oid's start getting re-used on wraparound.   > 
   This is what I mean. Does the DB engine only recycle   *unused* OIDs (that is, does it check for teh continued   existance of a tuple with OID 198401)? If that's the   method, then there isn't really a problem even if I do
use   OIDs as a primary index. None of my OIDs still in use
will   be touched.
   If OIDs start getting re-used regardless if they are
already   present, then, like Tom says, it's Ragnarok. But it
seems   like somebody would have increased the OID to INT8 if
that   were a prospect.
   -Josh Berkus
   P.S. Bruce, I'm sorry about not sending my comments on
your   book. Do you have any use for copy-editing comments from   the June 28th version, or are you already in pre-press?

Re: OID Perfomance - Object-Relational databases

Bruce, Tom, etc.:

> > This is what I mean.  Does the DB engine only recycle
> > *unused* OIDs (that is, does it check for teh continued
> > existance of a tuple with OID 198401)?  If that's the
> > method, then there isn't really a problem even if I do
> use
> > OIDs as a primary index.  None of my OIDs still in use
> will
> > be touched.
> 
> 
> No, it uses all oids, and can create duplicates.

Does this mean that Tom's "Wraparound Ragnarok" is the
accurate scenario?

-Josh Berkus