Postgres Pro
Downloads
Home   >   mailing lists

Thread: jsonb on-disk size calculation

jsonb on-disk size calculation

From
jian he
Date:

hi.

drop table x;
create table x(js jsonb);
insert into x select '{"Hello world":1}'::jsonb;
select pg_column_size(js) from x; -- return 33.

based on src/include/utils/jsonb.h
The key and value part is 20 bytes (is it correct?), Jsonb->vl_len_  is 4 byte, JsonbContainer->header is 4 bytes. That's 28 bytes.

but now on-disk is 33 bytes. 
so I am not sure where the remaining bytes are.





Re: jsonb on-disk size calculation

From
Adrian Klaver
Date:
On 7/31/23 04:10, jian he wrote:
> 
> hi.
> 
> drop table x;
> create table x(js jsonb);
> insert into x select '{"Hello world":1}'::jsonb;
> select pg_column_size(js) from x;-- return 33.
> 
> based on src/include/utils/jsonb.h
> The key and value part is 20 bytes (is it correct?), Jsonb->vl_len_  is 
> 4 byte, JsonbContainer->header is 4 bytes. That's 28 bytes.
> 
> but now on-disk is 33 bytes.
> so I am not sure where the remaining bytes are.

I don't claim to understand all this but from jsonb.h


/*
  * JsonbValue:  In-memory representation of Jsonb.  This is a convenient
  * deserialized representation, that can easily support using the "val"
  * union across underlying types during manipulation.  The Jsonb on-disk
  * representation has various alignment considerations.
  */


/*
  * Key/value pair within an Object.
  *
  * This struct type is only used briefly while constructing a Jsonb; it is
  * *not* the on-disk representation.
  *
  * Pairs with duplicate keys are de-duplicated.  We store the originally
  * observed pair ordering for the purpose of removing duplicates in a
  * well-defined way (which is "last observed wins").
  */



> 
> 
> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: jsonb on-disk size calculation

From
Junwang Zhao
Date:
convertJsonbObject convert JsonValue to Jsonb.

Jsonb->vl_len_ is 4 byte
JsonbContainer->header is 4 bytes
JsonbContainer->children is an array of two elements, that's 8 bytes
following the k/v part, 20 bytes

In total 36 bytes.

When Jsonb is stored to disk, I guess some conversion reduces the
vl_len_ to 1 byte, hence total 33 bytes,
but I cannot find the conversion logic.

On Mon, Jul 31, 2023 at 7:10 PM jian he <jian.universality@gmail.com> wrote:
>
>
> hi.
>
> drop table x;
> create table x(js jsonb);
> insert into x select '{"Hello world":1}'::jsonb;
> select pg_column_size(js) from x; -- return 33.
>
> based on src/include/utils/jsonb.h
> The key and value part is 20 bytes (is it correct?), Jsonb->vl_len_  is 4 byte, JsonbContainer->header is 4 bytes.
That's28 bytes. 
>
> but now on-disk is 33 bytes.
> so I am not sure where the remaining bytes are.
>
>
>
>
>


--
Regards
Junwang Zhao