3

I did some small tests to try these data types, found some puzzling results.

Summary:

jsonb uses more space for integers than strings:

select pg_column_size('{"a":"1"}'::jsonb) as json_size

18

select pg_column_size('{"a":1}'::jsonb) as json_size

28

Why does the size of jsonb inflate when using integers?

Long version:

I was originally interested in using an array of integers, here are those tests. In each pair of comma separated numbers, first corresponds to json_size (bytes), second number is array_size (bytes).

select pg_column_size('[]'::jsonb) as json_size, 
       pg_column_size(array[]::smallint[]) as array_size

8,16

select pg_column_size('[1]'::jsonb) as json_size, 
       pg_column_size(array[1]::smallint[]) as array_size

20,26

Check if jsonb actually stores integers as plain text; if so then this next one should use 4 bytes more for jsonb:

select pg_column_size('[12345]'::jsonb) as json_size, 
       pg_column_size(array[12345]::smallint[]) as array_size

22,26

Strange, it uses 2 bytes more, no idea what that means. Try some more practical and realistic data now:

select pg_column_size('[123,234,345,456,567,678,789,890]'::jsonb) as json_size, 
       pg_column_size(array[123,234,345,456,567,678,789,890]::smallint[]) as array_size

104,40

What happened to jsonb here? What caused it to inflate? Maybe if I use smaller numbers that have less characters...

select pg_column_size('[1,2,3,4,5,6,7,8]'::jsonb) as json_size, 
       pg_column_size(array[1,2,3,4,5,6,7,8]::smallint[]) as array_size

104,40

Nope. What about strings?

select pg_column_size('["1","2","3","4","5","6","7","8"]'::jsonb) as json_size, 
       pg_column_size(array[1,2,3,4,5,6,7,8]::smallint[]) as array_size

48,40

It seems that jsonb likes strings and not integers.

I quickly made it a rule of thumb that I should only use strings in jsonb and use native integers or arrays for numeric data, but I don't know if this rule is misguided or why this anomaly occurs.

Improve this question

1 Answer 1

Reset to default
4

It's simple. JSON doesn't have a concept of an integer, only a "number". From the docs

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.

That table shows a JSON "number" is mapped to a numeric. The max precision on numeric is

up to 131072 digits before the decimal point; up to 16383 digits after the decimal point

However, I don't think it actually stores that. It does store a numeric of some sort. The numeric source is quite frankly kind of difficult to follow.

Using an array of smallint is clearly better if it fits your workload. It has less wasted space for packing, and less wasted space for the data itself. smallint[] however

  • Does not support the same range of numbers.
  • Does not support embedding different types.
  • Does not support any precision at all.
  • Does not exchange well with web-clients.
Improve this answer
4
  • When you need to exchange, then you convert to JSON. That was the original purpose of JSON. You keep things nice, tidy and native database-side, and convert to JSON when talking to (a part of) the rest of the world.
    – joanolo
    Jan 22, 2017 at 9:24
  • That makes sense to have one datatype support all numerics, albeit excessively wasteful to use 12 bytes for simple integers. Perhaps beyond the scope of this question: I wonder why PostgreSQL does this instead of using integers when appropriate? If not for this "issue", I wouldn't have this indecision of whether I should continue switching to jsonb or go back to using a table with many nullable columns (currently leaning towards nullable columns).
    – davidtgq
    Jan 22, 2017 at 22:05
  • 1
    @DavidTan nah, just think about it: PostgreSQL would have to have a complex method of internal promotion and index-rewrites. Consider UPDATE foo SET json = $${"number": $var}$$; where foo.json->>"number" is indexed. The UPDATE would have to lock the whole table, to check if foo.json->>"number" needed internal promotion from interger to something else, and then possibly rewrite the whole table and the index if that was the case. That wouldn't scale, at all.
    – Evan Carroll
    Jan 22, 2017 at 22:25
  • Arrays in Postgres have 24 bytes of overhead, if you just serialized your []short as a bytea and cast it back to short, you'd save a lot of space unless your arrays are very large, on average. That would be difficult to query though.
    – Eloff
    Mar 23 at 21:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.