Re: JSON vs. JSONB storage size

>>>>> "Thomas" == Thomas Kellerer <spam_eater(at)gmx(dot)net> writes:

Thomas> The table size with jsonb was bigger in general, but the one
Thomas> with the "integer" value was even bigger than the one with the
Thomas> "string" storage.

jsonb stores numeric values as "numeric", not as integers or floats, so
the storage needed will depend on the number of decimal digits.

The size results you're seeing are mainly the consequence of the fact
that jsonb stores the whole Numeric datum, varlena header included (and
without packing the header), so there's an extra 4 bytes you might not
have accounted for: 1234567890 is three numeric "digits" (2 bytes each)
plus a 2 byte numeric header (for weight/scale/sign) plus the 4 byte
varlena header, for 12 bytes total, whereas "1234567890" takes only 10
(since the length is encoded in the jsonb value offsets). Furthermore,
there may be up to 3 padding bytes before the numeric value.

I think in your test, the extra 3 bytes is pushing the size of a single
row up to the next multiple of MAXALIGN, so you're getting slightly
fewer rows per page. I don't know what Windows is doing, but on my
system (freebsd amd64) I get 136 rows/page vs. 120 rows/page, which
would make a million rows take 57MB or 65MB. (Your use of
pg_total_relation_size is including the pkey index, which confuses the
results a bit.)

--
Andrew (irc:RhodiumToad)