Search Postgresql Archives

Re: JSON vs. JSONB storage size

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 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?

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@xxxxxxxxxxx





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux