I'm looking at a need for surrogate keys in tables I'm designing on a PostgreSQL-derived DBMS (Citus). Would OIDs suffice? Is there a downside to using them instead of creating a
bigint field and a sequence?
The oid type is currently implemented as an unsigned four-byte integer. Therefore, it is not large enough to provide database-wide uniqueness in large databases, or even in large individual tables. So, using a user-created table's OID column as a primary key is discouraged. OIDs are best used only for references to system tables.
bigint and a sequence
As "_a_horse_with no_name" suggested use bigserial
- smallserial 2 bytes
- serial 4 bytes
- bigserial 8 bytes
Notice: smallserial and serial don't seem to overcome the size limitation of 4 bytes specified above. Probably someone else can fill in on that.
The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases)
There are a couple of misunderstandings floating around here.
OIDs were included with every row by default in very old versions of Postgres. The default was soon changed to not include OID columns in user-defined tables.
Quoting the Postgres 8.1 documentation:
This controls whether
CREATE TABLE ASinclude an OID column in newly-created tables, if neither
WITHOUT OIDSis specified. It also determines whether OIDs will be included in tables created by
SELECT INTO. In PostgreSQL 8.1
default_with_oidsis disabled by default; in prior versions of PostgreSQL, it was on by default.
Bold emphasis mine. What @Shire quoted is related, but OIDs are not even considered for a PK in modern Postgres.
Is there a downside to using them instead of creating a
bigintfield and a sequence?
Bold emphasis mine again. Typically, a
serial column (implemented with a 4-byte integer) is good enough for most tables - unless you expect more than 2 billion (
2^31 - 1 = 2.147.483.647) rows over the lifetime of the table.