This post describes the primary key function that I created that is part of an open-source platform using Postgres. The platform will allow us to create enterprise systems that have an inherent capability to exchange data between systems and automatically aggregate data from many systems into a data warehouse for reporting.
The primary key function is baked into the schema definitions which are automatically created by the platform using a central data dictionary. Whenever new records are created, the function is called to generate a globally unique primary key. This key greatly simplifies the process of moving data from one system to the next and automatically aggregating data into a data warehouse because once it is assigned to data, it never has to change.
The primary key is made up of two components – a system id which is unique to the system that created the record and a record id which is an incremented integer for the table it is generating the key in.
The function combines the two components into a bigint variable. It uses both the positive and negative portions of the bigint value to maximize the number of unique values that can be stored in the key. The key will support 1.5 million system ids and a quadrillion record ids.
The key benefits of this primary key are:
- It is half the size of a GUID.
- It causes data to be stored sequentially which reduces bloating and increases retrieval performance.
- It identifies the system that created the record which is important information for the record governance function. Record governance tells us which system owns the record. When a system creates a record, it automatically governs it. Governance can be transferred from one system to the next.
- The key is easy for users and developers to reference 1-123 unlike a GIUD (2109338f-448b-411a-89e8-c6320d28b52a).
The primary key function may be useful to systems you are creating that require you to move data from one system to the next.
Please check out https://www.3d-ess.com/primary-keys. It includes the source code plus a video that discusses the architecture of the key in more detail.
I appreciate your comments, questions, and likes.
Thank you for your attention.
Comments