Skip to content

Instantly share code, notes, and snippets.

@ichux
Last active September 18, 2018 19:43
Show Gist options
  • Save ichux/1b5d15129370341811fb12eb7e333917 to your computer and use it in GitHub Desktop.
Save ichux/1b5d15129370341811fb12eb7e333917 to your computer and use it in GitHub Desktop.
sharding id at instagram
-- 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;
-- 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 from the table
SELECT * FROM our_table ORDER BY id DESC LIMIT 100;
CREATE TABLE IF NOT EXISTS public.our_table (
id BIGINT DEFAULT public.next_id() NOT NULL,
enabled BOOLEAN NOT NULL
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment