Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PostgreSQL Better ID & UUID Generator
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();
Output:
878162550749922330
Reference:
http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram
http://rob.conery.io/2014/05/29/a-better-id-generator-for-postgresql/
ALTER USER root WITH superuser;
CREATE EXTENSION "uuid-ossp";
SELECT uuid_generate_v4();
SELECT LOWER(REPLACE(CAST(uuid_generate_v4() As varchar(50)), '-',''));
Output:
adf4ce7c-4892-42bf-a808-1ee96bf9a6b0
43b2ac4eb63f46e6bfe93fe6bbb6267d
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.