Oracle exposes a physical address called ROWID. As long as a row is not relocated (no row migration), the ROWID for that row remains the same, allowing lookups even without a primary key.

PostgreSQL exposes a similar physical locator, ctid, which is a tuple address composed of (block_number, offset_in_block). Because PostgreSQL uses MVCC, an UPDATE typically creates a new tuple version, so the ctid will change whenever a row is updated or moved. Operations like VACUUM FULL or table rewrites also change tuple locations.

Neither Oracle ROWID nor PostgreSQL ctid should be used as a business key. They are physical locators that can change. Use a logical, immutable identifier instead.

Stable row identifiers in PostgreSQL

Common approaches for immutable identifiers include:

• Sequence-backed surrogate keys (bigserial)
• SQL-standard identity columns (GENERATED AS IDENTITY, PostgreSQL 10+)
• Legacy OIDs on user tables (32-bit; deprecated and removed in newer versions; not recommended)

Sequence-backed surrogate key (bigserial)

Creates a monotonically increasing 64-bit identifier. Prefer declaring it as the primary key.

CREATE TABLE t_seq (
    rid        bigserial PRIMARY KEY,
    c1         integer,
    c2         integer
);

INSERT INTO t_seq (c1, c2) VALUES (1, 2);
INSERT INTO t_seq (c1, c2) VALUES (1, 2);
INSERT INTO t_seq (c1, c2) VALUES (1, 2);

SELECT rid, c1, c2 FROM t_seq ORDER BY rid;
--  rid | c1 | c2
-- -----+----+----
--    1 |  1 |  2
--    2 |  1 |  2
--    3 |  1 |  2

SQL-standard identity column (PostgreSQL 10+)

Identity columns provide sequence-backed values managed by the system, with clearer DDL semantics than serial. Use ALWAYS too force system-provided values or BY DEFAULT to allow explicit inserts.

CREATE TABLE t_ident (
    rid        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    x          integer,
    y          integer
);

INSERT INTO t_ident (x, y) VALUES (1, 2);
INSERT INTO t_ident (x, y) VALUES (1, 2);
INSERT INTO t_ident (x, y) VALUES (1, 2);

SELECT rid, x, y FROM t_ident ORDER BY rid;
--  rid | x | y
-- -----+---+---
--    1 | 1 | 2
--    2 | 1 | 2
--    3 | 1 | 2

Legacy OIDs on user tables (not recommended)

The oid type is a 32-bit object identifier. It can represent up to approximately 4 billion distinct values, wich is insufficient for very large tables. OIDs on user tables have long been discouraged and CREATE TABLE ... WITH OIDS was removed in PostgreSQL 12.

\dT oid
--              List of data types
--   Schema   | Name |                Description
-- -----------+------+-------------------------------------------
--  pg_catalog| oid  | object identifier(oid), maximum 4 billion

Example (only on older releases where WITH OIDS is supported):

CREATE TABLE t_oids (
    a integer,
    b integer
) WITH OIDS;

CREATE UNIQUE INDEX t_oids_oid_uq ON t_oids (oid);

INSERT INTO t_oids (a, b) VALUES (1, 2);
INSERT INTO t_oids (a, b) VALUES (1, 2);
INSERT INTO t_oids (a, b) VALUES (1, 2);

SELECT oid, a, b FROM t_oids ORDER BY oid;
--   oid  | a | b
-- -------+---+---
--  16412 | 1 | 2
--  16413 | 1 | 2
--  16414 | 1 | 2

Why not ctid for keys

• ctid is a physical address (block, offset) and changes on UPDATE or table rewrite
• PostgreSQL’s MVCC makes new tuple versions; the old version is eventually vacuumed
• Queries that join or cache by ctid can silently break after updates

Use bigserial or identity columns for stable, logical row identifiers instead of ROWID/ctid.