jsonb / json / hstore

jsonb = rails 4.2+, psql 9.4+

hstore = psql 9.2+

JSONb & JSON

JSON:

1. stores an exact copy of the text

2. only expression index (rails 5)

3. must be re-parsed (same key ordering)

JSONb:

1. store as a binary

2. index(rails4.2) & expression index(rails 5)

3. no need to re-parse (key ordering is not guaranteed)

hStore vs JSONb & JSON

hstore:

1. no nested

2. only string format -> type coercion on both db & app layers -> Virtus gem helper for type coercion.

JSONb & JSON

1. no type coercion

2. can be nested

expression index

add_index :table_name, "(field-'array_key')", using: :gin, name: 'index_table_name_on_field_array_keys'

GiST and GIN

GIN(>100K unique terms) indexes are faster to search, but they take more time to index & process.

GiST(< 100K unique terms) - indexes are slower than GIN indexes, but faster to update.

http://nandovieira.com/using-postgresql-and-jsonb-with-ruby-on-rails

http://nandovieira.com/using-postgresql-and-hstore-with-rails

http://stackoverflow.com/questions/34041821/add-index-on-jsonb-field