Unlock PostgreSQL’s NoSQL Power: JSON vs JSONB Performance & Full‑Text Search

1. JSON and JSONB Data Types

PostgreSQL provides two JSON data types: json (textual storage) and jsonb (binary storage). Both support similar operators, but they differ in storage format, key order preservation, duplicate‑key handling, and performance characteristics.

Example: create a table with a JSON column and insert sample data.

mydb=> CREATE TABLE test_json1 (id serial primary key, name json);
mydb=> INSERT INTO test_json1 (name) VALUES ('{"col1":1,"col2":"francs","col3":"male"}');
mydb=> SELECT * FROM test_json1;

Querying a JSON field uses the -> operator for JSON values and ->> for text values.

mydb=> SELECT name -> 'col2' FROM test_json1 WHERE id=1;   -- returns "francs"
mydb=> SELECT name ->> 'col2' FROM test_json1 WHERE id=1; -- returns francs

Key differences:

JSON stores data as text; each read requires reparsing.

JSONB stores pre‑parsed binary data; reads are faster, writes are slower.

JSONB discards whitespace and normalises key order; duplicate keys are removed, keeping the last occurrence.

JSON preserves input order and duplicate keys.

JSONB Operators and Functions

Common operators include @>, ?>, ?&, and ?|. Functions such as jsonb_set, jsonb_each, and row_to_json enable modification and conversion.

mydb=> SELECT '{"name":"francs","age":"31"}'::jsonb || '{"sex":"male"}'::jsonb;
-- Result: {"age": "31", "sex": "male", "name": "francs"}

mydb=> SELECT jsonb_set('{"name":"francs","age":"31"}'::jsonb,'{age}','"32"'::jsonb,false);
-- Result: {"age": "32", "name": "francs"}

2. JSON vs JSONB Read/Write Performance Test

A three‑table setup is used: user_ini (plain columns), tbl_user_json (json column), and tbl_user_jsonb (jsonb column). Two million rows are generated with generate_series and inserted via row_to_json.

mydb=> CREATE TABLE user_ini(id int4, user_id int8, user_name varchar(64), create_time timestamp with time zone default clock_timestamp());
mydb=> INSERT INTO user_ini SELECT r, round(random()*2000000), r||'_francs' FROM generate_series(1,2000000) AS r;
mydb=> CREATE TABLE tbl_user_json(id serial, user_info json);
mydb=> CREATE TABLE tbl_user_jsonb(id serial, user_info jsonb);
mydb=> INSERT INTO tbl_user_json(user_info) SELECT row_to_json(user_ini) FROM user_ini;   -- ~13 s
mydb=> INSERT INTO tbl_user_jsonb(user_info) SELECT row_to_json(user_ini)::jsonb FROM user_ini; -- ~20 s

Space usage shows JSONB tables are larger (333 MB) than JSON tables (281 MB) for the same row count.

Read performance is measured with key‑value queries. A GIN index on the whole JSONB column enables fast containment checks, while a btree index on a specific key ( user_info->>'user_name') speeds equality searches.

mydb=> CREATE INDEX idx_gin ON tbl_user_jsonb USING gin(user_info);
mydb=> CREATE INDEX idx_jsonb ON tbl_user_jsonb USING btree ((user_info->>'user_name'));
mydb=> EXPLAIN ANALYZE SELECT * FROM tbl_user_jsonb WHERE user_info->>'user_name'='1_francs';
-- With btree index: execution time ~0.06 ms (index used)
mydb=> EXPLAIN ANALYZE SELECT * FROM tbl_user_jsonb WHERE user_info @> '{"user_name": "2_francs"}';
-- Without index: seq scan ~582 ms; after GIN index creation: ~0.11 ms

Range scans on numeric keys ( id) also benefit from btree indexes, with JSONB consistently faster than JSON (≈8 ms vs ≈27 ms for 10 k rows).

3. Full‑Text Search Support for JSON/JSONB (PostgreSQL 10)

PostgreSQL 10 extends to_tsvector to accept json and

jsonb> as input, enabling full‑text indexing of JSON documents.</p>
<p>Typical workflow:</p>
<ol>
<li>Create a GIN index on <code>to_tsvector('english', jsonb_column)

. Query with to_tsvector(... ) @@ to_tsquery(...) .

mydb=> CREATE INDEX idx_gin_search_json ON tbl_user_search_json USING gin(to_tsvector('english', user_info));
mydb=> SELECT * FROM tbl_user_search_json WHERE to_tsvector('english', user_info) @@ to_tsquery('english','KTU89H');
-- With index: execution time ~0.06 ms (bitmap heap scan)
-- Without index: seq scan ~8062 ms

Because the index was built on the two‑argument form of to_tsvector , queries must use the same form to benefit from the index.

4. Conclusion

The article demonstrates that PostgreSQL’s JSONB type offers superior read performance and richer indexing options compared to plain JSON, while JSON remains slightly faster for bulk inserts. PostgreSQL 10’s added full‑text search support for JSON/JSONB further expands its NoSQL capabilities, allowing efficient text queries on semi‑structured data.