SQL, Postgres OIDs, What are they and why are they useful?
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 isoff by default; in prior versions ofPostgreSQL, it was on by default.
The use of OIDs in user tables isconsidered deprecated, so mostinstallations should leave thisvariable disabled. Applications thatrequire OIDs for a particular tableshould specify WITH OIDS when creatingthe table. This variable can beenabled for compatibility with oldapplications that do not follow thisbehavior.
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 OIDScommanddefault_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.
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