Postgres Distributed Key Generation

README.md

Postgres Distributed Key Generation

This SQL creates a Postgres function to generate sequential, numeric, unique IDs in a consistent format across services. Useful for database sharding or microservices.

Draws heavily on Instagram's ID generator, via Rob Conery, with minor modifications.

The main changes are that the unique number resolution is per-second rather than per-millisecond. This is to reduce key size below 2^53^-1 so that generated IDs that are under Javascripts Number.MAX_SAFE_INTEGER limit . This is important if you're using these on a Node.js server (e.g. our use case is an Express API using Hashids).

Max IDs are in the order of 51 bits, broken down as follows:

• 31 bits for the timestamp difference
• 10 bits for a unique service ID (eg ID of the service/shard)
• 10 bits for a global sequence ID (modulo 1024 — meaning a maximum of 1024 unique records per second)

id_generator.sql

CREATE SEQUENCE global_id_sequence;
CREATE FUNCTION generate_id(OUT result bigint) AS $$
  DECLARE
    -- TO START IDS SMALLER, YOU COULD CHANGE THIS TO A MORE RECENT UNIX TIMESTAMP
    our_epoch bigint := 1483228800;

    seq_id bigint;
    now_millis bigint;
    -- UNIQUE SERVICE IDENTIFIER
    -- CHANGE THIS FOR EACH SERVICE!!!
    service_id int := 1;
  BEGIN
      SELECT nextval('global_id_sequence') % 1024 INTO seq_id;
      SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp())) INTO now_millis;
      result := (now_millis - our_epoch) << 20;
      result := result | (service_id << 10);
      result := result | (seq_id);
  END;
$$ LANGUAGE PLPGSQL;

-- Assign as the default value for the id column on a table (e.g. mytable)
ALTER TABLE mytable ALTER COLUMN id SET DEFAULT generate_id()