#hstore

Đây Logo shop mình nè

I've been working on an application to handle event data that depends on a fork of the Recurrence gem for building out recurrences.

That was working well until I needed to query and filter the data. I wanted an easy way to say:

Give me all events happening on any date from start_date through end_date with the tag awesome.

Since the events can be set to recur on specific days or periods between the start and end date, just asking for events that fall inside of the start_date and end_date parameters wouldn't work.

I was already using PostgreSQL's hstore to store the event window output from the Recurrence gem. The key value store contained all of the dates for the next 365 days. That would work right? Not really. Querying the hstore column to ask if it contained a value wasn't working out the way I wanted and didn't seem to be a performant option. It had to be easier.

I saw that the Array data type could do exactly what I wanted, quickly. The basic idea was to create an array of dates and ask the Event's Array column (called occurrence_dates) if it contained any of those dates. For this particular method, I wanted to know if any Event's occurrence_dates array overlapped the array I was passing in. You can use the && operator to determine if two arrays have elements in common. 

where("occurrence_dates && ?", "{#{(start_date..end_date).to_a.join(',')}}")

A very interesting set of slides from Christophe Pettus looking at the features in PosgreSQL that would allow one to use it as a document database:

XML

built-in type

can handle very large documents (2GB)

XPath support

export functions

no indexing, except defining custom ones using expression index

hstore

hierarchical storage type

in contrib (not part of the core)

custom functions (nb: very ugly syntax imo)

GiST and GIN indexes (nb: I’ve posted in the past about PostgreSQL GiST and GIN Index Types)

supports also expression indexes

JSON

built-in type starting with PostgreSQL 9.2

validates JSON

support expression indexing

nothing else besides a lot of feature scheduled for

Christophe Pettus’s slides also include the results and some thoughts about a locally-run pseudo-benchmark against these engines and MongoDB.

You can see all the slides and download them after the break.

Download PDF

Kevin Faustino:

Out of all the supported databases available in Active Record, PostgreSQL received the most amount of attention during the development of Rails 4. In today’s countdown post, we are going to look at the various additions made to the PostgreSQL database adapter.

Teaser:

hstore and hstore indexes support

arrays

uuid

network address data types

int4range, int8range

json

 After my previous article the PostgreSQL landscape has changed totally. We have a total new version (9.2) with lots of speed optimisations and new features. That brings me out of my cave to visit the (promised) FriendFeed's schema-less data in MySQL casestudy. I found FriendFeed technique to be one of the best examples of knowing your tools, and not following the buzz; even with very simple software stack they were able to create some great marvels (remember Python Tornado now being used by Facebook?). I will try to keep the whole thing really scientific and re-imagine how Postgres could have change the scenario completely. I will briefly explain the solution from FriendFeed, and then show how I would have done same thing using Postgres. 

