Generate non-fragmenting UUIDs in Postgres?

Question

If I understand correctly, fully-random UUID values create fragmented indexes. Or, more precisely, the lack of a common prefix prevents dense trie storage in the indexes.

I've seen a suggestion to use uuid_generate_v1() or uuid_generate_v1mc() instead of uuid_generate_v4() to avoid this problem.

However, it seems that Version 1 of the UUID spec has the low bits of the ID first, preventing a shared prefix. Also, this timestamp is 60 bits, which seems like it may be overkill.

By contrast, some databases provide non-standard UUID generators with a timestamp in the leading 32-bits and then 12 bytes of randomness. See Datomic's Squuid's for example 1, 2.

Does it in fact make sense to use "Squuids" like this in Postgres? If so, how can I generate such IDs efficiently with pgplsql?

Answer 1

Note that inserting sequential index entries will result in a denser index only if you don't delete values and all your updates produce heap only tuples.

If you want sequential unique index values, why not build them yourself?

You could use clock_timestamp() in microseconds as bigint and append values from a cycling sequence:

CREATE SEQUENCE seq MINVALUE 0 MAXVALUE 999 CYCLE;

SELECT CAST(
          floor(
             EXTRACT(epoch FROM t)
          ) AS bigint
       ) % 1000000 * 1000000000
     + CAST(
          to_char(t, 'US') AS bigint
       ) * 1000
     + nextval('seq')
FROM (SELECT clock_timestamp()) clock(t);