June 15th, 2021

trekking, Himalaya

What is the size of jsonb ?

It's interesting to know how big is your jsonb and it's not trivial as it looks, since jsonb is a binary format and also may be TOASTed (compressed).

Assume, that jb - is an attribute of type jsonb, than
CREATE TABLE test (jb jsonb);
ALTER TABLE test ALTER COLUMN jb SET STORAGE EXTERNAL; -- for easy demonstration
INSERT INTO test
SELECT
  jsonb_build_object(
  'id', i, 
  'foo', (select jsonb_agg(0) from generate_series(1, 1960/12)) -- [0,0,0, ...]
  ) jb
FROM
  generate_series(1, 10000) i;


raw_size = pg_column_size(jb::text::jsonb) -- the size of jsonb in memory
compressed_size = pg_column_size(jb) -- stored size of jsonb (raw_size if not TOAST-ed and non-compressed)

SELECT jb->'id' as id, pg_column_size(jb) as raw_size, pg_column_size(jb::text::jsonb) stored_size FROM test;
id raw_size stored_size
── ──────── ───────────
1      2006        2010
2      1998        1998
3      1998        1998
4      1998        1998
5      1998        1998
6      1998        1998
7      1998        1998
8      1998        1998
9      1998        1998
10     1998        1998
(10 rows)