Skip to content

Instantly share code, notes, and snippets.

@colophonemes
Last active March 8, 2024 01:50
Show Gist options
  • Save colophonemes/42b4167632a96a78a985fd14b4b0c013 to your computer and use it in GitHub Desktop.
Save colophonemes/42b4167632a96a78a985fd14b4b0c013 to your computer and use it in GitHub Desktop.
Postgres Distributed Key Generation

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)
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()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment