Rails 4 Postgres UUID Primary Key Guide

Sun Apr 28 00:00:00 -0700 2013

Basic setup

As noted in my previous post, Rails 4 now natively supports UUID primary keys in PostgreSQL. To create a table with a UUID simply create a migration (even simpler than I posted, Aaron Patterson pointed that I can just pass id: :uuid to the create_table method):

create_table :users, id: :uuid do |t|
  t.string :name
  t.timestamps
end

This will execute the following SQL:

CREATE TABLE "users" ("id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(), "name" character varying(255), "created_at" timestamp, "updated_at" timestamp)

While this is good enough to get started, there are some quirks along the way that we’ll need to overcome.

Sorting

If you’re not new to Rails, then you’re used to the fact the calling User.first will fetch the first User object ever created. This is due to the fact that the default sorting is by id, which is usually a AUTO_INCEREMENT integer column, so the first one will have the smallest id. When we switch to UUID for PK, this property will be lost, because the UUID is generated randomly (by default) and does not guarantee sequentiality. There are two ways to overcome this (if you do need to overcome this):

  • Add a default scope to sort by created_at
  • Switch to a different (from the default) UUID generation algorithm

While the first one is obvious, the second one needs some explanation. There are 5 methods for generating a UUID. The default version used in Rails is version 4, a completely random ID. We can switch to version 1, which is the combination of the machine’s MAC address and a timestamp, to get sequential UUID stings. This algorithm is somewhat less secure, because it exposes the generator machine MAC address and creation time, but this is not a really big security threat if you ask me. To switch the UUID generation to this algorithm we need to modify our migration:

create_table :users, id: false do |t|
  t.primary_key :id, :uuid, :default => 'uuid_generate_v1()'
  t.string :name
  t.timestamps
end

This will force the generated UUIDs to be sequential and preserve the sorting we’re used to. By the way, switching to sequential UUIDs also positively affects DB performance.

Relations

Since UUID primary keys are per-table and not a global setting (there is a plugin in the works to make it global), Rails has no way to tell if the primary key of a table is a integer or a UUID. Because of this, t.references :user will still generate a integer user_id field, which will obviously not work. The solution is simple:

create_table :posts, id: :uuid do |t|
  t.string :title
  t.uuid :user_id
  t.timestamps
end

The same goes for polymorphic relations.

Schema dumping

As Aaron mentioned on HN, ruby schema dumping does not work as expected yet (I noticed that it does not dump the :default for the primary key), you’ll have to use SQL dumping for now.

Enabling the extension

If your PostgreSQL user is a superuser, you can enable the extension right from a migration:

enable_extension 'uuid-ossp'

Rails 4 UUID primary keys with PostgreSQL

Sat Apr 27 00:00:00 -0700 2013

UPD: See my second, more detailed post about the matter.

Aaron recently added uuid primary key support to Rails4 edge. To try it in your app simply create a migration like this:

create_table :users, id: false do |t|
  t.primary_key :id, :uuid
  t.string :name
  t.timestamps
end

Rails now automatically sets the default value to uuid_generate_v4() for the primary_key.

You’ll of course need to run CREATE EXTENSION "uuid-ossp"; in Postgres, either manually or with a migration via a execute statement.