I am in no way trying to do a comparison of MySQL and PostgreSQL features. I am not trying to prove which RDBMS is better and which one you should choose over the other. It's just imagining a solution of a problem with a different tool!

 So just to revisit briefly FriendFeed was facing issues when adding new features due to its increasing user base, one of the biggest issue was schema changes. "In particular, making schema changes or adding indexes to a database with more than 10 - 20 million rows completely locks the database for hours at a time", nobody would like to have Facebook account blocked just because  a new timeline was introduced! The solution they produced was nifty, incremental and very inspiring. They stored JSON entities with 16-byte UUID, now JSON due to its schema-less nature can dynamically add or drop values from the entity (JSON object). You can simply choose a BLOB (even TEXT for simplicity) to store the JSON. For each property (JSON property) in entity that requires to be indexed; they created a separate table with primary key of {user_id, property_of_entity}; and this rule can be applied vitally everywhere. This allowed them to dynamically create and drop indexes on different fields of an entity. Since tables can be shraded, each index table can be sharded.

 Now we can do the exact same thing PostgreSQL (we can choose to completely stick to FriendFeed solution and don't  anything special/specific to Postgres); but the good news is that Postgres has some really neat features that can help us improve, and remove extra coding overhead! Here is entity table's schema redefined for PostgreSQL: 

 Notice nothing changed much except the BINARY going to BYTEA, and HSTORE for body. Yep you guessed it I am going to use HSTORE to actually store the body. Now if you don't know what HSTORE is you can refer to my previous article. So representing an entity can actually be quite simple, consider entity (taken directly from blogpost):

 Can be represented as 

 Inserting this entity into table is pretty straight forward: 

INSERT INTO entities (id, updated, body) VALUES(  '\\x71f0c4d2291844cca2df6f486e96e37c'::bytea,  '2012-09-28T03:42:29.655011'::timestamp,  hstore(   ARRAY['updated', 'user_id', 'title', 'feed_id', 'link', 'published', 'id'],   ARRAY[     '1235697046',     '\xf48b0440ca0c4f66991c4d5f6a078eaf',     'We just launched a new backend system for FriendFeed!',     '\xf48b0440ca0c4f66991c4d5f6a078eaf',     'http://friendfeed.com/e/71f0c4d2-2918-44cc-a2df-6f486e96e37c',     '1235697046',     '\x71f0c4d2291844cca2df6f486e96e37c']  ) )

 In Python using psycopg2 its even more pythonic. Here is just an example script to do that:

 One can use SQLAlchemy to create even better looking code (using adapter like in this gist). Also you can use sharding just like FriendFeed guys, and yes SQLAlchemy supports sharding. That takes care of two major issues:

Structure free storage through HSTORE

Sharding and code clarity through SQLAlchemy, you can choose to do it manually if you don't like ORMs.

Now the last part is the indexing on the fields of our "structure free" body. FriendFeed used separate tables to do this and separate code in application code was maintaining that. Well good news PostgreSQL can do that form me without additional tables:

CREATE INDEX entities_index_user_id ON entities USING BTREE(((body->'user_id')::bytea));

There are different options for types of index you can create (GIN, GIST, HASH, and BTREE), and the best part is usual rules for functional indexes apply on these indexes as well. I won't go into details but you can look into documentation, and have a detailed look what does this precisely mean. Creating index like this will usually cause the same lock issue on complete table; and this is where PostgreSQL shines again. You can create index concurrently by simply adding CONCURRENTLY in your CREATE INDEX statement:

CREATE INDEX CONCURRENTLY entities_index_user_id ON entities USING BTREE(((body->'user_id')::bytea));

  Now what about the case when I don't want to index a field any more? FriendFeed did it by dropping the index table and updating the code for not hitting the indexing table. What about PostgreSQL? You can either disable the index, or simply get rid of it and drop it:

DROP INDEX CONCURRENTLY IF EXISTS entities_index_user_id;

 The above technique gives me multiple advantages; it helps me reduce code complexity by a huge margin! Imagine the pain of every time updating your code and dealing the complex architecture just because you introduced a field that needs indexing. Consider it against a simple CREATE INDEX statement. For me simplicity matters the most, and these builtin features are convincing enough for me to use PostgreSQL (even migrate to it). Secondly, if you look closely the index will lie on same shard where the tuple lies; this simply removes the possibility of accidentally moving the table used for indexing of field to a different shard (this may be done at times but it would compromise atomicity). Using the index created simply gives me all the powers that I would have on normal column index, which means inserting a row automatically hits the index removing the possibility of row being skipped due to buggy code. Schema updates become simpler and easier to maintain. Not to state the obvious its ACID! You can use modern JSON support to serialize your output directly to JSON. I am leaving the disadvantages portion to the audience.

Heroku just announced their support of hstore in their dedicated Postgres 9.1 instances. Hstore is a schema less key value store inside of PostgreSQL that allows us to store data like hashes directly inside of a column. It's great for when you don't know exactly what types of attributes you need to store on a model, or if you need to support many different attributes for the same model.

Update: You can now use Hstore with development databases on Heroku

A good example is storing attributes for a Product model. We might start out only selling books, which have an author, number of pages, but then transition over to selling laptops which have cpu speed and display resolution. Using Hstore allows us to easily store all these values without having to make a bunch mostly blank columns.

To get started with Rails and hstore you can watch the screencast below or visit the hstore example app running on Heroku.

More on Hstore

Hstore in Rails functions much like serializing hashes, except that we can query our data much faster since hstore is a native data type. It is supported natively in Rails 4, but until then we'll need to use the activerecord-postgres-hstore gem.

Getting Started

You will need a version of PostgreSQL locally that supports the hstore extension. I recommend installing postgres using homebrew on OS X. Once you've done that you can enable hstore usage by running this in Postgres

CREATE EXTENSION hstore;

You can put this in a migration if you prefer

class SetupHstore < ActiveRecord::Migration def self.up execute "CREATE EXTENSION hstore" end def self.down execute "DROP EXTENSION hstore" end end

Once that is done you will need to create a column with a type of hstore, here we are giving our Product model a column called data with hstore type.

class CreateProducts < ActiveRecord::Migration def change create_table :products do |t| t.string :name t.hstore :data t.timestamps end end end

Once we've done that we can now store any type of attributes in the data column.

Product.create(:name => "Geek Love: A Novel", :data => {'author' => 'Katherine Dunn', 'pages' => 368, 'category' => 'fiction'}) Product.last.data['category'] # => 'fiction'

Querying

Not only does hstore allow us to store arbitrary keys and values it allows us to quickly query them.

# Find all products that have a key of 'author' in data Product.where("data ? :key", :key => 'author') # Find all products that have a 'pages' and '368' key value pair in data Product.where("data @> (:key => :value)", :key => 'pages', :value => '368') # Find all products that don't have a key value pair 'pages' and '999' in data Product.where("not data @> (:key => :value)", :key => 'pages', :value => '999') # Find all products having key 'author' and value like 'ba' in data Product.where("data -> :key LIKE :value", :key => 'author, :value => "%Kat%")

More information available in the Postgres hstore docs. Though like a normal column if you query it frequently, you can get even more speed by adding an index. You can do this using one of two indexes that also speed up full text searches. They're GiST (Generalized Search Tree) or GIN (Generalized Inverted iNdex). Which sill speed up queries using the @> and ? postgres operators.

class Index < ActiveRecord::Migration def up execute "CREATE INDEX products_gin_data ON products USING GIN(data)" end def down execute "DROP INDEX products_gin_data" end end

Use It

Try out the hstore example app, clone the Github repo, and let me know what cool things you build on twitter @schneems.

Thanks