UUID in PostgreSQL

Generated Primary and Foreign Keys for Distributed Databases

ID Conditions:

  1. unique to identify table row across databases.
  2. ordered for efficient clustered index performance.

Examples

UUID is not effective when data is spread across multi-master replication (MMR) scenario. It fails condition #2 above.

-- to enable gen_random_uuid() function
CREATE EXTENSION pgcrypto;

CREATE SCHEMA IF NOT EXISTS core;

create table core.user (
	userPK UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
	created timestamp without time zone NOT NULL,
	updated timestamp without time zone NOT NULL
);

References

  1. UUID Primary Keys in PostgreSQL

  2. Enterprise PostgreSQL

  3. 2nd Alternative to UUID Generation appears to be based somewhat on #2.