SQL, Postgres OIDs, What are they and why are they useful?

Question

I am looking at some PostgreSQL table creation and I stumbled upon this:

CREATE TABLE (
...
) WITH ( OIDS = FALSE );

I read the documentation provided by postgres and I know the concept of object identifier from OOP but still I do not grasp,

• why such identifier would be useful in a database?
• to make queries shorter?
• when should it be used?

Solution 1

OIDs basically give you a built-in id for every row, contained in a system column (as opposed to a user-space column). That's handy for tables where you don't have a primary key, have duplicate rows, etc. For example, if you have a table with two identical rows, and you want to delete the oldest of the two, you could do that using the oid column.

OIDs are implemented using 4-byte unsigned integers. They are not unique–OID counter will wrap around at 2³²-1. OID are also used to identify data types (see /usr/include/postgresql/server/catalog/pg_type_d.h).

In my experience, the feature is generally unused in most postgres-backed applications (probably in part because they're non-standard), and their use is essentially deprecated:

In PostgreSQL 8.1 default_with_oids is off by default; in prior versions of PostgreSQL, it was on by default.

The use of OIDs in user tables is considered deprecated, so most installations should leave this variable disabled. Applications that require OIDs for a particular table should specify WITH OIDS when creating the table. This variable can be enabled for compatibility with old applications that do not follow this behavior.

Solution 2

OIDs being phased out

The core team responsible for Postgres is gradually phasing out OIDs.

Postgres 12 removes special behavior of OID columns

The use of OID as an optional system column on your tables is now removed from Postgres 12. You can no longer use:

• CREATE TABLE … WITH OIDS command
• default_with_oids (boolean) compatibility setting

The data type OID remains in Postgres 12. You can explicitly create a column of the type OID.

After migrating to Postgres 12, any optionally-defined system column oid will no longer be invisible by default. Performing a SELECT * will now include this column. Note that this extra “surprise” column may break naïvely written SQL code.

Solution 3

OID's are still in use for Postgres with large objects (though some people would argue large objects are not generally useful anyway). They are also used extensively by system tables. They are used for instance by TOAST which stores larger than 8KB BYTEA's (etc.) off to a separate storage area (transparently) which is used by default by all tables. Their direct use associated with "normal" user tables is basically deprecated.

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.

Apparently the OID sequence "does" wrap if it exceeds 4B 6. So in essence it's a global counter that can wrap. If it does wrap, some slowdown may start occurring when it's used and "searched" for unique values, etc.

See also https://wiki.postgresql.org/wiki/FAQ#What_is_an_OID.3F

Solution 4

To remove all OIDs from your database tables, you can use this Linux script:

First, login as PostgreSQL superuser:

sudo su postgres

Now run this script, changing YOUR_DATABASE_NAME with you database name:

for tbl in `psql -qAt -c "select schemaname || '.' || tablename from pg_tables WHERE schemaname <> 'pg_catalog' AND schemaname <> 'information_schema';" YOUR_DATABASE_NAME` ; do  psql -c "alter table $tbl SET WITHOUT OIDS" YOUR_DATABASE_NAME ; done

I used this script to remove all my OIDs, since Npgsql 3.0 doesn't work with this, and it isn't important to PostgreSQL anymore.

Solution 5

If you still use OID, it would be better to remove the dependency on it, because in recent versions of Postgres it is no longer supported. This can stop (temporarily until you solve it) your migration from version 10 to 12 for example.

See also: https://dev.to/rafaelbernard/postgresql-pgupgrade-from-10-to-12-566i

View more solutions

Comments

[Basil Bourque]

I cannot find any references to cite at the moment, but FYI I have heard that using Microsoft Access as a front-end to Postgres requires the presence of the old system column.

[radiospiel]

oids are not guaranteed to be unique. From the docs: "In a large or long-lived database, it is possible for the counter to wrap around. Hence, it is bad practice to assume that OIDs are unique, unless you take steps to ensure that this is the case."

[Carl G]

The wrapping around also implies that you could not necessarily delete the older of two rows based only on their OID, as the one with the lower OID may have been a wrap-around.

[FuzzyChef]

OIDs are not globally unique, per comments above, nor were they in 2011 when this answer was written. Also, OIDs are needed for system objects, so using up all of the OIDs on row counters doesn't help the database with assigning OIDs to new tables (for the table, not its rows). Also, consider whether a singe 4-byte integer counter is really going to be sufficient for every table in your database.

[vdegenne]

it is worth to mention, in most implementation of phpPgAdmin when creating a table, the option is checked disable as default, meaning in a matter of fact that this option is deprecated.

[vdegenne]

if you don't know what OIDs are used for then you probably don't want to be using them.

[paul23]

@radiospiel in which case one could argue only variable length fields can be unique; and even that is dubious given limited ram. Given limited amounts of memory all possible combinations of bits is used at "some point", making the id no longer unique.

[Bill Bunting]

pg_upgrade will fail if tables have OIDs. This includes upgrades via pg_upgrade command line, or attempts to upgrade an AWS RDS PostgreSQL database with OIDs (by snapshot upgrade) using the AWS console. "pg_upgrade does not support upgrading of databases containing these reg* OID-referencing system data types: regproc, regprocedure, regoper, regoperator, regconfig, and regdictionary. (regtype can be upgraded.)" - postgresql.org/docs/10/pgupgrade.html