Twitter Snowflake ID for PostgreSQL

snowflake-id.sql

CREATE SEQUENCE IF NOT EXISTS public.snowflake_id_seq
    MINVALUE 0 MAXVALUE 1023
    CYCLE;
ALTER SEQUENCE public.global_id_seq OWNER TO postgres;

-- 41 bits for time in milliseconds
-- (41 years of IDs from epoch)
-- 13 bits for the logical shard ID
-- 10 bits for the global auto-incrementing sequence (modulus 1024).
-- This means we can generate 1024 IDs, per shard, per millisecond
-- TODO: Figure out how to store config vars outside of function
CREATE OR REPLACE FUNCTION public.id_generator()
    RETURNS bigint
AS $$
DECLARE
    our_epoch bigint := 1314220021721;
    seq_id bigint;
    now_millis bigint;
    -- the id of this DB shard, must be set for each
    -- schema shard you have - you could pass this as a parameter too
    shard_id int := 0;
    result bigint := 0;
BEGIN
    SELECT nextval('public.snowflake_id_seq') 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;

ALTER FUNCTION public.id_generator() OWNER TO postgres;