Using Postgres Hstore with Rails 420 Nov 2013
I’m a huge fan of PostgresSQL given it’s performance and stability. One of it’s great features is the ability to store hashes in tables using the Hstore column type AND to query them! This is perfect for a column like user settings where you may need to add or change the type of settings you need as your app evolves. Here’s some steps and gotchas that I came across while using this column.
So first let’s start by creating a migration which would enable the Hstore extension on our database:
Next you’ll need to change your schema dump format from
sql as the schema for hstore can’t be represented by ruby.
config.active_record.schema_format = :sql
I’ve also come across a more cleaner way to enable this without using the native execute command (although I never got it to work):
With this approach you don’t have to even change the schema_format, it will automatically add
enable_extension "hstore" in your schema.rb file. Again, I’ve yet to see this work but I’d try this first before using the native sql approach.
Now you are ready to start using this column. Let’s add a settings column to our users table.
If you know which keys you’ll store in the settings column, you can define accessors in your model and even validate them:
Although, if you don’t know what kind of keys the client will send your controller, here’s a way to receive dynamic keys/value pairs using strong params:
We are now ready to query our users based on their settings:
More query syntax can be found here in the hstore documentation.
hstore provides index support for a few select operators such as
@>, ?, ?& or ?|. So if you are planning to use any of those, it’s probably a good idea to add an index for better performance:
Alternatively you can use the native DSL to accomplish the same thing:
I generally tend to use GIN index as the lookups are three times faster compared to GiST indexes, although they take three times longer to build. You can checkout their documentation to decide which is the best fit for your needs.
If you are using Heroku, there is one small issue I ran across. As a consequence of switching to sql schema format, I kept getting this error when heroku ran
db:migrate at the end of each deployment:
No such file or directory - pg_dump -i -s -x -O -f /app/db/structure.sql
It’s trying to dump a new structure.sql with the pg_dump command which is not present on Heroku’s environment. You really don’t need that in production anyway. So the way to get around it would be to just silent that task if it’s running in any environment other than development by adding this in your
Rake::Task["db:structure:dump"].clear unless Rails.env.development?
That’s a wrap! Let me know if I missed anything.