I recently stumbled over the presentation "How to Use JSON in MySQL Wrong" by Bill Karwin[1]
While most of the indexing part simply doesn't apply to Postgres, I was curious about the statement that the data type of a json value inside the json matters as well (Slide 56)
Apparently in MySQL storing {"a": 123456789} takes less space than {"a": '123456789'}
So I tested that with Postgres both using json and jsonb - my expectation was, that this would be similar in Postgres as well.
However, it turned out that for a json column there was no difference at all (both versions would show up the same with pg_total_relation_size())
The table size with jsonb was bigger in general, but the one with the "integer" value was even bigger than the one with the "string" storage.
The following little test script:
create table json_length_test1 (id serial primary key, d json);
insert into json_length_test1
select i, jsonb_build_object('a', 1234567890)
from generate_series(1,1e6) t(i);
create table json_length_test2 (id serial primary key, d json);
insert into json_length_test2
select i, jsonb_build_object('a', '1234567890')
from generate_series(1,1e6) t(i);
create table jsonb_length_test1 (id serial primary key, d jsonb);
insert into jsonb_length_test1
select i, jsonb_build_object('a', 1234567890)
from generate_series(1,1e6) t(i);
create table jsonb_length_test2 (id serial primary key, d jsonb);
insert into jsonb_length_test2
select i, jsonb_build_object('a', '1234567890')
from generate_series(1,1e6) t(i);
select 'json', pg_size_pretty(pg_total_relation_size('json_length_test1')) as json_int_size,
pg_size_pretty(pg_total_relation_size('json_length_test2')) as json_text_size
union all
select 'jsonb', pg_size_pretty(pg_total_relation_size('jsonb_length_test1')) as json_int_size,
pg_size_pretty(pg_total_relation_size('jsonb_length_test2')) as json_text_size
Returns (Postgres 12, Windows 10)
?column? | json_int_size | json_text_size
---------+---------------+---------------
json | 71 MB | 71 MB
jsonb | 87 MB | 79 MB
I am a bit surprised by this (not because the jsonb sizes are generally bigger, but that the string value takes less space)
Is this caused by the fact that a string value compresses better internally?
Thomas
[1] https://www.slideshare.net/billkarwin/how-to-use-json-in-mysql-wrong
