What is PostgreSQL's JSONB Type
- A clear comparison of PostgreSQL JSON vs JSONB from performance, indexing, and ops perspectives.
- Gives you defensible criteria for choosing the right document-storage strategy in real systems.
Before We Begin
JSONB is a way to store JSON data in PostgreSQL databases. PostgreSQL actually provides both json and jsonb, and they differ in storage format and query characteristics. When I first used PostgreSQL and encountered JSONB, I simply thought this unfamiliar type was the same as JSON and used it without much consideration. However, when problems arose during use, I organized the content I had to investigate.
JSON
JSON format is a text-based lightweight data exchange format. As a side note, when I first started developing about 10 years ago, data formats like SOAP and XML could often be seen, but now JSON has become commonplace.
JSON in Databases
Multiple databases have introduced JSON types. This is because, as mentioned above, the JSON format has become widespread, and demand for the ability to directly store and process JSON data has grown.
JSON type in MySQL: MySQL also provides a dedicated JSON type, but its implementation details differ from PostgreSQL's json and jsonb. In this post, I focus on the difference between PostgreSQL's two JSON-related types.
JSON / JSONB in PostgreSQL: PostgreSQL added json in version 9.2 and jsonb in version 9.4. json preserves the input text more closely, while jsonb stores a parsed binary representation.
- Comparison Table
| JSON | JSONB | |
|---|---|---|
| Storage | Preserves the original text | Stores a parsed binary representation |
| Input cost | Lower | Higher |
| Reparsing required | Yes for many operations | No, it is already decomposed for processing |
| Indexing | Doesn't support general GIN indexing | Supported |
| Data processing | Slower for most operators | Faster for most operators |
| Whitespaces | Preserves whitespace and key order | Removes insignificant whitespace and does not preserve order |
| Duplicates | Preserved in the original text | Duplicate keys collapse to the last value |
The advantage is that indexing can be created on JSONB data, speeding up searches in large datasets. Since it is already parsed, many operators also work more efficiently on JSONB.
The disadvantage is that JSONB may incur additional input overhead during storage because of the internal conversion process. Also, the stored representation does not preserve whitespace or key order exactly as entered.
Data Comparison in JSONB
When comparing against a JSONB column, it is common to convert a string literal to jsonb first.
SELECT *
FROM table_name
WHERE jsonb_column_name = 'JSON_string_value'::jsonb;To check if it matches the stored value, you need to compare after type conversion with ::jsonb as shown above.
More guides related to JSONB queries can be found in the PostgreSQL documentation and the link below.
Understanding how to handle JSON types in databases allows you to predict how they work and their performance differences.
