Skip to content

Instantly share code, notes, and snippets.

@kjmph
Last active December 5, 2024 04:54
Show Gist options
  • Save kjmph/5bd772b2c2df145aa645b837da7eca74 to your computer and use it in GitHub Desktop.
Save kjmph/5bd772b2c2df145aa645b837da7eca74 to your computer and use it in GitHub Desktop.
Postgres PL/pgSQL function for UUID v7 and a bonus custom UUID v8 to support microsecond precision as well. Read more here: https://datatracker.ietf.org/doc/draft-peabody-dispatch-new-uuid-format/
-- Based off IETF draft, https://datatracker.ietf.org/doc/draft-peabody-dispatch-new-uuid-format/
create or replace function uuid_generate_v7()
returns uuid
as $$
begin
-- use random v4 uuid as starting point (which has the same variant we need)
-- then overlay timestamp
-- then set version 7 by flipping the 2 and 1 bit in the version 4 string
return encode(
set_bit(
set_bit(
overlay(uuid_send(gen_random_uuid())
placing substring(int8send(floor(extract(epoch from clock_timestamp()) * 1000)::bigint) from 3)
from 1 for 6
),
52, 1
),
53, 1
),
'hex')::uuid;
end
$$
language plpgsql
volatile;
-- Generate a custom UUID v8 with microsecond precision
create or replace function uuid_generate_v8()
returns uuid
as $$
declare
timestamp timestamptz;
microseconds int;
begin
timestamp = clock_timestamp();
microseconds = (cast(extract(microseconds from timestamp)::int - (floor(extract(milliseconds from timestamp))::int * 1000) as double precision) * 4.096)::int;
-- use random v4 uuid as starting point (which has the same variant we need)
-- then overlay timestamp
-- then set version 8 and add microseconds
return encode(
set_byte(
set_byte(
overlay(uuid_send(gen_random_uuid())
placing substring(int8send(floor(extract(epoch from timestamp) * 1000)::bigint) from 3)
from 1 for 6
),
6, (b'1000' || (microseconds >> 8)::bit(4))::bit(8)::int
),
7, microseconds::bit(8)::int
),
'hex')::uuid;
end
$$
language plpgsql
volatile;
PERFORMANCE: Move from pgcrypto to built-in gen_random_uuid():
Curtis Summers (https://github.com/csummers)
PERFORMANCE: Use set_bit to upgrade v4 to v7, not set_byte:
PERFORMANCE: Reduce local variable use while still being maintainable
Rolf Timmermans (https://github.com/rolftimmermans)
Copyright 2023 Kyle Hubert <kjmph@users.noreply.github.com> (https://github.com/kjmph)
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
@qwesda
Copy link

qwesda commented Jan 31, 2023

In case anybody in interested: I made a function to extract the timestamp from the uuid.

CREATE OR REPLACE FUNCTION public.timestamp_from_uuid_v7(_uuid uuid)
RETURNS timestamp without time zone
LANGUAGE sql
IMMUTABLE PARALLEL SAFE STRICT LEAKPROOF
AS $$
  SELECT to_timestamp(('x0000' || substr(_uuid::text, 1, 8) || substr(_uuid::text, 10, 4))::bit(64)::bigint::numeric / 1000);
$$
;

@kjmph
Copy link
Author

kjmph commented Jan 31, 2023

@qwesda, thanks!

@nawlbergs
Copy link

@qwesda
Im fairly new to postgres.. (coming in from mysql) can you give an example of how/when you would use that? can you use it as part of a select query?

@qwesda
Copy link

qwesda commented Jan 31, 2023

@nawlbergs I'm currently running some test for a new DB and my current plan is to have most tables set up like this:

CREATE TABLE statements.statement (
    uuid_statement uuid DEFAULT public.uuid_generate_v7() PRIMARY KEY,
    revision_id uuid NOT NULL DEFAULT public.uuid_generate_v7(),
    
    name text,
    currency text,
    amount_in_currency_calculated numeric
    
    [ ... ]
);

CREATE OR REPLACE FUNCTION public.trigger_set_revision_id()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.revision_id = public.uuid_generate_v7();
        RETURN NEW;
END;
$$
;

CREATE TRIGGER trigger_set_revision_id
BEFORE INSERT OR UPDATE ON statements.statement
FOR EACH ROW
EXECUTE FUNCTION public.trigger_set_revision_id();

With this I have the following properties:

  • uuid based primary keys, that are sortable
  • a revision uuid that can be used in audit logs (also sortable) or in web apps to detect if data has changed
  • created/modified timestamps (without timezone) for free

The API would then make a request something like (simplified for readability ...):

SELECT
    jsonb_agg(
        jsonb_build_object(
            'uuid_statement', statement.uuid_statement,
            'revision_id', statement.revision_id,
            
            'created_at', public.timestamp_from_uuid_v7(statement.uuid_statement),
            'modified_at', public.timestamp_from_uuid_v7(statement.revision_id),
        
            'name', statement.name,
            
            'currency', statement.currency,
            'amount_in_currency_calculated', statement.amount_in_currency_calculated
        )
    ORDER BY statement.uuid_statement DESC)

FROM statements.statement;

@seankanderson
Copy link

I'm not knowledgeable enough in the theory to write such a function and be able to test/verify that it will not generate collisions. Are there any known and accepted ways to do that?

Copy link

ghost commented Mar 16, 2023

@kjmph Would it be possible for you to attach a license to this, either in the gist itself, or by copying this over to a repository with a license? Even a simple license like MIT would make it a lot easier to use this in projects that have restrictions/tracking on that aspect of code.

Also, thanks for putting this all together! I'd love for this to be a built-in Postgres function someday, but in the meantime, it's great to see the community getting ahead of things.

@kjmph
Copy link
Author

kjmph commented Mar 16, 2023

Would my addition of a LICENSE.txt and CONTRIBUTORS.txt suffice in the gist for now? I added it. Also, it seems GitHub wants to name this according to the alphabetical ordering of the files in the gist. Bleh. One day I may break this into a repository. Thanks @DanHultonEvisort

Copy link

ghost commented Mar 16, 2023

That's perfect, thanks so much!

@guilherme-de-marchi
Copy link

amazing!

@rolftimmermans
Copy link

rolftimmermans commented Sep 17, 2023

Thanks for this. For those who don't mind sacrificing readability, the following implementation of uuid_generate_v7() should be a bit faster (no benchmarks, sorry):

create or replace function uuid_generate_v7()
returns uuid
as $$
begin
  return encode(overlay(
    set_bit(set_bit(uuid_send(gen_random_uuid()), 53, 1), 52, 1)
    placing substring(int8send((extract(epoch from clock_timestamp()) * 1000)::bigint) from 3)
    from 1 for 6), 'hex')::uuid;
end
$$
language plpgsql
volatile;

@kjmph
Copy link
Author

kjmph commented Sep 18, 2023

So, let's see if I can spot the difference with your changes.

  1. There was a stray floor that still remained after earlier changes to the IETF draft, this was removed.
  2. All local variables are removed (forgive me, is this not optimized out.. I'm not as familiar with what happens behind the scenes with PL/pgSQL.)
  3. Rather than set the byte that contains the version number, since v4 is being upgraded, only the 2 and 1 bits are flipped from zeros to ones (thus version 7 becomes set).

Making change #1 and change #3 gets the implementation on par with your implementation without local variables on my test machine. This brought the generation from 24.59M Ops/sec to 25.41M Ops/sec. So, I don't see the need to sacrifice readability for performance here. Does this implementation look correct to you, @rolftimmermans? If so, I'll make the change to the gist and add you to contributors.

create or replace function uuid_generate_v7()
returns uuid
as $$
declare
  unix_ts_ms bytea;
  uuid_bytes bytea;
begin
  unix_ts_ms = substring(int8send((extract(epoch from clock_timestamp()) * 1000)::bigint) from 3);

  -- use random v4 uuid as starting point (which has the same variant we need)
  uuid_bytes = uuid_send(gen_random_uuid());

  -- overlay timestamp
  uuid_bytes = overlay(uuid_bytes placing unix_ts_ms from 1 for 6);

  -- set version 7 by flipping the 2 and 1 bit in the version 4 string
  uuid_bytes = set_bit(set_bit(uuid_bytes, 53, 1), 52, 1);

  return encode(uuid_bytes, 'hex')::uuid;
end
$$
language plpgsql
volatile;

Thanks!

@kjmph
Copy link
Author

kjmph commented Sep 18, 2023

Ah, my apologies, the floor wasn't left over from the earlier draft, it is there for two reasons.

  1. The specification states that the least significant bits MUST be used when truncating a timestamp.
  2. The microsecond version, labeled version 8, also relies on the timestamp precision being accurate.

Thus, we can't remove the floor for correctness reasons, unless there is a faster way to only retrieve the millisecond bits from the clock_timestamp. However, good news, it seems all the performance gains on my test machine were due to change #3, which is the set_bit instead of the set_byte calls. So, I still think that change should be accepted.

@rolftimmermans
Copy link

rolftimmermans commented Sep 18, 2023

Thanks @kjmph for the summary and sorry for not making this clearer in my comment. I was surprised to see that removing local variables made a small but significant difference on our production PostgreSQL 15 server, but of course YMMV.

And thanks for clearing up that removing floor() is not standards compliant. Keep the changes you like and definitely feel free to ignore the rest. :)

@kjmph
Copy link
Author

kjmph commented Sep 18, 2023

Thanks @rolftimmermans, it is incorporated now. Cheers!

(EDIT: So much for test machines.. The same thing happened to me on production, I see a marked difference when removing local variables. I took that change as well. Let's see what everyone else thinks).

@ItsWendell
Copy link

ItsWendell commented Oct 22, 2023

I made the switch to pg_uulidv7 since I saw that is supported on the database provider I use neon database, and I thought I'll share my strategy here with uuid v7 enthusiast to migrate to this extension, if desired.

I got a conflict while enabling this extension CREATE EXTENSION IF NOT EXISTS pg_uuidv7;, since this extensions shares / adds the same function signature we all have in the plpgsql function uuid_generate_v7().

In case you just use this as a default value, here's the quickest way to migrate:

## Rename old function to a new function function name
ALTER FUNCTION uuid_generate_v7() RENAME TO uuid_generate_v7_fn;

## Creating the extension should no longer conflict
CREATE EXTENSION IF NOT EXISTS pg_uuidv7;

## Replace uuid_generate_v7_fn with a wrapper that now uses uuid_generate_v7
CREATE OR REPLACE FUNCTION uuid_generate_v7_fn() RETURNS uuid AS $$
BEGIN
  RETURN uuid_generate_v7();
END;
$$ LANGUAGE plpgsql;

Here's also a gist to a Dockerfile I've setup that adds some custom extensions functions to alpine base containers of postgres that I use for local development that has e.g. the pg_uuidv7 extension installed:

https://gist.github.com/ItsWendell/af2e2b4c93bb2f5d73f34b87406af435

@kjmph
Copy link
Author

kjmph commented Oct 23, 2023

Thanks for sharing @ItsWendell! I chose uuid_generate_v7 since it followed the convention. Not surprised other projects are also using that name. I appreciate you posting this for people who want to back out and switch to a C implementation. Not everyone can use an extension, and some will benefit from a PL/pgSQL version, so I'll continue to leave this up.

(Note: great Dockerfile too)

@dverite
Copy link

dverite commented Nov 2, 2023

With regard to performance, once the function is reduced to a one-liner in the plpgsql language, it should be converted to the SQL language instead of plpgsql. This avoids the overhead of the plpsql interpreter. The contents of SQL functions are typically inlined into the calling query (see https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions).

In a quick test generating 1 million values, with Postgres 15, the following version appears to be 13-15% faster than the plpgsql version:

Code:

create or replace function uuid_generate_v7()
returns uuid
as $$
  -- use random v4 uuid as starting point (which has the same variant we need)
  -- then overlay timestamp
  -- then set version 7 by flipping the 2 and 1 bit in the version 4 string
select encode(
    set_bit(
      set_bit(
        overlay(uuid_send(gen_random_uuid())
                placing substring(int8send(floor(extract(epoch from clock_timestamp()) * 1000)::bigint) from 3)
                from 1 for 6
        ),
        52, 1
      ),
      53, 1
    ),
    'hex')::uuid;
$$
language SQL
volatile;

@kjmph
Copy link
Author

kjmph commented Nov 3, 2023

Thanks @dverite! You all are so great, this gist is amazing. Can I ask what everyone is using to benchmark? I'm running \timing with this query:

$ select true from (select uuid_generate_v7() from generate_series(1, 1000000000)) a limit 1;

However, for this change I don't see a 13-15% improvement. Maybe I'm missing the inlining advantage. Could you, @dverite, please share what your benchmark is?

@dverite
Copy link

dverite commented Nov 3, 2023

Hi @kjmph, I'm using select count(uuid_generate_v7()) from generate_series(1,1000000); in psql with \timing on
On my desktop PC (Ubuntu 22..04, Postgres 15.4, AMD Ryzen 7 5800X3D), executing this query 4 consecutive times, I typically get these durations:

  • SQL version:
    Time: 1667.722 ms (00:01.668)
    Time: 1666.580 ms (00:01.667)
    Time: 1662.223 ms (00:01.662)
    Time: 1666.470 ms (00:01.666).

  • plpgsql version:
    Time: 2109.898 ms (00:02.110)
    Time: 2087.937 ms (00:02.088)
    Time: 2089.504 ms (00:02.090)
    Time: 2090.521 ms (00:02.091)

On my laptop PC (Ubuntu 20.04, Postgres 15.4, Intel core I5-8265U), I get those:

  • SQL version:
    Time: 2958,489 ms (00:02,958)
    Time: 2969,462 ms (00:02,969)
    Time: 2961,334 ms (00:02,961)
    Time: 2971,450 ms (00:02,971)

  • plpgsql version:
    Time: 3587,606 ms (00:03,588)
    Time: 3429,490 ms (00:03,429)
    Time: 3379,035 ms (00:03,379)
    Time: 3402,240 ms (00:03,402)

@kjmph
Copy link
Author

kjmph commented Nov 4, 2023

Great, thanks for sharing @dverite. It took a bit of digging, but it appears the SQL version of the function is more performant for a large number of invocations, and less performant for a small number of invocations. The cross-over on my machine is ~50 invocations. Thus, would you agree that the predominant use case is to insert a new record with an UUIDv7? If so, it seems better to bias towards the single invocation case and keep the current PL/pgSQL version.

Looking at the explain analyze output shows that the reason I wasn't seeing a performance improvement with my benchmark query was most likely because of the subquery scan with the limit. I see that the aggregate (count) performs better at those larger invocation counts.

If you want to try to reproduce, use the bench function located at this site. Try executing:

$ select * from bench('select uuid_generate_v7_dverite()', 100000); -- the version posted by @dverite 
$ select * from bench('select uuid_generate_v7_kjmph()', 100000); -- the version posted in the gist
$ select * from bench('select uuid_generate_v7_dverite() from generate_series(1, 10)', 50000);
$ select * from bench('select uuid_generate_v7_kjmph() from generate_series(1, 10)', 50000);
$ select * from bench('select uuid_generate_v7_dverite() from generate_series(1, 100)', 10000);
$ select * from bench('select uuid_generate_v7_kjmph() from generate_series(1, 100)', 10000);

If you prefer to work up an example query for a typical insert load, we can explore that as well.

Thoughts?

@agiza1
Copy link

agiza1 commented Nov 9, 2023

Ahh

@agiza1
Copy link

agiza1 commented Nov 9, 2023

No

@ardabeyazoglu
Copy link

ardabeyazoglu commented Apr 23, 2024

The given uuidv7 implementations here are not sorted in my tests. Isn't uuidv7 supposed to be sorted?
Simply running following query and checking "generate_series" integer column reveals it.

select uuid_generate_v7(), generate_series from generate_series(1,200) order by uuid_generate_v7 asc

On the other hand the function from this gist works correct.

@rolftimmermans
Copy link

rolftimmermans commented Apr 23, 2024

@ardabeyazoglu If you need that level of granularity you should use UUIDv8, which has microsecond precision. UUIDv7 uses milliseconds only. The functions given here easily generate 200 UUIDs within the same millisecond, which will cause UUIDv7s to be in random order with respect to each other.

@ardabeyazoglu
Copy link

@rolftimmermans I see. However, the one provided in gist i sent fulfils that level of granularity. It gives me 500 rows instantly and sorted. But, there is a slight performance drop comparing to this one.

@rolftimmermans
Copy link

rolftimmermans commented Apr 23, 2024

@ardabeyazoglu I guess the function in your link does not implement UUIDv7 correctly (to the extent we can call any implementation of a draft RFC "correct"). There is an implementation of uuid_generate_v8() given in this gist. I suggest using that; it's fast and has microsecond precision.

Edit: Seems like the draft RFC is changing the level of precision allowed in UUIDv7: https://www.ietf.org/archive/id/draft-ietf-uuidrev-rfc4122bis-14.html#name-uuid-version-7

@kjmph
Copy link
Author

kjmph commented Apr 23, 2024

Hello @ardabeyazoglu, it is a bit of a subtle answer. Earlier drafts of UUIDv7 contained sub-second precision bits in the format, that an implementation MAY use. UUIDv8 was for all custom usage that was implementation controlled. Current versions of the draft made UUIDv7 only for millisecond precision, and all sub-millisecond precision was moved to UUIDv8 for custom formats. The implementation attached to this draft for UUIDv7 conforms to the current drafts, while the UUIDv8 in this gist conforms to old UUIDv7 with microsecond precision.

The gist you linked to was an old UUIDv7 implementation with microsecond precision. If you want to compare apples to apples, please compare uuid_generate_v8 in this gist to the other implementation for performance analysis.

Note, uuid_generate_v7 in this gist is sorted in your example query. It is generating that many UUIDs per millisecond that they look unordered in the test query. As Rolf indicated.

Thanks @rolftimmermans for answering these questions, thought I would provide more color if this helpful.

@ardabeyazoglu
Copy link

Thanks for detailed clarification @kjmph, I also saw the difference after reading the codes carefully.

@vchirikov
Copy link

@kjmph, FYI

According to current RFC (was 4122 now it's RFC 9562 - Universally Unique IDentifiers (UUIDs) ):

uuidv7 may contain:

  1. An OPTIONAL sub-millisecond timestamp fraction (12 bits at maximum) as per Section 6.2 (Method 3).

IMHO, it's ok to fill rand_a with 12 bit of microseconds in uuid v7

@jamesarosen
Copy link

jamesarosen commented Nov 29, 2024

IMHO, it's ok to fill rand_a with 12 bit of microseconds in uuid v7

uuid_generate_uuidv7_with_us() is wordy, but it's far from the worst function name I've seen. We could have both!

(We could also have both with an optional boolean argument. I suspect that would be a performance hit, but I spend more time in JS than Postgres, so I don't trust my instincts here.)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment