rails, PostgreSQL and the native uuid type
UUID have the very handy property that they are uniqe and there are quite many of them for you to use. Also they are difficult to guess and knowing the UUID of one object, it’s very hard to guess a valid UUID of another object.
This makes UUIDs perfect for identifying things in web applications:
- Even if you shard across multiple machines, each machine can independently generate primary keys without (realistic) fear of overlapping.
- You can generate them without using any kind of locks.
- Sometimes, you have to expose such keys to the user. If possible, you will of course do authorization checks, but it still makes sense not allowing users know about neighboring keysThis gets even more important when you are not able to do authorization keys because the resource you are referring to is public (like a mail alias) but it should still not possible to know other items if you know one.
Knowing that UUIDs are a good thing, you might want to use them in your application (or you just have to in the last case above).
There are multiple recipes out there that show how to do it in a rails application (this one for example).
All of these recipes store UUIDs as varchar’s in your database. In general, that’s fine and also the only thing you can do as most databases don’t have a native data type for UUIDs.
PostgreSQL the other hand indeed has a native 128 bit integer type to store UUID.
This is more space efficient than storing the UUID in string form (288 bit) and it might be a tad bit faster when doing comparison operations on the database as integer operations (even if they are this big) require a constant amount of operations whereas comparing two string UUIDs is a string comparison which is dependent on the string size and size of the matching parts.
So maybe for the (minuscule) speed increase or for the purpose of correct semantics or just for interoperability with other applications, you might want to use native PostgreSQL UUIDs from your Rails (or other, but without the abstraction of a “Migration”, just using UUID is trivial) applications.
This already works quite nicely if you generate the columns as strings in your migrations and then manually send an
alter table (whenever you restore the schema from scratch).
But if you want to create the column with the correct type directly from the migration and you want the column to be created correctly when using
rake db:schema:load, then you need a bit of additional magic, especially if you want to still support other databases.
In the end, everything boils down to monkey patching ActiveRecord::ConnectionAdapters::Adapters and PostgreSQLColumn of the same module. So here’s what I’ve addded to
config/initializers/uuuid_support.rb (Rails 3.0.):
In your migrations you can then use the :uuid type. In my sample case, this was it:
Maybe with a bit better Ruby knowledge than I have, it should be possible to just monkey-patch the parent
AbstractAdaper while still calling the method of the current subclass. This would not require a separate patch for all adapters in use.
For my case which was just support for SQLite and PostgreSQL, the above initializer was fine though.
blog comments powered by Disqus