10 ms·

Rails 4 UUID primary keys with PostgreSQL

71 points by labria 13y ago

zimbatm 13y ago
I think the primary_key line should be: t.primary_key :id, :type => :uuid
- netghost 13y ago
  That makes a lot more sense, I was staring at the example, and then just figured I'd have to try it later.
- hiddenfeatures 13y ago
  I was really wondering where the difference to the normal syntax was. Thanks for the clarification
- labria 13y ago
  Sorry, fixed that.
- labria 13y ago
  It's actually: t.primary_key :id, :uuid
scotth 13y ago
Can you use find_in_batches or find_each with UUIDs?
- pg_bot 13y ago
  Of course, both of those methods are members of ActiveRecord and will work regardless of how you filter your records.
  - ryeguy 13y ago
    But don't those methods rely on the sequential ordering of auto incrementing ids?
    - pg_bot 13y ago
      After reading the documentation more clearly. http://edgeapi.rubyonrails.org/classes/ActiveRecord/Batches.html http://edgeapi.rubyonrails.org/classes/ActiveRecord/Batches..... 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 13y ago
        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.
- dickeytk 13y ago
  No because ActiveRecord will try and do > uuid comparisons that won't evaluate. Also, if it's not sequential, you may miss records while iterating through a collection.
floormat 13y ago
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?
- pallinder 13y ago
  Imagine a distributed system where you want to preserve uniqueness across the board. Using a uuid more or less (by the sheer number of possibilities) guarantees that this will be the case.
  - cheald 13y ago
    UUID v1 uses the MAC address of the computer doing the generation as a part of the UUID, which ensures uniqueness so long as you aren't cloning MAC addresses in your infrastructure. The downside of this is that it can leak information about the machine that generated the UUID, but if you require deterministic uniqueness, there you go.
- radicalbyte 13y ago
  As pallinder said, it can be very handy: the IDs can be generated by the nodes, not the db server. Very useful in disconnected environments. Imagine being able to create data on a smartphone whilst sitting on the plane, and not having to do anything messy with ID replacement when you sync with the server in the office. The cost? The keys are larger, and (unless using a sequential algorithm) are poor candidates for clustered keys (because they force page splits). The impact can be rather large (this lead to terrible performance in early versions of Sharepoint).
  - jacques_chester 13y ago
    To be clear, MAC-based UUIDs are sequential. PRNG (v4) UUIDs are random and don't work well with indexes.
- labria 13y ago
  At the DB level, it facilitates master-master replication set-up, eliminating the auto_increment collision problems. Master-master replication, in turn, allows building distributed applications that can handle net splits reasonably well.
flog 13y ago
Now if only I could get this working with Postgres.app
- pg_bot 13y ago
  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 13y ago
    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 13y ago
      Yes, that's exactly I didn't include it in the migration in the example.
  - wtn 13y ago
    There's a shortcut in the Active Record PostgreSQLAdapter: enable_extension 'uuid-ossp'
tenderlove 13y ago
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')"
joseph4521 13y ago
Or just use Sequel already, works with all versions of Rails (and without Rails too).
JasonPunyon 13y ago
(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.
- jshen 13y ago
  Just use a k-ordered uuid.
- labria 13y ago
  Yepp, mentioned it here: http://labria.github.io/2013/04/28/rails-4-postgres-uuid-pk-guide/ http://labria.github.io/2013/04/28/rails-4-postgres-uuid-pk-...
  - ryeguy 13y ago
    No you didn't. Not fully at least. OP is saying that there is a penalty just for doing the inserts themselves.
- jashkenas 13y ago
  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.)
  - deleted 13y ago
    [deleted]
  - axelfontaine 13y ago
    You can merge any two data buckets, without any risk of id collision.
- tszming 13y ago
  Yes, what you said also apply to MySQL/InnoDB and MongoDB. Someone did an experiment with MongoDB some times ago for various id schemes: http://i.imgur.com/clm9D.png http://i.imgur.com/clm9D.png (https://groups.google.com/d/topic/mongodb-user/1gPqVmFHExY/discussion https://groups.google.com/d/topic/mongodb-user/1gPqVmFHExY/d...), I don't know if Postgres is being different but people should do their own testings instead of blindly listening to advices from the Internet.
