So, here's a destruction test case:
200,000 JSON values (plus 2 key columns)
Average width 4K (+/- 1K)
183 keys per JSON valuekeys 10 to 30 characters105 float values70 integer values8 text and date valuesno nesting
The "jsonic" table is JSON
The "jsonbish" table is JSONB
(I can't share this data set, but it makes a good test case)
And, we see the effect:
postgres=# select pg_size_pretty(pg_total_relation_size('jsonic'));pg_size_pretty
----------------394 MB
(1 row)
postgres=# select pg_size_pretty(pg_total_relation_size('jsonbish'));pg_size_pretty
----------------1147 MB
(1 row)
So, pretty bad; JSONB is 200% larger than JSON.
I don't think having 183 top-level keys is all that unreasonable of a
use case. Some folks will be migrating from Mongo, Redis or Couch to
PostgreSQL, and might have a whole denormalized schema in JSON.
BTW, I find this peculiar:
postgres=# select pg_size_pretty(pg_relation_size('jsonic'));
pg_size_pretty
----------------383 MB
(1 row)
postgres=# select pg_size_pretty(pg_relation_size('jsonbish'));
pg_size_pretty
----------------11 MB
(1 row)
Next up: Tom's patch and indexing!
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
