This week I had the opportunity to play around with JSONb, a feature added to Postgres 9.4. It is similar to the native JSON column introduced in 9.3 but with some extra benefits. The two largest values that I have found so far is that JSONb can be indexed and the cost of selecting keys is quite a bit less. I also made a Ruby/Docker playground you can use to run some tests yourself. Check out jsonb_tests if you would like to try some of these features out yourself!
Benchmarks Against Different Types
For the listed benchmarks I created two tables, a
users table and a
customers table. They both have a million records with a
details field that
has the following structure:
The difference being that the
users table is JSONb with a
GIN index and the
customers table is using standard JSON.
Different Ways to Count
Raw Read Speed
OR Type Selection