Many duplicate values when using postgres uuid_generate_v4

user_673859 0
Viewed 2670

We added a UUID column to our 80 million row DB and the default gets generated using the postgres uuid_generate_v4() function.

We backfilled the uuid using this script:

current = 1
batch_size = 1000
last_id = 80000000

while current < last_id
  start_id = current
  end_id = current + batch_size
  puts "WORKING ON current: #{current}"
  ActiveRecord::Base.connection.execute <<-SQL.squish
    UPDATE table_name
    SET public_id = uuid_generate_v4()
    WHERE id BETWEEN '#{start_id}' and '#{end_id}' AND public_id IS NULL
  SQL
  current = end_id + 1
end

however, at the end of the script, we found that we had 135 duplicates, with some even having 3. How is this possible? Does the uuid_generate_v4() function generate dupes with such high probability?

uuid postgresql activerecord
2 Answers
user_55241000

https://doxygen.postgresql.org/uuid-ossp_8c.html#a9effb407a94b4ecc119d9546cd102c94

#ifdef HAVE_UUID_E2FS
    uuid_t      uu;

    uuid_generate_random(uu);

so you can try checking your /dev/urandom, eg:

for i in $(seq 1 8000000); do uuidgen >>/tmp/u; done
-bash-4.2$ cat /tmp/u | sort | uniq -c | sort -r | head -3
      1 fffe894a-63e3-47e0-aea2-563f9652afd3
      1 fffbb781-61d5-4751-b4eb-e45a8ed684b7
      1 fffa7bff-ea37-46db-925b-d58f931512be

a little brutal, but if you see dupes here (left 1 will be more then one, you probably should use uuid_generate_v1() or other function that does not rely on /dev/urandom or uses some timestamp in addition, or look for some other solution... https://www.postgresql.org/docs/current/static/uuid-ossp.html

user_53159740
Related