Re: OIDs as keys

From: daniel alvarez <d-alvarez(at)gmx(dot)de>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: OIDs as keys
Date: 2003-02-26 14:59:35
Message-ID: 24135.1046271575@www36.gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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!

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2003-02-26 15:56:04 Re: OIDs as keys
Previous Message Richard Huxton 2003-02-26 13:58:53 Re: OIDs as keys