Globally Unique Primary Keys

Introduction
The primary key architecture is a cornerstone of our ability to automatically exchange data between systems and aggregate it for reporting.
The important features of the primary key are as follows:
Globally Unique
When a primary key is assigned by a system, it is globally unique within that table. As a result, we can transfer the data to any other system without worrying about primary key collisions. We can also identify records when they need to be updated.
Compact
The primary key uses the BIGINT data type, which requires only 8 bytes. This is exceptionally small for a primary key that is globally unique across systems.
Because it is small and efficient for retrieval, it can be used on every table in every system. This consistency greatly simplifies the architecture.
System Identification
The key contains the System ID of the system that created the record. This feature greatly simplifies record governance and provides useful audit information.
The current version of the primary key supports over 1 billion systems, with the ability to increase this capacity if required.
Record ID
The Record ID component uniquely identifies the record within its table and originating system. It uses PostgreSQL sequences to generate sequential values. Each table has its own sequence, which prevents two systems from generating the same primary key for records in that table. The current Record ID component supports over 8 billion unique IDs within a table for a given system.
Efficient Storage
Because the primary key has a sequential Record ID component, it supports efficient B-tree index insertion. This helps reduce the page splits and fragmentation associated with randomly distributed identifiers, improving index efficiency and retrieval performance.
Readability
The primary key can be displayed in a human-readable format. e.g. 300-0000001234. This makes it easy for developers and users to reference records. The format can also be used on external documents.
Operational Data and Metadata
The same primary key architecture is used for both operational data and metadata. Operational data includes the records used to run the business, while metadata defines the structure and operation of the enterprise system. Using the same identification mechanism for both provides consistency across the entire platform and allows metadata to be exchanged and managed using the same approach as operational data.
Version Control
The primary key architecture also supports version control for metadata. It allows metadata changes to be managed across different schema versions and development environments without creating primary key collisions.
Universal Identifier
Industries identify contracts, equipment, vehicles, books, buildings, and well sites in their own ways. These identifiers are often unsuitable as enterprise primary keys.
We combine the primary key of the table (which identifies the type of data) with the primary key of the record. This creates a universal identifier that can be stored in a QR code on a contract, piece of equipment, oil well, or any other item that needs to be referenced or linked to other data.
