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

On 10/11/19 4:40 AM, Thomas Kellerer wrote:
> 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
typeof 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
aswell.
 
> 
> However, it turned out that for a json column there was no difference at all (both versions would show up the same
withpg_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
withthe "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?

Not sure if it applies here:

https://www.postgresql.org/docs/11/datatype-json.html

"When converting textual JSON input into jsonb, the primitive types 
described by RFC 7159 are effectively mapped onto native PostgreSQL 
types, as shown in Table 8.23. ..."

Table 8.23.
JSON primitive type PostgreSQL type  Notes
...
number                 numeric          NaN and infinity values are disallowed
...
> 
> 
> Thomas
> 
>    [1] https://www.slideshare.net/billkarwin/how-to-use-json-in-mysql-wrong
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com