instagram snowflake id

snowflake_id.pgsql

CREATE SCHEMA snowflake;

CREATE DOMAIN snowflake.id AS bigint;

CREATE SEQUENCE snowflake.table_id_seq;

CREATE OR REPLACE FUNCTION snowflake.next_id(shard_id smallint default 1)
RETURNS snowflake.id
AS $$
DECLARE
    our_epoch bigint := 1690000000000; -- July 22, 2023 GMT
    seq_id bigint;
    result snowflake.id;
    now_millis bigint;
BEGIN
    SELECT nextval('snowflake.table_id_seq') % 1024 INTO seq_id;

    SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
    result := (now_millis - our_epoch) << 23;
    result := result | (shard_id << 10);
    result := result | (seq_id);
    RETURN result;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION snowflake.next_id(smallint)
  IS 'Modifications made to require shard id';


CREATE OR REPLACE FUNCTION snowflake.get_shard(id snowflake.id)
RETURNS smallint
AS $$
  SELECT ((id<<41)>>51)::smallint;
$$ LANGUAGE sql
IMMUTABLE;

COMMENT ON FUNCTION snowflake.get_shard(snowflake.id)
  IS '13 bits from snowflake.id representing shard';


CREATE OR REPLACE FUNCTION snowflake.get_sequence(id snowflake.id)
RETURNS smallint
AS $$
  SELECT ((id<<54)>>54)::smallint;
$$ LANGUAGE sql
IMMUTABLE;

COMMENT ON FUNCTION snowflake.get_sequence(snowflake.id)
  IS '10 bits from snowflake.id representing sequence';


CREATE OR REPLACE FUNCTION snowflake.get_ts(id snowflake.id)
RETURNS timestamp without time zone
AS $$
  SELECT to_timestamp(((id >> 23) + 1690000000000 ) / 1000 )::timestamp without time zone;
$$ LANGUAGE sql
IMMUTABLE;

COMMENT ON FUNCTION snowflake.get_ts(snowflake.id)
  IS '41 bits from snowflake.id representing timestamp';