X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id C1F1547580B for ; Wed, 26 Feb 2003 09:59:43 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 70684-08 for ; Wed, 26 Feb 2003 09:59:33 -0500 (EST) Received: from mx0.gmx.net (mx0.gmx.net [213.165.64.100]) by postgresql.org (Postfix) with SMTP id 77795474E5C for ; Wed, 26 Feb 2003 09:59:32 -0500 (EST) Received: (qmail 18882 invoked by uid 0); 26 Feb 2003 14:59:36 -0000 Date: Wed, 26 Feb 2003 15:59:35 +0100 (MET) From: daniel alvarez To: Richard Huxton Cc: pgsql-performance@postgresql.org MIME-Version: 1.0 References: <200302261358.53730.dev@archonet.com> Subject: Re: OIDs as keys X-Priority: 3 (Normal) X-Authenticated-Sender: #0002681999@gmx.net X-Authenticated-IP: [217.6.136.218] Message-ID: <24135.1046271575@www36.gmx.net> X-Mailer: WWW-Mail 1.6 (Global Message Exchange) X-Flags: 0001 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/211 X-Sequence-Number: 1267 > > 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 --=20 +++ GMX - Mail, Messaging & more http://www.gmx.net +++ Bitte l=E4cheln! Fotogalerie online mit GMX ohne eigene Homepage!