From: | Mitar <mmitar(at)gmail(dot)com> |
---|---|
To: | Dmitry Dolgov <9erthalion6(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Why is writing JSONB faster than just JSON? |
Date: | 2021-04-23 08:56:57 |
Message-ID: | CAKLmikOg2i3PxPNPrxcMJ1hsuOoicwxC8_xoK7TF4OBjjKEaKw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi!
On Thu, Apr 15, 2021 at 12:11 PM Dmitry Dolgov <9erthalion6(at)gmail(dot)com> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Matthias Apitz | 2021-04-23 09:32:02 | Re: client waits for end of update operation and server proc is idle |
Previous Message | Karsten Hilbert | 2021-04-23 08:53:57 | Re: client waits for end of update operation and server proc is idle |