[tehlike]

The sequence thing is definitely interesting, I stopped using them a while ago, using mostly sequential uuid (or uuid v7), or use something like HiLo https://en.wikipedia.org/wiki/Hi/Lo_algorithm

[pmarreck]

This PL/pgSQL function might help others looking to keep uuidv7 generation responsibility within the database until it's natively supported:

  -- IETF Draft Spec: https://www.ietf.org/archive/id/draft-peabody-dispatch-new-uuid-format-01.html

  CREATE SEQUENCE uuidv7_seq MAXVALUE 4095; -- A 12-bit sequence

  CREATE OR REPLACE FUNCTION generate_uuidv7()
  RETURNS uuid AS $$
  DECLARE
    unixts bigint;
    msec bigint;
    seq bigint;
    rand bigint;
    uuid_hex varchar;
  BEGIN
    -- Get current UNIX epoch in milliseconds
    unixts := (EXTRACT(EPOCH FROM clock_timestamp()) * 1000)::bigint;

    -- Extract milliseconds
    msec := unixts % 1000; -- Milliseconds

    -- Get next value from the sequence for the "motonic clock sequence counter" value
    seq := NEXTVAL('uuidv7_seq');

    -- Generate a random 62-bit number
    rand := (RANDOM() * 4611686018427387903)::bigint; -- 62-bit random number

    -- Construct the UUID
    uuid_hex := LPAD(TO_HEX(((unixts << 28) + (msec << 16) + (7 << 12) + seq)), 16, '0') ||
                LPAD(TO_HEX((2 << 62) + rand), 16, '0');

    -- Return the UUID
    RETURN uuid_hex::uuid;
  END;
  $$ LANGUAGE plpgsql VOLATILE;

  SELECT generate_uuidv7();

[tehlike]

Keeping id generation inside the app is useful, you can batch multiple statements (e.g. insert product, insert product details in a single query, or other sorts of dependencies). You don't have to wait for first insertion to finish to get the id of the record, for example.

[pmarreck]

Good point. There are implementations of this in various languages, then, that are linked in the blog post.

I'm not sure how difficult it would be to tell postgres to only generate the identifier if it's not part of the inserted data already... but that might be a nice compromise

[tehlike]

I think that's what default value achieves. If it's not provided, it generates one, if it is provided, it's used.

[pmarreck]

It is! I had looked it up in the meantime.

[brentjanderson]

OP here - we avoid sequences in all but one part of our application due to a dependency. We use [KSUIDs][1] and UUID v4 in various places. This one "gotcha" applies to any sequence, so it's worth calling out as general advice when running a migration like this.

[1]: https://segment.com/blog/a-brief-history-of-the-uuid/

[tehlike]

Definitely great call out. Thanks for writing this.