Sequential UUID Generation

I don't think PostgreSQL has anything like that at the moment. It would
not be difficult to tweak the UUID generator to generate sequential (or
monotonic) values, the tricky part seems to be durability requirements.

One idea would be to simply store the value in (shared) memory, but that
would mean losing the state on restart/crash, so there would need to be
some sort of protection against generating duplicate values (say, using
postmaster timestamp as the first 64 bits of the UUID).

Another idea is to piggy-back this on bigint sequence somehow - split
the 128bit range into 64+64, use the sequence value for the first 64b
and pick the other half by random. That would guarantee both uniqueness,
monotonicity and durability. And it would also be fairly random, making
it difficult to guess UUIDs.

regards

On 10/29/2018 04:06 PM, Uday Bhaskar V wrote:

Hi,

 We have migrated from Oracle to Postgres, there because of the
replication requirements we used UUID columns.
I did a POC(in postgres) with sequential UUID against Non sequential
which has shown lot of different in space utilization and index size.
Sql server has "newsequentialid" which generates sequential UUID.  I
need similar functionality here. 
I want to create a function which generates a sequential UUIDs, Any
suggestions or support would be much appreciated.   

Thanks,
Uday

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Thanks Tomas! I will try.

Regards,
Uday

On Tue, Oct 30, 2018 at 6:43 PM Tomas Vondra <tomas.vondra@2ndquadrant.com>
wrote:

I would be very interested in a extension which generated sequential uuids.
My entire db is key'd with uuids, and I have measured some index bloat
related specifically to random uuid generation.

Thanks for bringing this up.

I came across a project for time based UUID ("tuid") a little while back:
https://github.com/tanglebones/pg_tuid

I haven't used in production but skimmed through the code a bit out of
technical curiosity. It handles some of the expected edge cases for
backwards clock drift and concurrent generation.

The repo includes a PG extension and sample app code for generating tuids
in a couple languages as well as a pure-SQL one (though that one uses
random() rather than get_random_bytes() so I'd consider it more of an proof
of concept).

Regards,
-- Sehrope Sarkuni
Founder & CEO | JackDB, Inc. | https://www.jackdb.com/

I tried below function as which can be used as default to column. But every
time we need to created 2 sequences, 1st one takes care of the first 8
bytes and 2nd takes care of the 2nd part of the UUID. I have not tested
index and space utilization. I have to examine this. This might not be
completely unique in the nature. but still trying for the best.

CREATE OR REPLACE FUNCTION public.fnu_generate_sequential_uuid(
sequence1 text,
sequence2 text)
RETURNS uuid
LANGUAGE 'plpgsql'
COST 100.0
VOLATILE NOT LEAKPROOF
AS $function$

DECLARE
sequenceUUIDPart1 text;
randomUUIDPart2 text;
counter integer:=0;
significantByte integer:=0;
startIndex integer:=0;
endIndex integer:=0;
BEGIN

-- Get random UUID
randomUUIDPart2 := replace(( uuid_generate_v4 () :: text),'-','');

-- verify first sequence reached max count.
IF to_hex(currval(sequence1)) :: text = '7fffffffffffffff' THEN
startIndex:=0;
endIndex:=7;
-- convert sequence into 32 bit string
sequenceUUIDPart1 = rpad(to_hex(nextval(sequence2))::text, 32, '0');
ELSE
startIndex:=8;
endIndex:=15;
-- convert sequence into 32 bit string
sequenceUUIDPart1 = rpad(to_hex(nextval(sequence1))::text, 32, '0');
END IF;

RAISE NOTICE 'current Guid: %', sequenceUUIDPart1;

-- loop through the 8th byte to 16th byte, till first sequence max .
-- loop through the 0 to 7 the byte till second sequence end.
FOR counter IN startIndex..endIndex LOOP

select get_byte(decode(sequenceUUIDPart1::text,'hex'), counter) into
significantByte;

-- fill last 8 bytes with the generated random UUID values.
sequenceUUIDPart1 := encode(set_byte(decode(sequenceUUIDPart1
::text,'hex') :: bytea ,counter, significantByte ) :: bytea, 'hex') :: text;
RAISE NOTICE 'current Guid: %', sequenceUUIDPart1;
END LOOP;

return sequenceUUIDPart1 :: UUID;
EXCEPTION
WHEN OTHERS
THEN
RAISE EXCEPTION 'An error was encountered in
create_engagement_data_get_aud_area_ent_list - % -ERROR- %', sqlstate,
sqlerrm;
END

$function$;

On Wed, Oct 31, 2018 at 1:51 AM Sehrope Sarkuni <sehrope@jackdb.com> wrote:

On 11/19/18 2:08 PM, Uday Bhaskar V wrote:

I tried below function as which can be used as default to column. But
every time we need to created 2 sequences, 1st one takes care of the
first 8 bytes and 2nd takes care of the 2nd part of the UUID. I have not
tested index and space utilization. I have to examine this. This might
not be completely unique in the nature. but still trying for the best.

I got a bit bored a couple of days ago, so I wrote an extension
generating UUIDs based on either a sequence or timestamp. See

https://blog.2ndquadrant.com/sequential-uuid-generators/

for an explanation and some simple test results.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services