First, hstore is a contrib module, which only allows you to store key => value pairs, where keys and values can only be texts (however values can be sql NULLs too).
Both json & jsonb allows you to store a valid JSON value (defined in its spec).
F.ex. these are valid JSON representations: null, true, [1,false,"string",{"foo":"bar"}], {"foo":"bar","baz":[null]} - hstore is just a little subset compared to what JSON is capable (but if you only need this subset, it's fine).
The only difference between json & jsonb is their storage:
jsonis stored in its plain text format, whilejsonbis stored in some binary representation
There are 3 major consequences of this:
jsonbusually takes more disk space to store thanjson(sometimes not)jsonbtakes more time to build from its input representation thanjsonjsonoperations take significantly more time thanjsonb(& parsing also needs to be done each time you do some operation at ajsontyped value)
When jsonb will be available with a stable release, there will be two major use cases, when you can easily select between them:
- If you only work with the JSON representation in your application, PostgreSQL is only used to store & retrieve this representation, you should use
json. - If you do a lot of operations on the JSON value in PostgreSQL, or use indexing on some JSON field, you should use
jsonb.
PostgreSQL
postgresql.org › docs › current › datatype-json.html
PostgreSQL: Documentation: 18: 8.14. JSON Types
August 13, 2026 - Primitive JSON values are compared ... the default database collation. The jsonb data type supports array-style subscripting expressions to extract and modify elements....
Address: 178 Howard Place, Gulf, Washington, 702
PostgreSQL
postgresql.org › docs › 9.5 › functions-json.html
PostgreSQL: Documentation: 9.5: JSON Functions and Operators
February 11, 2021 - Note: Many of these functions and operators will convert Unicode escapes in JSON strings to the appropriate single character. This is a non-issue if the input is type jsonb, because the conversion was already done; but for json input, this may result in throwing an error, as noted in Section 8.14.
When do JSON columns make sense in Postgres?
We use them in three cases Changelogs. When a row changes in a particular table we store a JSONB dump of it with a diff from the old state in an events table with thing_id, changed_at, state, diff where state and diff are JSONB. Fetching data from external apis. We store the response data in a jsonb and use a typed VIEW like CREATE VIEW typed_api_data AS SELECT (data->>'amount')::integer FROM api_data; to read from it. If the api unexpectedly changed a datatype we'll still have all the data but can just fix our view. Storing search parameters from elasticsearch. Use JSONB when you can't use regular typed columns. More on reddit.com
21
16
February 8, 2022When to use JSONB vs. separate columns
JSON is great when you want dynamic fields, i.e. can be configured or changed on the go or differ depending on row data. For example we use it for settings that changes depending on the type some data in a table. We never need to query these settings and it allows flexibility for different type of fields. Separate columns are the go-to for SQL when you know the data, it's faster to query (even though you can index JSON fields) and structured data is easier to handle. In your case, I wouldn't use JSON. Address fields are tricky though, if you plan to support global cases then read up on data structure. More on reddit.com
14
7
April 11, 2021How does PostgreSQL keeps long data such as JSONB.
May I offer you some TOAST ? Sorry, had to make this joke... More on reddit.com
9
4
March 7, 2024Single Database Table with a JSONB column
Don't discuss. Measure. More on reddit.com
11
2
April 4, 202423:19
Even JSONB In Postgres Needs Schemas | POSETTE 2024 - YouTube
04:58
Faster queries with index on JSONB columns in Postgres - YouTube
05:05
How to store and query JSON data in Postgres - YouTube
JSON & JSONB Types in PostgreSQL | Part 1
00:39
JSONB Data Insertion in PostgreSQL: Quick Guide! #postgresql ...
00:27
PostgreSQL JSONB Can Be Weird... Here’s Why ⚠️ - YouTube
Top answer 1 of 9
657
First, hstore is a contrib module, which only allows you to store key => value pairs, where keys and values can only be texts (however values can be sql NULLs too).
Both json & jsonb allows you to store a valid JSON value (defined in its spec).
F.ex. these are valid JSON representations: null, true, [1,false,"string",{"foo":"bar"}], {"foo":"bar","baz":[null]} - hstore is just a little subset compared to what JSON is capable (but if you only need this subset, it's fine).
The only difference between json & jsonb is their storage:
jsonis stored in its plain text format, whilejsonbis stored in some binary representation
There are 3 major consequences of this:
jsonbusually takes more disk space to store thanjson(sometimes not)jsonbtakes more time to build from its input representation thanjsonjsonoperations take significantly more time thanjsonb(& parsing also needs to be done each time you do some operation at ajsontyped value)
When jsonb will be available with a stable release, there will be two major use cases, when you can easily select between them:
- If you only work with the JSON representation in your application, PostgreSQL is only used to store & retrieve this representation, you should use
json. - If you do a lot of operations on the JSON value in PostgreSQL, or use indexing on some JSON field, you should use
jsonb.
2 of 9
194
Peeyush:
The short answer is:
- If you are doing a lot of JSON manipulation inside PostgreSQL, such as sorting, slicing, splicing, etc., you should use JSONB for speed reasons.
- If you need indexed lookups for arbitrary key searches on JSON, then you should use JSONB.
- If you are doing neither of the above, you should probably use JSON.
- If you need to preserve key ordering, whitespace, and duplicate keys, you should use JSON.
For a longer answer, you'll need to wait for me to do a full "HowTo" writeup closer to the 9.4 release.
GeeksforGeeks
geeksforgeeks.org › postgresql › what-is-jsonb-in-postgresql
What is JSONB in PostgreSQL? - GeeksforGeeks
July 23, 2025 - The details column is of type JSONB, which means it will hold the product attributes in JSON format. A new row is inserted with the name value 'Smartphone' and details containing the JSON data.
Snowflake
snowflake.com › en › blog › engineering › postgres-jsonb-columns-and-toast
Postgres JSONB Columns and TOAST: A Performance Guide
June 16, 2026 - How does JSON work in a database like Postgres that is optimized for rows and columns? Like the text, bytea and geometry types, the jsonb type is "variable length" — there is no limit to how big it can be.
Crunchy Data Blog
crunchydata.com › blog › indexing-jsonb-in-postgres
Indexing JSONB in Postgres | Crunchy Data Blog
It works by first accessing the details jsonb column, extracting the value associated with the order_total key as text using the ->> operator, and then casting that text value to a numeric type. Postgres then builds a standard B-tree index on these resulting numeric values.
Medium
mareks-082.medium.com › postgresql-jsonb-in-net-25fbcc7b64b2
PostgreSQL JSONB in .NET. PostgreSQL got some new JSON features… | by Marek Sirkovský | Medium
July 29, 2025 - One thing worth mentioning is that JSONB is a TOASTable type. That means that PostgreSQL can store large JSONB documents efficiently by compressing and storing them in a separate TOAST (The Oversized-Attribute Storage Technique) table.
Architecture Weekly
architecture-weekly.com › architecture weekly › postgresql jsonb - powerful storage for semi-structured data
PostgreSQL JSONB - Powerful Storage for Semi-Structured Data
April 21, 2025 - PostgreSQL's JSONB tokenisation is sneaky. It not only parses data but also preserves the actual data types of values. Each value in the binary format includes a type that identifies whether it's a string, number, boolean, null, array, or object.
DB Vis
dbvis.com › thetable › json-vs-jsonb-in-postgresql-a-complete-comparison
JSON vs JSONB - A complete comparison
October 22, 2025 - In general, you should use jsonb whenever you have to deal with dynamic JSON data or when you need to perform advanced queries. PostgreSQL supports JSON data through the json vs jsonb data types.
Reddit
reddit.com › r/postgresql › when do json columns make sense in postgres?
r/PostgreSQL on Reddit: When do JSON columns make sense in Postgres?
February 8, 2022 -
I'm considering using JSON for fields that I never use in WHERE clauses and which are only used for a relatively small subset of rows in the table like attributes for certain customers.
Top answer 1 of 9
19
We use them in three cases Changelogs. When a row changes in a particular table we store a JSONB dump of it with a diff from the old state in an events table with thing_id, changed_at, state, diff where state and diff are JSONB. Fetching data from external apis. We store the response data in a jsonb and use a typed VIEW like CREATE VIEW typed_api_data AS SELECT (data->>'amount')::integer FROM api_data; to read from it. If the api unexpectedly changed a datatype we'll still have all the data but can just fix our view. Storing search parameters from elasticsearch. Use JSONB when you can't use regular typed columns.
2 of 9
7
One example is data that is only accessed document by document, accessed through an indexed key. Another is properties that might become useful in the future, that can be kept there for now, then extracted to a new column if it becomes relevant.
DataCamp
datacamp.com › doc › postgresql › querying-&-filtering-json-fields
PostgreSQL Querying & Filtering JSON Fields
`JSON` stores data in a text format, while `JSONB` stores data in a decomposed binary format, allowing for efficient indexing and querying. JSON functions are used in PostgreSQL to access and filter JSON data stored in columns of type `JSON` or `JSONB`. They enable complex data retrieval and ...
Neon
neon.com › postgresql › json-functions › jsonb-operators
PostgreSQL JSONB Operators
JSONB operators work the same on any PostgreSQL deployment. Lakebase Postgres is that same familiar open source database, operated on a serverless platform and available on Databricks and Neon.
Reddit
reddit.com › r/postgresql › when to use jsonb vs. separate columns
r/PostgreSQL on Reddit: When to use JSONB vs. separate columns
April 11, 2021 -
I'm building a social media-like application and trying to model the tables.
I realise Postgres now supports JSONB which is exciting, but I'm unsure when to use it.
I have a few examples where I think it could be used:
A user signs up and they specifiy their address. Should the address be a JSONB or separate fields. E.g.,
JSONB: { address1: "", address2: "", zipCode: "" } Columns: address1 (Varchar), address2 (varchar)...
Also things like posts, comments, upvotes etc. They feel like they could be JSONB as well? I'm really not sure!
Post info: { id: uuid, description: "", upvotes: 4 } Columns: id (uuid), description (varchar(255)), upvotes: int
Thanks all.
Top answer 1 of 4
3
JSON is great when you want dynamic fields, i.e. can be configured or changed on the go or differ depending on row data. For example we use it for settings that changes depending on the type some data in a table. We never need to query these settings and it allows flexibility for different type of fields. Separate columns are the go-to for SQL when you know the data, it's faster to query (even though you can index JSON fields) and structured data is easier to handle. In your case, I wouldn't use JSON. Address fields are tricky though, if you plan to support global cases then read up on data structure.
2 of 4
2
You might consider JSON(B) if you don't need to search against it often.
Heap
heap.io › blog › when-to-avoid-jsonb-in-a-postgresql-schema
When To Avoid JSONB In A PostgreSQL Schema | Heap
Let’s say we instead store our measurements as JSONB blobs, like this: ... The performance is dramatically worse — a whopping 584 seconds on my laptop, about 2000x slower: https://explain.depesz.com/s/zJiT · The underlying reason is that PostgreSQL doesn’t know how to keep statistics on the values of fields within JSONB columns.
PostgreSQL
postgresql.org › docs › current › functions-json.html
PostgreSQL: Documentation: 18: 9.16. JSON Functions and Operators
August 13, 2026 - As an extension to the SQL standard, a PostgreSQL path expression can be a Boolean predicate, whereas the SQL standard allows predicates only within filters. While SQL-standard path expressions return the relevant element(s) of the queried JSON value, predicate check expressions return the single three-valued jsonb result of the predicate: true, false, or null.
TigerData
tigerdata.com › learn › how-to-query-jsonb-in-postgresql
How to Query JSONB in PostgreSQL | Tiger Data
February 17, 2025 - In summary, JSONB is a powerful tool for handling JSON data within your PostgreSQL database. It allows the user to easily store, search, and manipulate complex data structures, making it a valuable asset for many applications.
Beekeeper Studio
beekeeperstudio.io › blog › jsonb-functions-in-postgresql
Master JSONB Functions in PostgreSQL: A Practical Guide | Beekeeper Studio
August 24, 2025 - Advanced functions: Complex operations like jsonb_set, jsonb_each_text, and jsonb_array_elements ... These capabilities make PostgreSQL an excellent choice for applications that need to store and query semi-structured JSON data alongside traditional relational data.
ClickHouse
clickhouse.com › blog › elasticsearch-log-analytics-clickhouse
Do you still need Elasticsearch for log analytics? ClickHouse says no. | ClickHouse
May 22, 2026 - ClickHouse now brings full-text search and large-scale analytics together in one engine, making it a powerful alternative to Elasticsearch for log analytics. This benchmark shows why.
Medium
medium.com › @suffyan.asad1 › an-introduction-to-working-with-json-data-in-postgresql-730aa889c5d3
An introduction to working with JSON data in PostgreSQL | by Suffyan Asad | Medium
September 4, 2023 - The recommendation in the PostgreSQL documentation is: In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about the ordering of object keys.
Medium
medium.com › @richardhightower › jsonb-postgresqls-secret-weapon-for-flexible-data-modeling-cf2f5087168f
JSONB: PostgreSQL’s Secret Weapon for Flexible Data Modeling | by Rick Hightower | Medium
May 4, 2025 - Binary Data: JSONB is not suitable for storing binary data — use bytea instead. PostgreSQL’s JSONB type bridges the gap between traditional relational databases and document stores.