Postgres introduced recently, in 9.4, jsonb column type, additional to existing json column that already existed before. JSON colums is no more than a blob field where the json is dumped as string. JSONB on the other fact is stored in a custom format optimized for json operations alowing for more operators. See stackoverflow thread for more information.
I created a github repo to showcase how to use it in Ecto. To see it in action clone the repo then create and populate database with 1M records:
I used jsonb to serialize a map in settings field and an array in acls field. Also to showcase that we can create also indexes on this fields I added settings_index and acls_index fields.
In settings field I stored objects that look like this:
Now that we have the database ready we can run queries on it inside the jsonb field:
String exist as array element:
Array contains value:
We’ll use array intersection here.
Object contains another object:
Object key exists:
Also I tested how fast the queries work with and without index.
This are my results:
As you can see there is significant performance increase if we add proper indexes.