sharding id at instagram

function.sql

-- create the needed sequence
CREATE SEQUENCE table_id_seq;

-- change the public to schema of choice
CREATE OR REPLACE FUNCTION public.next_id(OUT result bigint) AS $$
DECLARE
    our_epoch bigint := 1314220021721;
    seq_id bigint;
    now_millis bigint;
    shard_id int := 5;
BEGIN
    SELECT nextval('table_id_seq'::regclass) % 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);
END;
    $$ LANGUAGE PLPGSQL;

inserts.sql

-- insert into the table
INSERT INTO public.our_table (enabled) VALUES(TRUE) RETURNING id;
INSERT INTO public.our_table (enabled) VALUES(False) RETURNING id;

select.sql

-- select from the table
SELECT * FROM our_table ORDER BY id DESC LIMIT 100;

table.sql

CREATE TABLE IF NOT EXISTS public.our_table (
    id BIGINT DEFAULT public.next_id() NOT NULL,
    enabled BOOLEAN NOT NULL
);

z-reference.md

Modified from

ichux

Modified by