Re: OIDs as keys - Mailing list pgsql-performance

From daniel alvarez
Subject Re: OIDs as keys
Date
Msg-id 24135.1046271575@www36.gmx.net
Whole thread Raw
In response to Re: OIDs as keys  (Richard Huxton <dev@archonet.com>)
Responses Re: OIDs as keys  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: OIDs as keys  (Richard Huxton <dev@archonet.com>)
Re: OIDs as keys  (Josh Berkus <josh@agliodbs.com>)
List pgsql-performance
> > I am considering the option of placing an index on the already existing
oid
> > and using it as the primary key for all tables (saves some space and a
sequence
> > lookup). This includes saving the oid in foreign keys (virtual ones, not
actually
> > declared references). I read that using OID in keys is generally a bad
idea.
> > Is it really? Why exactly?

> OIDs are not even guaranteed to be there any more - you can create a table
> WITHOUT OIDs if you want to save some space. If you want a numeric primary
> key, I'd recommend int4/int8 attached to a sequence - it's much clearer
what's
> going on then.

Of course this is a cleaner solution. I did not know that oids can be
supressed and
was looking for a way to make space usage more efficient. Trying to get rid
of user-
defined surrogate primary keys and substitute them by the already existing
OID is
obviously the wrong approch, as postgres already defines a cleaner option.

There can also be some problems when using replication, because one needs to
make
sure that OIDs are the same on all machines in the cluster.

Why should user-defined tables have OIDs by default? Other DBMS use ROWIDs
as the physical storage location used for pointers in index leafs, but this
is equivalent
to Postgres TIDs. To the user an OID column is not different than any other
column
he can define himself. I'd find it more natural if the column wasn't there
at all.

Daniel Alvarez

--
+++ GMX - Mail, Messaging & more  http://www.gmx.net +++
Bitte lächeln! Fotogalerie online mit GMX ohne eigene Homepage!


pgsql-performance by date:

Previous
From: Richard Huxton
Date:
Subject: Re: OIDs as keys
Next
From: Tom Lane
Date:
Subject: Re: OIDs as keys