Thinking Sphinx and postgresql HSTORE October 20, 2015

in code

I’ve been working on putting up where I’ve been exploring using some of postgres’ newer features. HSTORE lets you directly store an indexable hash map in a row, which for my use case eliminates any desire to use a document store such as Mongo.

However as things have gotten more complicated I’ve tacked on a search engine, the one of choice for Ruby/Rails acts Sphinx connected by Pat Allan’s ThinkingSphinx gem. Since you are directly interfacing with the database through your index definition using my HSTORE keys was no problem at all:

ThinkingSphinx::Index.define :bike_model, :with => :active_record do
  # regular old fields
  indexes manufacturer
  indexes model
  indexes size

  # hstore!
  BikeModel.all_component_keys.each do |key|
    indexes ("components -> '#{key}'"), :as => key.to_sym

  # some attributes
  has year, price

and in my ActiveRecord backed BikeModel a static method which grabs all the unique keys directly from the database.

def self.all_component_keys
    select distinct k
    from (
        select skeys(components) as k
        from bike_models
    ) as dt