JSON vs. JSONB storage size - Mailing list pgsql-general

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
ofa 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
aswell. 
 

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