Want to Contribute to us or want to have 15k+ Audience read your Article ? Or Just want to make a strong Backlink?

A look into JSONB in YugabyteDB

To see how JSON is saved in YugabyteDB, we have now to try each PostgreSQL and YugabyteDB.

Let’s create two check tables for investigation:

drop desk if exists test1;
create desk test1 
(
id int main key,
val json
)
break up into 1 tablets
;
drop desk if exists test2;
create desk test2 
(
id int main key,
val jsonb
)
break up into 1 tablets
;
Enter fullscreen mode

Exit fullscreen mode

The explanation I create two tables is to have the ability to see the distinction between PostgreSQL JSON and JSONB. The JSON information kind in PostgreSQL is storing JSON as textual content, whereas the JSONB is storing JSON binary. The recommendation from the PostgreSQL documentation is to make use of JSONB.

With the tables in place, let’s create some rows in each:

-- test1 / json
insert into test1 values (1,'{ "f1": "aaaaa", "f2": "bbbbb", "f3": "ccccc", "f4": "ddddd", "f5": "eeeee" }');
insert into test1 values (2,'{ "f1": "fffff", "f2": "ggggg", "f3": "hhhhh", "f4": "iiiii", "f5": "jjjjj" }');
insert into test1 values (3,'{ "f1": "kkkkk", "f2": "lllll", "f3": "mmmmm", "f4": "nnnnn", "f5": "ooooo" }');
-- test2 / jsonb
insert into test2 values (1,'{ "f1": "aaaaa", "f2": "bbbbb", "f3": "ccccc", "f4": "ddddd", "f5": "eeeee" }');
insert into test2 values (2,'{ "f1": "fffff", "f2": "ggggg", "f3": "hhhhh", "f4": "iiiii", "f5": "jjjjj" }');
insert into test2 values (3,'{ "f1": "kkkkk", "f2": "lllll", "f3": "mmmmm", "f4": "nnnnn", "f5": "ooooo" }');
Enter fullscreen mode

Exit fullscreen mode

With the information being inserted in YugabyteDB, let’s take a look at the way it’s saved in YugabyteDB. So as to do this, entry to a pill server that hosts a reproduction of the desk.

The explanation for the ‘break up into 1 tablets’ clause is to simplify discovering the information: if the desk would have been break up into extra tablets, the row can be distributed over the tablets primarily based on the first key definition. With the default main key definition the row would have been randomly distributed over the tablets due to hash sharding. With 1 pill, all of the inserted rows can be within the single pill.

The only technique to perceive the place a pill is hosted in your YugabyteDB cluster is to make use of an upcoming model of yb_stats to lookup the desk specifics:

% yb_stats --print-entities --table-name-match 'check(1|2)'
Desk:    ysql.yugabyte.test1, state: RUNNING, id: 000033e8000030008000000000004232
Pill:   ysql.yugabyte.test1.f1c52b62513f461095b13cb0425b2ef0 state: RUNNING
            ( VOTER,yb-1.native:9100,FOLLOWER VOTER,yb-3.native:9100,LEADER VOTER,yb-2.native:9100,FOLLOWER )
Desk:    ysql.yugabyte.test2, state: RUNNING, id: 000033e8000030008000000000004237
Pill:   ysql.yugabyte.test2.a535c9ff10fb4fb2b874770ce5cb4ac7 state: RUNNING
            ( VOTER,yb-1.native:9100,FOLLOWER VOTER,yb-3.native:9100,FOLLOWER VOTER,yb-2.native:9100,LEADER )
Enter fullscreen mode

Exit fullscreen mode

This queries the entities (objects) from the reside database, and exhibits you the 2 tables test1 and test2 within the yugabyte database, the desk id, and the pill of every of the tables. It additionally exhibits the replicas that every pill has. Within the case of a replication issue 3 cluster with 3 nodes, it is apparent that every node will get a reproduction.

This implies we will get to any of the nodes to take a look at the pill information for every of the tables: it does not matter if a node hosts a follower or chief of a pill for the 2 steps that we have to do subsequent.

Step one is to make rocksdb retailer the desk information to an SST file. This would possibly come as a shock: the information is inserted into YSQL, and dedicated due to auto commit, but there is no such thing as a datafile.

There are two issues which can be necessary right here: to start with: there is no such thing as a danger of data-loss, as a result of DocDB offers WAL (write forward log), which persists any modification to a rocksdb database. The second factor is that LSM-tree offers it is first layer of storage in a memtable, which is, just like the title suggests, reminiscence solely.

If the memtable grows past the utmost memtable dimension, then a brand new memtable is created, and the present memtable is made immutable, sorted and saved to disk as SST file.

So as to learn the precise information, we have now to ask DocDB to (prematurely) persist the memtable so we will learn the rocksdb contents. That is finished utilizing a yb-admin command. The above --print-entities command prints the desk id (please thoughts to make use of the desk id, plenty of different objects in YugabyteDB have id’s too!), which can be utilized to flush the pill information to disk:

$ yb-admin -init_master_addrs localhost:7100 flush_table_by_id 000033e8000030008000000000004232 600
Flushed [000033e8000030008000000000004232] tables.
$ yb-admin -init_master_addrs localhost:7100 flush_table_by_id 000033e8000030008000000000004237 600
Flushed [000033e8000030008000000000004237] tables.
Enter fullscreen mode

