pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOID Perfomance - Object-Relational databases
21+ messages / 7 participants
[nested] [flat]
* OID Perfomance - Object-Relational databases
@ 2000-10-03 16:06 Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 21+ messages in thread
From: Josh Berkus @ 2000-10-03 16:06 UTC (permalink / raw)
To: pgsql-sql; +Cc: Jeff MacDonald <jeff@pgsql.com>
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* RE: OID Perfomance - Object-Relational databases
@ 2000-10-03 16:17 Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
0 siblings, 1 reply; 21+ messages in thread
From: Michael Ansley @ 2000-10-03 16:17 UTC (permalink / raw)
To: 'josh@agliodbs.com' <josh@agliodbs.com>; pgsql-sql; +Cc: Jeff MacDonald <jeff@pgsql.com>
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
>>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: OID Perfomance - Object-Relational databases
@ 2000-10-03 16:35 Mitch Vincent <mitch@venux.net>
parent: Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
0 siblings, 0 replies; 21+ messages in thread
From: Mitch Vincent @ 2000-10-03 16:35 UTC (permalink / raw)
To: Michael Ansley <Michael.Ansley@intec-telecom-systems.com>; +Cc: pgsql-sql
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
> >>
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* RE: OID Perfomance - Object-Relational databases
@ 2000-10-03 16:35 Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
0 siblings, 0 replies; 21+ messages in thread
From: Michael Ansley @ 2000-10-03 16:35 UTC (permalink / raw)
To: 'Mitch Vincent' <mitch@venux.net>; +Cc: pgsql-sql
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
>> > >>
>> >
>>
^ permalink raw reply [nested|flat] 21+ messages in thread
* RE: OID Perfomance - Object-Relational databases
@ 2000-10-03 16:54 Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
0 siblings, 0 replies; 21+ messages in thread
From: Michael Ansley @ 2000-10-03 16:54 UTC (permalink / raw)
To: pgsql-sql
Regarding all of this, can someone tell me in a fairly technical manner what
the difference is between searching for a row using it's primary key, and
searching using it's OID. Rumour has it that it's faster. Technically,
what's the difference?
Cheers...
-----Original Message-----
From: Michael Ansley [mailto:Michael.Ansley@intec-telecom-systems.com]
Sent: 03 October 2000 17:36
To: 'Mitch Vincent'
Cc: sqllist
Subject: RE: [SQL] 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 <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
<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
>> > >>
>> >
>>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: OID Perfomance - Object-Relational databases
@ 2000-10-03 19:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 21+ messages in thread
From: Tom Lane @ 2000-10-03 19:10 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql; Jeff MacDonald <jeff@pgsql.com>
Josh Berkus <josh@agliodbs.com> 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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: OID Perfomance - Object-Relational databases
@ 2000-10-04 00:33 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 21+ messages in thread
From: Josh Berkus @ 2000-10-04 00:33 UTC (permalink / raw)
To: ; +Cc: pgsql-sql; Jeff MacDonald <jeff@pgsql.com>
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: OID Perfomance - Object-Relational databases
@ 2000-10-04 01:26 Michael Fork <mfork@toledolink.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 21+ messages in thread
From: Michael Fork @ 2000-10-04 01:26 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: OID Perfomance - Object-Relational databases
@ 2000-10-04 03:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Fork <mfork@toledolink.com>
0 siblings, 0 replies; 21+ messages in thread
From: Tom Lane @ 2000-10-04 03:36 UTC (permalink / raw)
To: Michael Fork <mfork@toledolink.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-sql
Michael Fork <mfork@toledolink.com> 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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: OID Perfomance - Object-Relational databases
@ 2000-10-04 03:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 21+ messages in thread
From: Tom Lane @ 2000-10-04 03:43 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql; Jeff MacDonald <jeff@pgsql.com>
Josh Berkus <josh@agliodbs.com> 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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: OID Perfomance - Object-Relational databases
@ 2000-10-04 04:10 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 21+ messages in thread
From: Josh Berkus @ 2000-10-04 04:10 UTC (permalink / raw)
To: pgsql-sql; +Cc: pgman@candle.pha.pa.us
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* RE: OID Perfomance - Object-Relational databases
@ 2000-10-04 09:27 Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
0 siblings, 0 replies; 21+ messages in thread
From: Michael Ansley @ 2000-10-04 09:27 UTC (permalink / raw)
To: 'Josh Berkus' <josh@agliodbs.com>; pgsql-sql
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
>>
>>
>>
>>
>>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: OID Perfomance - Object-Relational databases
@ 2000-10-04 11:43 Jeff MacDonald <jeff@pgsql.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 21+ messages in thread
From: Jeff MacDonald @ 2000-10-04 11:43 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql; Jeff MacDonald <jeff@pgsql.com>
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* RE: Re: OID Perfomance - Object-Relational databases
@ 2000-10-04 12:59 Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
0 siblings, 0 replies; 21+ messages in thread
From: Michael Ansley @ 2000-10-04 12:59 UTC (permalink / raw)
To: pgsql-sql
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...
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: OID Perfomance - Object-Relational databases
@ 2000-10-09 01:56 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 21+ messages in thread
From: Bruce Momjian @ 2000-10-09 01:56 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
[ 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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: OID Perfomance - Object-Relational databases
@ 2000-10-09 17:34 Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 21+ messages in thread
From: Josh Berkus @ 2000-10-09 17:34 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: OID Perfomance - Object-Relational databases
@ 2000-10-09 19:04 Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 21+ messages in thread
From: Josh Berkus @ 2000-10-09 19:04 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql; jeff@pgsql.com
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: OID Perfomance - Object-Relational databases
@ 2000-10-09 19:19 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 21+ messages in thread
From: Bruce Momjian @ 2000-10-09 19:19 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql; jeff@pgsql.com
[ 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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: OID Perfomance - Object-Relational databases
@ 2000-10-09 20:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 21+ messages in thread
From: Tom Lane @ 2000-10-09 20:14 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-sql; jeff@pgsql.com
"Josh Berkus" <josh@agliodbs.com> 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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: OID Perfomance - Object-Relational databases
@ 2000-10-09 22:44 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 21+ messages in thread
From: Josh Berkus @ 2000-10-09 22:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-sql; jeff@pgsql.com
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: OID Perfomance - Object-Relational databases
@ 2000-10-09 23:30 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 21+ messages in thread
From: Bruce Momjian @ 2000-10-09 23:30 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql; jeff@pgsql.com
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
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2000-10-09 23:30 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-10-03 16:06 OID Perfomance - Object-Relational databases Josh Berkus <josh@agliodbs.com>
2000-10-03 19:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-10-04 00:33 ` Josh Berkus <josh@agliodbs.com>
2000-10-04 01:26 ` Michael Fork <mfork@toledolink.com>
2000-10-04 03:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-10-04 03:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-10-04 04:10 ` Josh Berkus <josh@agliodbs.com>
2000-10-09 01:56 ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-10-04 11:43 ` Jeff MacDonald <jeff@pgsql.com>
2000-10-03 16:17 RE: OID Perfomance - Object-Relational databases Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
2000-10-03 16:35 ` Mitch Vincent <mitch@venux.net>
2000-10-03 16:35 RE: OID Perfomance - Object-Relational databases Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
2000-10-03 16:54 RE: OID Perfomance - Object-Relational databases Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
2000-10-04 09:27 RE: OID Perfomance - Object-Relational databases Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
2000-10-04 12:59 RE: Re: OID Perfomance - Object-Relational databases Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
2000-10-09 17:34 Re: OID Perfomance - Object-Relational databases Josh Berkus <josh@agliodbs.com>
2000-10-09 19:04 Re: OID Perfomance - Object-Relational databases Josh Berkus <josh@agliodbs.com>
2000-10-09 19:19 ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-10-09 20:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-10-09 22:44 ` Josh Berkus <josh@agliodbs.com>
2000-10-09 23:30 ` Bruce Momjian <pgman@candle.pha.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox