Re: Performance of JSON type in postgres

On Mon, Jul 14, 2025 at 2:01 PM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Mon, Jul 14, 2025 at 12:54 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> wrote:
>
>> On 7/14/25 12:51, veem v wrote:
>> > So I want to
>> > understand the experts' opinion on this which I believe will be
>> > crucial during design itself.
>>
>> It is spelled out here:
>>
>> https://www.postgresql.org/docs/current/datatype-json.html
>>
>>
> I've taken to heart the main takeaway from that page:
>
> "In general, most applications should prefer to store JSON data as jsonb,
> unless there are quite specialized needs, such as legacy assumptions about
> ordering of object keys."
>

I don't think the documentation is accurate at all, unless one of those
specialized needs is to 'be faster'. json serialization is more than 2x
faster based on simple testing (see below). This is absolutely not a
trivial difference.

I would say, use json for serialization, use jsonb for data storage, unless
the precise structure of the input document is important.

merlin

leaselock_iam(at)leaselock_prod=> explain analyze select json_agg(l) from (
select l from llcore.lease l limit 10000) q;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=405.52..405.53 rows=1 width=32) (actual
time=69.043..69.048 rows=1 loops=1)
-> Limit (cost=0.00..380.52 rows=10000 width=247) (actual
time=0.017..9.764 rows=10000 loops=1)
-> Seq Scan on lease l (cost=0.00..100383.89 rows=2638089
width=247) (actual time=0.016..8.831 rows=10000 loops=1)
Planning Time: 0.109 ms
Execution Time: 69.088 ms
(5 rows)

Time: 160.560 ms
leaselock_iam(at)leaselock_prod=> explain analyze select jsonb_agg(l) from (
select l from llcore.lease l limit 10000) q;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=405.52..405.53 rows=1 width=32) (actual
time=146.139..146.141 rows=1 loops=1)
-> Limit (cost=0.00..380.52 rows=10000 width=247) (actual
time=0.017..20.837 rows=10000 loops=1)
-> Seq Scan on lease l (cost=0.00..100383.89 rows=2638089
width=247) (actual time=0.016..19.975 rows=10000 loops=1)
Planning Time: 0.108 ms
Execution Time: 152.277 ms