pg-id: generates random-looking never repeating primary key ids and more

This library contains PostgreSQL functions which generate bigint ids having the following format (with defaults from pg-id-consts.sql.example):

EssssRRRRRRRRRRRRRR
 ^   ^^^^^^^^^^^^^^
 4   14

Here,

• "E" is an "environment number" (e.g. denoting dev, test, staging, production...). The range is 1..7. The current value of the environment should be returned by function id_env_no(): you need to create it beforehand in the schema where you install the library.
• "ssss" is a 4-digit "microshard number". The range is 0..0999. The current value of the microshard should be returned by function id_shard_no(): you need to create it beforehand in the schema where you install the library.
• "RRRRRRRRRRRRRR" is a value which is unique within the chosen "environment number" and "microshard number". Depending on the function used, it is either a randomly-looking number (for id_gen()), a number based on the current timestamp (for id_gen_timestampic()) or just an auto-incrementing number (for id_gen_monotonic()).

Installation

First, copy pg-id-consts.sql.example to pg-id-consts.sql and update CONST_MUL, CONST_SUM and CONST_MOD with some random numbers that only you know. Those numbers will play the role of crypto constants which will not allow people to easily guess the pattern of ids generated by id_gen().

Then run in psql console:

-- Selects the schema where you want to install the library.
SET search_path TO your-schema;
-- Create pre-requisite configuration functions.
CREATE OR REPLACE FUNCTION id_env_no() RETURNS integer LANGUAGE sql
  SET search_path FROM CURRENT AS 'SELECT 1';
CREATE OR REPLACE FUNCTION id_shard_no() RETURNS integer LANGUAGE sql
  SET search_path FROM CURRENT AS 'SELECT 123';
-- Install the library.
\ir .../pg-id-up.sql

id_gen()

Generates next globally-unique randomly-looking id. The main idea is to not let external people infer the rate at which the ids are generated, even when they look at some ids sample.

The function implicitly uses id_seq sequence to get the information about the next available number, and then uses Feistel cipher to generate a randomly-looking non-repeating id based of it.

Examples of ids generated (underscores are just for illustration):

• 2_0000_17217633124378: "environment 2, shard 0, number 17217633124378"
• 1_0238_17493700363834: "environment 1, shard 238, number 17493700363834"

id_gen_timestampic()

Similar to id_gen(), but instead of generating randomly looking ids, prepends the "sequence" part of the id with the current timestamp (actually, the number of seconds since 2010-01-01 UTC which is 9 decimal digits, i.e. +17 years from 2023). The function reserves up to 5 decimal digits for the number part of the id, so within each second, up to 100k unique ids can be generated.

The function implicitly uses id_seq_timestampic sequence to get the information about the next available number.

The benefit of this function is performance: increasing ids are more friendly to heavy INSERTs since they maximize the chance for btree index to reuse the newly created leaf pages. At the same time, having timestamp in the prefix doesn't allow to infer the number of objects existing in the database so far.

Example of id generated (underscores are just for illustration):

• 2_0001_435044939_00029: "environment 2, xshard 1, seconds 435044939, number 29"

id_gen_monotonic()

The simplest and fastest function among the above: generates next globally-unique monotonic id, without using any timestamps as a prefix. Monotonic ids are more friendly to heavy INSERTs since they maximize the chance for btree index to reuse the newly created leaf pages.

The function implicitly uses id_seq_monotonic sequence to get the information about the next available number.

Example of id generated (underscores are just for illustration):

• 2_0001_00000000000003: "environment 2, shard 1, number 3"

The downside is that the ids of this format basically expose the number of unique objects which were created in the database so far.