1 Creating a sequence

Each table requires a separate sequence to be created

  • The purpose of the sequence is to quickly generate the snowflakeId. Each time the newly generated snowflakeId is saved in the sequence, it is then used to calculate the new snowflakeId.
  • The sequence is named seq_” table name “_id
drop sequence if exists seq_table_id;
create sequence if not exists seq_table_id 
as bigint
	increment 1
    minvalue 0	
	maxvalue 9223372036854775807
	start 0
	cache 1;
Reset sequence
select setval('seq_table_id'::regclass,0);
Copy the code

2 Create the snowflakeId function

  • GenerateId depends on the sequence
  • GenerateId is a public function that takes the sequence of the table as the first argument and the machine number as the second argument (values range 0-1023)

2.1 Creating and generating the snowflakeId function (uninx timestamp)

  • Because the UNINX timestamp is used, it is used up to 2039-09-07 23:47:35.551+08
drop function if exists generateId(regclass,int);
create or replace function generateId(regclass,int)
    returns bigint
as $$
    --4095 =0x0000000000000FFF
    --4190208 =0x00000000003FF000
	select 
        (case when (sn>>22)=milliseconds and ((sn&4190208)>>12)=$2 then	
            setval($1,(milliseconds<<22)|($2<<12)|((sn&4095)))    Sn nextval already incremented by 1
        else
            setval($1,(milliseconds<<22)|($2<<12)|1)
        end)
    from (select nextval($1) as sn,(extract(epoch from clock_timestamp())*1000) : :bigint as milliseconds) as tmp;
$$ language sql;
Copy the code

2.2 Creating and generating the snowflakeId function (time offset starts from 2021-01-01)

  • The time offset starts from 2021-01-01 and can be used up to 2090-09-07 15:47:35.551+08
drop function if exists generateIdOffset(regclass,int);
create or replace function generateIdOffset(regclass,int)
    returns bigint
as $$
	- 1609430400000 = 2021-01-01 00:00:00. 000000
    --4095 =0x0000000000000FFF
    --4190208 =0x00000000003FF000
	select 
        (case when (sn>>22)=milliseconds and ((sn&4190208)>>12)=$2 then	
            setval($1,(milliseconds<<22)|($2<<12)|((sn&4095)))    Sn nextval already incremented by 1
        else
            setval($1,(milliseconds<<22)|($2<<12)|1)
        end)
    from (select nextval($1) as sn,(extract(epoch from clock_timestamp())*1000) : :bigint- 1609430400000.  as milliseconds) as tmp;
$$ language sql;
Copy the code

3 test

SnowflakeId generates about 90 bytes per millisecond

3.1 UnINx timestamp

select 
	id,id>>22,(id&4190208)>>12,id&4095
from(
    select generateId('public.seq_table_id'::regclass,1) as id from generate_series(1.10000))as tmp;
Copy the code

3.2 The time offset starts from 2021-01-01

select 
	id,1609430400000 +(id>>22),(id&4190208)>>12,id&4095
from(
    select  generateIdOffset('public.seq_table_id'::regclass,1) as id from generate_series(1.10000))as tmp;
Copy the code