Postgres jsonb vs composite type performance differences

It's not really a question of performance,

• Composite Types are strongly typed meaning you have to define their shape.
• JSON is weakly typed.

Moreover, JSON has a use case, but with a composite type you almost never want to use them because it's almost always better normalizing them into their own relations. There is an exception to that though -- when you're going to create a whole library around a set of data then composite types can make everything and nicer. For example stdaddr in PostGIS is one such example. That type represents a physical address and multiple things can take that type (like the PostGIS geocoder).

So you can do

CREATE TABLE foo ( foo_id int, address postgis.stdaddr );

now you can get the geocoded information relatively easily and you only have to pass around one value rather than 15.

The TYPE inventory_item was defined in the question (same as in the guide), so we need only to define tables tc with composite (ROW) type, and tj with JSONb type.

INSERT TIME

-- drop table tc; drop table tj;
CREATE TABLE tc (id serial, x inventory_item);
CREATE TABLE tj (id serial, x JSONb);

EXPLAIN ANALYSE 
  INSERT INTO tc(x) VALUES 
    (ROW('fuzzy dice', 42, 1.99)),
    (ROW('test pi', 3, 3.1415))
; -- Execution Time: try1 0.386 ms; try2 0.559 ms; try3 0.102 ms; ... 
EXPLAIN ANALYSE 
  INSERT INTO tj(x) VALUES 
    ('{"name":"fuzzy dice", "supplier_id":42, "price":1.99}'::jsonb),
    ('{"name":"test pi", "supplier_id":3, "price":3.1415}'::jsonb)
; -- Execution Time: try1 0.343; try2 0.355 ms; try3 0.112 ms; ...

Of course, we need loops, etc. something por complex to test... But seems "comparable" times, no big difference.

SELECT NATIVE TIME

Only retrieving the original datatype. Need good benchmark, but lets imagine something simple only to check big differences.

EXPLAIN ANALYSE   SELECT x, i FROM tc, generate_series(1,999999) g(i); 
EXPLAIN ANALYSE   SELECT x, i FROM tj, generate_series(1,999999) g(i); 

no difference again. Both with "Execution Time: ~460".

EXPLODE TIME

EXPLAIN ANALYSE
  SELECT i, id, (x).name, (x).supplier_id, (x).price
  FROM tc, generate_series(1,999999) g(i)
; -- Execution Time: ~490 ms 
EXPLAIN ANALYSE
  SELECT i, tj.id, t.* 
  FROM tj, generate_series(1,999999) g(i),
       LATERAL jsonb_populate_record(null::inventory_item, tj.x) t  
; -- Execution Time: ~650 ms

Seems that is very fast to transform JSONb-object into SQL-row! Seems a binary cast: we can suppose that the function jsonb_populate_record maps the JSONb types to SQL using by inventory_item internal definition.

And it is faster tham composite table.

Explode and calculate something

EXPLAIN ANALYSE
  SELECT i, (x).supplier_id+i, (x).price+0.01
  FROM tc, generate_series(1,999999) g(i)
; -- Execution Time: ~800 ms

EXPLAIN ANALYSE
  SELECT i,  t.supplier_id+i, t.price+0.01
  FROM tj, generate_series(1,999999) g(i),
       LATERAL jsonb_populate_record(null::inventory_item, tj.x) t  
; -- Execution Time: ~620 ms

Perhaps ~150 ms to calculations, so same expected time... There are some error in the example above, need better benchmark to check real difference.

---

Check comparative time to cast from text.

EXPLAIN ANALYSE -- (supposed to) cast from binary
  SELECT i, id, x->>'name' as name, 
         (x->'supplier_id')::int as supplier_id, (x->'price')::float as price
  FROM tj, generate_series(1,999999) g(i)
; -- Execution Time: ~1600 ms 

EXPLAIN ANALYSE -- cast from text
  SELECT i, id, x->>'name' as name, 
         (x->>'supplier_id')::int as supplier_id, (x->>'price')::float as price
  FROM tj, generate_series(1,999999) g(i)
; -- Execution Time: ~1600 ms 

Long and same times. Seems that (x->'supplier_id')::int it is only a sugar syntax for (x->>'supplier_id')::int or (x->'supplier_id')::text::int.

PS: this answer is also a complement for this other question, about "Binary to binary cast with JSONb".