labria 13y ago
See my second, more detailed post here: http://labria.github.io/2013/04/28/rails-4-postgres-uuid-pk-guide/ http://labria.github.io/2013/04/28/rails-4-postgres-uuid-pk-...
jashmenn 13y ago
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 https://github.com/jashmenn/activeuuid

71 points by labria 13y ago

zimbatm 13y ago

I think the primary_key line should be: t.primary_key :id, :type => :uuid

netghost 13y ago
That makes a lot more sense, I was staring at the example, and then just figured I'd have to try it later.
hiddenfeatures 13y ago
I was really wondering where the difference to the normal syntax was. Thanks for the clarification
labria 13y ago
Sorry, fixed that.
labria 13y ago
It's actually: t.primary_key :id, :uuid

scotth 13y ago

Can you use find_in_batches or find_each with UUIDs?

pg_bot 13y ago
Of course, both of those methods are members of ActiveRecord and will work regardless of how you filter your records.
- ryeguy 13y ago
  But don't those methods rely on the sequential ordering of auto incrementing ids?
  - pg_bot 13y ago
    After reading the documentation more clearly. http://edgeapi.rubyonrails.org/classes/ActiveRecord/Batches.html http://edgeapi.rubyonrails.org/classes/ActiveRecord/Batches..... 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 13y ago
      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.
dickeytk 13y ago
No because ActiveRecord will try and do > uuid comparisons that won't evaluate. Also, if it's not sequential, you may miss records while iterating through a collection.

floormat 13y ago

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?

pallinder 13y ago
Imagine a distributed system where you want to preserve uniqueness across the board. Using a uuid more or less (by the sheer number of possibilities) guarantees that this will be the case.
- cheald 13y ago
  UUID v1 uses the MAC address of the computer doing the generation as a part of the UUID, which ensures uniqueness so long as you aren't cloning MAC addresses in your infrastructure. The downside of this is that it can leak information about the machine that generated the UUID, but if you require deterministic uniqueness, there you go.
radicalbyte 13y ago
As pallinder said, it can be very handy: the IDs can be generated by the nodes, not the db server. Very useful in disconnected environments. Imagine being able to create data on a smartphone whilst sitting on the plane, and not having to do anything messy with ID replacement when you sync with the server in the office. The cost? The keys are larger, and (unless using a sequential algorithm) are poor candidates for clustered keys (because they force page splits). The impact can be rather large (this lead to terrible performance in early versions of Sharepoint).
- jacques_chester 13y ago
  To be clear, MAC-based UUIDs are sequential. PRNG (v4) UUIDs are random and don't work well with indexes.
labria 13y ago
At the DB level, it facilitates master-master replication set-up, eliminating the auto_increment collision problems. Master-master replication, in turn, allows building distributed applications that can handle net splits reasonably well.

flog 13y ago

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

pg_bot 13y ago
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 13y ago
  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 13y ago
    Yes, that's exactly I didn't include it in the migration in the example.
- wtn 13y ago
  There's a shortcut in the Active Record PostgreSQLAdapter: enable_extension 'uuid-ossp'

tenderlove 13y ago

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')"

joseph4521 13y ago

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

JasonPunyon 13y ago

(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.

jshen 13y ago
Just use a k-ordered uuid.
labria 13y ago
Yepp, mentioned it here: http://labria.github.io/2013/04/28/rails-4-postgres-uuid-pk-guide/ http://labria.github.io/2013/04/28/rails-4-postgres-uuid-pk-...
- ryeguy 13y ago
  No you didn't. Not fully at least. OP is saying that there is a penalty just for doing the inserts themselves.
jashkenas 13y ago
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.)
- deleted 13y ago
  [deleted]
- axelfontaine 13y ago
  You can merge any two data buckets, without any risk of id collision.
tszming 13y ago
Yes, what you said also apply to MySQL/InnoDB and MongoDB. Someone did an experiment with MongoDB some times ago for various id schemes: http://i.imgur.com/clm9D.png http://i.imgur.com/clm9D.png (https://groups.google.com/d/topic/mongodb-user/1gPqVmFHExY/discussion https://groups.google.com/d/topic/mongodb-user/1gPqVmFHExY/d...), I don't know if Postgres is being different but people should do their own testings instead of blindly listening to advices from the Internet.

labria 13y ago

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

jashmenn 13y ago

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 https://github.com/jashmenn/activeuuid