4 ms·
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://e
by tehlike 3y ago
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 https://en.wikipedia.org/wiki/Hi/Lo_algorithm
- brentjanderson 3y agoOP 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/ https://segment.com/blog/a-brief-history-of-the-uuid/
- tehlike 3y agoDefinitely great call out. Thanks for writing this.
- pmarreck 3y agoThis 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 3y agoKeeping 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 3y agoGood 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