Exit fullscreen mode

This may generate an .sst file in:

{fs_data_dirs}/yb-data/tserver/information/rocksdb/table-{desk id}/tablet-{pill id}
Enter fullscreen mode

Exit fullscreen mode

If we go to the pill listing of table1 alias the JSON desk, we will then dump the sst file contents within the following means:

$ sst_dump --command=scan --file=. --output_format=decoded_regulardb
from [] to []
Course of ./000010.sst
Sst file format: block-based
SubDocKey(DocKey(0x1210, [1], []), [SystemColumnId(0); HT{ physical: 1669383197054696 }]) -> null
SubDocKey(DocKey(0x1210, [1], []), [ColumnId(1); HT{ physical: 1669383197054696 w: 1 }]) -> "x9d{ "f1": "aaaaa", "f2": "bbbbb", "f3": "ccccc", "f4": "ddddd", "f5": "eeeee" }"
SubDocKey(DocKey(0xc0c4, [2], []), [SystemColumnId(0); HT{ physical: 1669383197061929 }]) -> null
SubDocKey(DocKey(0xc0c4, [2], []), [ColumnId(1); HT{ physical: 1669383197061929 w: 1 }]) -> "x9d{ "f1": "fffff", "f2": "ggggg", "f3": "hhhhh", "f4": "iiiii", "f5": "jjjjj" }"
SubDocKey(DocKey(0xfca0, [3], []), [SystemColumnId(0); HT{ physical: 1669383197066513 }]) -> null
SubDocKey(DocKey(0xfca0, [3], []), [ColumnId(1); HT{ physical: 1669383197066513 w: 1 }]) -> "x9d{ "f1": "kkkkk", "f2": "lllll", "f3": "mmmmm", "f4": "nnnnn", "f5": "ooooo" }"
Enter fullscreen mode

Exit fullscreen mode

Now let’s check out the output.

All of the SubDocKeys comprise a DocKey. The DocKey shops the first key. The primary column within the DocKey is a hash worth of the row, the second column is the precise main key worth.

The primary row within the output above is the SystemColumnId, which is invisible on the consumer layer, and is usually known as ‘liveness column’. This column is used to point the row presence.

The row after the SystemColumnId row is the second column within the desk. In case you examine the row information after ‘->’, you see that the precise JSON textual content is seen. In different phrases: regardless of being a JSON column, the JSON textual illustration is saved on the DocDB layer, and is what’s offered to YSQL if the column is requested.

In all rows alias SubDocKeys you discover information with ‘HT’: the Hybrid (Logical Clock) Time. That is how MVCC is carried out.

Now let’s go to the test2 desk information, and use the sst_dump instrument to see how the JSONB information appears to be like like:

$ sst_dump --command=scan --file=. --output_format=decoded_regulardb
from [] to []
Course of ./000010.sst
Sst file format: block-based
SubDocKey(DocKey(0x1210, [1], []), [SystemColumnId(0); HT{ physical: 1669383197078672 }]) -> null
SubDocKey(DocKey(0x1210, [1], []), [ColumnId(1); HT{ physical: 1669383197078672 w: 1 }]) -> "x05x00x00 x02x00x00x80x02x00x00x00x02x00x00x00x02x00x00x00x02x00x00x00x05x00x00x00x05x00x00x00x05x00x00x00x05x00x00x00x05x00x00x00f1f2f3f4f5aaaaabbbbbcccccdddddeeeee"
SubDocKey(DocKey(0xc0c4, [2], []), [SystemColumnId(0); HT{ physical: 1669383197086482 }]) -> null
SubDocKey(DocKey(0xc0c4, [2], []), [ColumnId(1); HT{ physical: 1669383197086482 w: 1 }]) -> "x05x00x00 x02x00x00x80x02x00x00x00x02x00x00x00x02x00x00x00x02x00x00x00x05x00x00x00x05x00x00x00x05x00x00x00x05x00x00x00x05x00x00x00f1f2f3f4f5fffffggggghhhhhiiiiijjjjj"
SubDocKey(DocKey(0xfca0, [3], []), [SystemColumnId(0); HT{ physical: 1669383198094809 }]) -> null
SubDocKey(DocKey(0xfca0, [3], []), [ColumnId(1); HT{ physical: 1669383198094809 w: 1 }]) -> "x05x00x00 x02x00x00x80x02x00x00x00x02x00x00x00x02x00x00x00x02x00x00x00x05x00x00x00x05x00x00x00x05x00x00x00x05x00x00x00x05x00x00x00f1f2f3f4f5kkkkklllllmmmmmnnnnnooooo"
Enter fullscreen mode

Exit fullscreen mode

The format of the sst information is similar: the first key sits within the DocKey, we acquired a ‘liveness column’ per row, and the JSONB column is singular. The info within the JSONB column differs from the JSON column: the JSON information is saved in a binary means so it may be used straight by YSQL.

Add a Comment

Your email address will not be published. Required fields are marked *

Want to Contribute to us or want to have 15k+ Audience read your Article ? Or Just want to make a strong Backlink?