What is the size of jsonb ?

[obartunov]

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)