1

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?

3
  • 4
    OIDs are deprecated, don't use them. Use a bigserial
    – user1822
    Commented Feb 1, 2016 at 17:46
  • thanks horsie. I consider this an answer. So I should always have (OIDS=false)? Can you point out where the docs say it's deprecated? Commented Feb 1, 2016 at 17:49
  • 3
    @Andre "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." postgresql.org/docs/9.4/static/datatype-oid.html. I couldn't find reference to deprecated but atleast discouraged. Commented Feb 1, 2016 at 18:03

2 Answers 2

2

OIDS

From Docs http://www.postgresql.org/docs/9.4/static/datatype-oid.html

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)

http://www.postgresql.org/docs/9.5/static/datatype-numeric.html#DATATYPE-SERIAL

3

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:

default_with_oids (boolean)

This controls whether CREATE TABLE and CREATE TABLE AS include an OID column in newly-created tables, if neither WITH OIDS nor WITHOUT OIDS is specified. It also determines whether OIDs will be included in tables created by SELECT INTO. In PostgreSQL 8.1 default_with_oids is 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.

The obvious reason why @a_horse (correctly) suggested bigserial (and not just serial) is in the question:

Is there a downside to using them instead of creating a bigint field 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.