

Rails 4 UUID primary keys with PostgreSQL - labria
http://labria.github.io/2013/04/27/rails-4-uuid-primary-keys-with-postgresql/

======
JasonPunyon
(Caveat: I know this about SQL server, not Postgres)

If you use random UUID's (as opposed to sequential UUID's) for your primary
key your database will spend an extra hunk of time on reordering your PK index
on disk on insert. This bit us at Stackoverflow. So remember: just because you
can do something doesn't mean you should.

~~~
jashkenas
What's the advantage of a sequential UUID over an auto-incrementing integer?

(Keeping in mind that both can be started at N on different shards, or
prefixed.)

~~~
axelfontaine
You can merge any two data buckets, without any risk of id collision.

------
tenderlove
I commented on the OP's article, but do this for the pk:

    
    
        create_table(:users, id: :uuid) do |t|
          t.string 'name'
        end
    

And to make sure the extension is enabled, (in your migrations) do:

    
    
        enable_extension 'uuid-ossp'
    

For now, make sure to use SQL schema dumping. I don't think Ruby schema files
will work (as I haven't written a test for it yet).

EDIT: I forgot to mention, you can enable hstore by using
"enable_extension('hstore')"

------
floormat
Sorry for the up coming stupid question but...

What is the value of this? Why can't unique identification be done using just
regular increments on an ID column? Or even a composite key?

~~~
bdon
It's also useful if you don't want your URLs to expose how many of a certain
thing you have, whether it be users, posts, payments etc. A lot of sites let
you derive how much activity they have based on how fast their numeric IDs
increment. You could use a separate token alongside the pkey to do the same
but this feature just makes it simpler.

~~~
__Joker
Along the lines

[http://www.wired.com/autopia/2010/10/how-the-allies-used-
mat...](http://www.wired.com/autopia/2010/10/how-the-allies-used-math-against-
german-tanks/)

[http://www.jackofallclouds.com/2009/09/anatomy-of-an-
amazon-...](http://www.jackofallclouds.com/2009/09/anatomy-of-an-amazon-
ec2-resource-id/)

------
zimbatm
I think the primary_key line should be:

    
    
        t.primary_key :id, :type => :uuid

~~~
labria
It's actually: t.primary_key :id, :uuid

------
jashmenn
If you'd like to use UUIDs in Rails 3, I've been maintaining the activeuuid
plugin for a while now:

<https://github.com/jashmenn/activeuuid>

------
labria
See my second, more detailed post here:
[http://labria.github.io/2013/04/28/rails-4-postgres-uuid-
pk-...](http://labria.github.io/2013/04/28/rails-4-postgres-uuid-pk-guide/)

------
joseph4521
Or just use Sequel already, works with all versions of Rails (and without
Rails too).

------
flog
Now if only I could get this working with Postgres.app

~~~
pg_bot
Running on rails 4 it should be:

    
    
       $ rails g migration add_uuid_to_postgresql
    
       class AddUuidToPostgresql < ActiveRecord::Migration
         execute("CREATE EXTENSION uuid-ossp;")
       end
    
       $ rake db:migrate && rake db:test:prepare
    

Then create a table as shown in the article, and it should work.

~~~
Legion
For this to work, the PG role that the Rails app is using has to be a
superuser, as AFAIK only superuser roles can execute CREATE EXTENSION.

Does anyone consider this an issue? I have been using non-superuser roles
within my Rails apps, and using an outside superuser role to add extensions
with an external tool (like pgAdmin).

~~~
labria
Yes, that's exactly I didn't include it in the migration in the example.

------
scotth
Can you use find_in_batches or find_each with UUIDs?

~~~
pg_bot
Of course, both of those methods are members of ActiveRecord and will work
regardless of how you filter your records.

~~~
ryeguy
But don't those methods rely on the sequential ordering of auto incrementing
ids?

~~~
pg_bot
After reading the documentation more clearly.
[http://edgeapi.rubyonrails.org/classes/ActiveRecord/Batches....](http://edgeapi.rubyonrails.org/classes/ActiveRecord/Batches.html).
It looks like there may need to be a patch for this feature, however the
batching process forces the database to make a query in Ascending order for
the primary keys so as long as you can generate new UUIDS in ascending order
you should be fine. However problems look like they will occur if you generate
a new record that has a UUID that would be inserted randomly between two
numbers in the UUID range. I will see if I can bang something out and issue a
pull request.

~~~
w-ll
I was dealing with a similar issue on a project that all id's are UUID's
because I wanted non-sequential id's.

I found it was just as simple of selecting ranges based of their date_created
attribute.

