OID vs SERIAL
Seeking informed opinion on what is better to use as a unique row id for
linking tables together in a normalized database, a SERIAL field or the
pgsql OID. This is for an intranet application with a small user base,
but I'd like to make it robust and scalable where it is easy to do so.
My conclusions so far:
OIDs:
Pros:
* They're already there; save a couple bytes per row
* Specific method to retrieve after INSERT (maybe faster than SELECT
on the sequence)
Cons:
* Not serial by table; hard to build linked table 'by hand'
* not pure SQL
SERIAL:
Pros:
* Based on fairly vanilla SQL
* Easier to reproduce all or part of a db on a dump/restore
Cons:
* Performance?
* Extra id field redundant
I'm sure I'm missing something, and I'm not entirely sure how to weight
the points I've got. Advice appreciated.
Please CC me. I subscribed to the digest.
Jay
The other disadvantage of oids is that since Postgres doesn't currently
support
DELETE COLUMN, if you want to use the work around of doing a SELECT INTO
another
table it doesn't work because the oids are changed. In general, until
Postgres supports a fuller range of schema change functions you will
find
certain things like this inconvenient. Of course even the above has
another
work around. Either way can be made to work fine. I actually prefer
OIDS, but
I would suggest you use serials. I swapped over from oids to serials and
I'm
in two minds whether I'm better off.
Jay Bloodworth wrote:
Show quoted text
Seeking informed opinion on what is better to use as a unique row id for
linking tables together in a normalized database, a SERIAL field or the
pgsql OID. This is for an intranet application with a small user base,
but I'd like to make it robust and scalable where it is easy to do so.
My conclusions so far:OIDs:
Pros:
* They're already there; save a couple bytes per row
* Specific method to retrieve after INSERT (maybe faster than SELECT
on the sequence)Cons:
* Not serial by table; hard to build linked table 'by hand'
* not pure SQLSERIAL:
Pros:
* Based on fairly vanilla SQL
* Easier to reproduce all or part of a db on a dump/restoreCons:
* Performance?
* Extra id field redundantI'm sure I'm missing something, and I'm not entirely sure how to weight
the points I've got. Advice appreciated.Please CC me. I subscribed to the digest.
Jay
************
Does anyone know if the big boys like Oracle, Informix, Sybase, DB2
and so on support something like OIDs? This would be an interesting
portability issue.
Dirk
Oracle uses ROWID
Dirk Lutzebaeck ha scritto:
Does anyone know if the big boys like Oracle, Informix, Sybase, DB2
and so on support something like OIDs? This would be an interesting
portability issue.Dirk
************
Jos�
Oracle has a rowid column automatically built into every row. Most vendors
suggest using primary keys for all tables instead of a "rowid".
-----Original Message-----
From: Dirk Lutzebaeck [SMTP:lutzeb@aeccom.com]
Sent: Monday, August 30, 1999 2:15 AM
To: Jay Bloodworth
Cc: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] OID vs SERIAL
Does anyone know if the big boys like Oracle, Informix, Sybase, DB2
and so on support something like OIDs? This would be an interesting
portability issue.
Dirk
************
Import Notes
Resolved by subject fallback