ftisiot ideas about food and life
who talks suggestions soft json-pg

What are the differences between JSON or JSONB in PostgreSQL®?

Published Jan 6, 2023 by in Postgresql, Json, Jsonb at https://ftisiot.net/postgresqljson/what-are-the-differences-json-jsonb-postgresql/

PostgreSQL® offers two types of data types to handle JSON data:

  • JSON stores the JSON as text, performing a validation on the correctness of the JSON syntax
  • JSONB optimizes the JSON storage in a custom binary format. Therefore, on top of validating the correctness of the JSON format, time is spent to properly parse and store the content.

NOTE: more info is available in the PostgreSQL JSON types documentation page

The following table defines the differences between JSON and JSONB data types in PostgreSQL.

Quality JSON JSONB
Data storage format raw text custom binary
Write faster to write, only minimal JSON validation slower to write due to binary conversion overhead
Read slower to read, reparsing needed faster to read
White spaces preserved removed
Duplicated keys preserved only last valued is kept
Order of keys preserved can be altered
Index support no yes

Review all the JSON PostgreSQL use-cases listed in the main page


Francesco Tisiot

Mastodon Francesco comes from Verona, Italy and works as a Senior Developer Advocate at Aiven. With his many years of experience as a data analyst, he has stories to tell and advice for data-wranglers everywhere. Francesco loves sharing knowledge with others as a speaker and writer, and is on a mission to defend the world from bad Italian food!

Story logo

© 2023