Hi!
On Thu, Apr 15, 2021 at 12:11 PM Dmitry Dolgov <9erthalion6@xxxxxxxxx> wrote:
> > My point was that for JSON, after validating that the input is
> > syntactically correct, we just store it as-received. So in particular
> > the amount of whitespace in the value would depend on how the client
> > had chosen to format the JSON. This'd affect the stored size of
> > course, and I think it would have an effect on compression time too.
>
> Yes, I got it and just wanted to confirm you were right - this was the
> reason I've observed slowdown trying to reproduce the report.
Thank you for trying to reproduce the report. I did a bit more digging
myself and I am still confused.
First, it is important to note that the JSON I am using contains
primarily random strings as values, so not really something which is
easy to compress. See example at [1]. I have realized though that in
the previous benchmark I have been using the same JSON document and
inserting it multiple times, so compression might work across
documents or something. So I ran a version of the benchmark with
different JSONs being inserted (but with the same structure, just
values are random strings). There was no difference.
Second, as you see from [1], the JSON representation I am using is
really compact and has no extra spaces. I also used
pg_total_relation_size to get the size of the table after inserting
10k rows and the numbers are similar, with JSONB being slightly larger
than others. So I think the idea of compression does not hold.
So I do not know what is happening and why you cannot reproduce it.
Maybe explain a bit how you are trying to reproduce it? Directly from
psql console? Are you using the same version as me (13.2)?
Numbers with inserting the same large JSON 10k times:
Type: jsonb
Mean: 200243.1
Stddev: 1679.7741187433503
Size: { pg_total_relation_size: '4611792896' }
Type: json
Mean: 256938.5
Stddev: 2471.9909890612466
Size: { pg_total_relation_size: '4597833728' }
Type: text
Mean: 248175.3
Stddev: 376.677594236769
Size: { pg_total_relation_size: '4597833728' }
Inserting different JSON 10k times:
Type: jsonb
Mean: 202794.5
Stddev: 978.5346442512907
Size: { pg_total_relation_size: '4611792896' }
Type: json
Mean: 259437.9
Stddev: 1785.8411155531167
Size: { pg_total_relation_size: '4597833728' }
Type: text
Mean: 250060.5
Stddev: 912.9207249263213
Size: { pg_total_relation_size: '4597833728' }
[1] https://gitlab.com/mitar/benchmark-pg-json/-/blob/master/example.json
Mitar
--
http://mitar.tnode.com/
https://twitter.com/mitar_m
