Last active
April 5, 2023 18:47
-
-
Save chinshr/6b995b60156902d6362b60a5ee633080 to your computer and use it in GitHub Desktop.
Snowflake'ish Id generation with PostgreSQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Modified from https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c | |
-- And https://gist.github.com/yohang88/f950fbb239192866895a | |
-- And https://rextester.com/NPNU50638 | |
CREATE OR REPLACE FUNCTION message.next_id() RETURNS TRIGGER AS $$ | |
DECLARE | |
our_epoch bigint := 1314220021721; | |
seq_id bigint; | |
now_millis bigint; | |
-- the id of this DB shard is either hard coded, or | |
-- here, `message_id` is used instead | |
shard_id int; | |
result bigint; | |
BEGIN | |
IF NEW.message_id IS NULL THEN | |
-- hard code to 1, or whatever | |
SELECT 1 INTO shard_id; | |
ELSE | |
SELECT NEW.message_id % 8192 INTO shard_id; | |
END IF; | |
SELECT nextval('message.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); | |
NEW.id = result; | |
return NEW; | |
END; | |
$$ LANGUAGE PLPGSQL; | |
CREATE TABLE messages ( | |
id BIGINT NOT NULL PRIMARY KEY, | |
message_id BIGINT NOT NULL | |
); | |
CREATE TRIGGER trigger_insert_messages_id | |
BEFORE INSERT | |
ON messages | |
FOR EACH ROW | |
EXECUTE PROCEDURE message.next_id(); | |
INSERT INTO messages(message_id) values(1); | |
INSERT INTO messages(message_id) values(2); | |
INSERT INTO messages(message_id) values(2); | |
SELECT * FROM messages; | |
/* | |
id | message_id | |
---------------------+------------ | |
3074421248655098886 | 1 | |
3074421458806507527 | 2 | |
3074421558270232584 | 2 | |
(3 rows) | |
*/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment