pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: daniel alvarez <d-alvarez@gmx.de>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: OIDs as keys
Date: Wed, 26 Feb 2003 15:59:35 +0100 (MET)
Message-ID: <24135.1046271575@www36.gmx.net> (raw)
References: <200302261358.53730.dev@archonet.com>


> > 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!




view thread (26+ messages)  latest in thread

Message-ID: <24135.1046271575@www36.gmx.net>
Permalink:  ../24135.1046271575@www36.gmx.net/
Also on:    postgresql.org/message-id/24135.1046271575@www36.gmx.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: d-alvarez@gmx.de, dev@archonet.com
  Subject: Re: OIDs as keys
  In-Reply-To: <24135.1046271575@www36.gmx.net>
  References: <200302261358.53730.dev@archonet.com> <24135.1046271575@www36.gmx.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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