After the JSON datatype became one of the primitive data types in PostgreSQL, there have been many improvements and many related materials have been also published. However, there are few which deal with indexes for JSON type and their performance. In this post, we’ll describe JSON datatype in brief and discuss its indexes and their performance.
What is JSON?
JSON(JavaScript Object Notation) is the open-standard format for interchanging data objects which consist of Name/Value pairs. Although JSON is independent with programing languages, as it follows JavaScript grammar format, it is text format familiar to C-family languages. So, it is very easy for programmers to read, write, also easy for machine to parse and generate, so that lots of programing languages support JSON.
JSON in PostgreSQL
Since PostgreSQL 9.2, JSON has been a primitive data type in PostgreSQL. One can store JSON object in a column of JSON datatypes, and by using this one can use PostgreSQL as a schema-less NoSQL database. One of the defects of relation databases is that it is not flexible for column addition/deletion. But using JSON datatypes, it can be flexible with column addition/deletions.
Another datatype related to JSON object, JSONB, was also added in PostgreSQL 9.4. The JSON datatype stores JSON objects in text format, so it has disadvantages to parse and analyze the text-format JSON objects every time it is processed. In addition, it stores unnecessary white spaces and duplicated keys. JSONB datatype converts text-format JSON object into a binary format. And doing that, it removes unnecessary white spaces and duplicated keys, and sorts the keys. Because of these pre-processing, JSONB consumes more spaces and processing power than JSON. However data processing for JSONB becomes much more efficient. Of course, all operators of JSON and additional Containment and Existence operators are supported for JSONB, and moreover it has an advantage to use GIN index.
| GiN Index GiN( Generalized Inverted Index ) is inverted index, a structure that has only one single index entry per a key and store the mapping information(posting list) of all key row to have the same value in the index entry. In postgreSQL, the key is stored in the index entry and mapping information for the key is stored in the posting tree. To search the index entry and posting tree is using a B-Tree. Therefore, GIN is useful when an index must map many values to on row, such as indexing array, documents.
|
JSONB and Indexes
When we use ->> operator of JSONB, PostgreSQL can use B-tree or Hash index for processing the operations. ->> oerator returns the value of the specified attribute in text format. PostgreSQL can use indexes for the text results as compare operands. GIN index can be used by GIN jsonb operator class.
| GIN jsonb operator classes Default operator class jsonb_ops supports existence operators( ?, ?&, ?| ) and containment operator( @> ). And jsonb_path_ops supports containment operator only. Therefore GIN index is only possible to search for that have a particular key or key-values. [PostgreSQL Doc : jsonb operators ] |
We conducted some tests for measure the performance of JSONB and its indexes as follows. We used pokec dataset ( https://snap.stanford.edu/data/soc-pokec.html ), which is made from the SNS in Slovakia and published by Stanford University. It has 1.6 million user profiles. We built indexes on the hobbies attribute of profiles.
| CREATE INDEX idx_btree_hobbies ON jsonTbl USING BTREE ((jsb->>‘hobbies’)); CREATE INDEX idx_btree_hobbies ON jsonTbl USING HASH ((jsb->>‘hobbies’)); SELECT count(*) FROM jsonTbl WHERE jsb->>’hobbies’ = ‘snowboarding’; SELECT count(*) FROM jsonTbl WHERE jsb->>’hobbies’ = ‘snowboarding’ OR jsb->>’hobbies’ = ‘varenie’;
CREATE INDEX idx_btree_hobbies ON jsonTbl USING GIN ((jsb->‘hobbies’)); SELECT count(*) FROM jsonTbl WHERE jsb->’hobbies’ ? ‘snowboarding’; SELECT count(*) FROM jsonTbl WHERE jsb->’hobbies’ ? ‘snowboarding’ OR jsb->’hobbies’ ? ‘varenie’;
CREATE INDEX idx_btree_hobbies ON jsonTbl USING GIN (jsb jsonb_ops); CREATE INDEX idx_btree_hobbies ON jsonTbl USING GIN (jsb jsonb_path_ops); SELECT count(*) FROM jsonTbl WHERE jsb @> ‘{“hobbies” :“snowboarding”}; SELECT count(*) FROM jsonTbl WHERE jsb @> ‘{“hobbies”:”snowboarding”}’ OR jsb @> ‘{“hobbies”:”varenie”}’; |
(Unit : ms)
| One key | Every Key | reference | ||||
| Btree | Hash | GIN | GiN jsonb_ops | GiN jsonb_path _ops | ||
| Index building time | 28,420 | 39,449 | 10,826 | 417,689 | 126,889 | |
| Index size | 120 | 72 | 63 | 941 | 494 | |
| Insert | 33,046 | 25,855 | 28,222 | 189,765 | 86,339 | |
| update | 28,857 | 43,230 | 30,929 | 203,395 | 92,230 | |
| delete | 31,153 | 31,337 | 27,751 | 26,770 | 30,127 | |
| select 1 | 1 | 1 | 1 | 42 | 1 | 22 rows |
| select 2 | 19 | 27 | 45 | 82 | 51 | 5262 rows |
According to the result above, BTREE performed the best in search for single attribute, Gin index outstands in building time and the size of the index. Jsonb_path_ops performed better in searching every single attribute than jsonb_ops. Although jsonb_path_ops is index for all attributes, it performed well compare to the single key indexes, also index size and building time of jsonb_path_ops were not good enough as it has more than 40 attributes.
Conclusions
BTREE index performed the best in indexing search for single attribute. HASH index also performed well, nonetheless not providing WAL (Write Ahead Log) is critical to the abnormal termination. GIN index benefits in indexing all attribute with a single index, however it performances poorer in indexing a single key than BTREE and HASH index.
As a result, in the choice of index for jsonb type, if the index searches a few predetermined attributes, BTREE is the best choice, however if the index searches arbitrary attributes prefers GIN index. In the case of using GIN index, use jsonb_path_ops as a class checking whether it simply has a key-value, but in order to check not only it has a key-value also existence of the key, better to use jsonb_ops.
Tags: BTREE , GIN , Index , Internals , JSON , JSONB , PostgreSQL
Common questions
Should I use json or jsonb for a new column?
Use jsonb for almost all cases. It is faster to query because it is already parsed, and it is what PostgreSQL's indexing operators and operator classes are built around. Use json only when exact preservation of input text, key order, or duplicate keys matters.
Does a GIN index on a jsonb column speed up every query against it?
No. A GIN index only accelerates the operators its operator class supports: containment, existence, and jsonpath operators with jsonb_ops, or just containment and jsonpath with jsonb_path_ops. A query that extracts a key with ->> and compares it to a scalar needs a separate B-tree expression index.
What is the difference between jsonb_ops and jsonb_path_ops?
jsonb_ops, the default, indexes every key and value separately and supports existence operators (?, ?|, ?&) along with containment. jsonb_path_ops indexes hashed paths instead, supports only containment and jsonpath operators, and produces a smaller, more selective index.
Can I index a single key inside a jsonb column?
Yes, with a B-tree expression index such as CREATE INDEX ON t ((data->>'key')). It supports equality and range comparisons on that extracted value and can be declared unique, which a GIN index on the whole column cannot do.
Does jsonb preserve the order of keys in an object?
No. jsonb reorders object keys internally and keeps only the last value when a key is duplicated in the input. If exact textual order or duplicate keys must be preserved, use the json type instead.
Sources
About this page
Last reviewed October 30, 2026 by the Nine Bits editors. Read our editorial policy or report an error.