8

A snowflake-style id generator for postgresql

 2 years ago
source link: https://thenorthmemory.github.io/post/a-snowflake-style-id-generator-for-postgresql/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

A snowflake-style id generator for postgresql

2019-07-06

在各种群里潜水，看到一条消息说解决分布式数据库ID自增解决方案——twitter snowflake，遂搜索并学习了一下，发现真的挺好，ID算法能保证使用69年。。。够用了。。。够用了！

snowflake 算法原版见 这里，相当精妙的算法。

以下是摘录自 Rob Conery 的英文博文，实现得也相当赞，记录下来，以备不时之需。

create schema shard_1;
create sequence shard_1.global_id_sequence;

CREATE OR REPLACE FUNCTION shard_1.id_generator(OUT result 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 := 1;
BEGIN
    SELECT nextval('shard_1.global_id_sequence') % 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;

select shard_1.id_generator();
create table shard_1.users(
  id bigint not null default id_generator(),
  email varchar(255) not null unique,
  first varchar(50),
  last varchar(50)
)

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK