How To Dev

Introduction In this article, we will continue exploring GIN Index with the JsonB data type. JsonB, short for JSON Binary, is a data type developed from the JSON data type and supported by PostgreSQL since version 9.2. The key difference between JSON and JsonB lies in how they are stored. JsonB supports binary storage and resolves the limitations of the JSON data type by optimizing the insert process and supporting indexing. Creating an Index When creating an index for a JsonB column, Postgres supports two strategies as follows: jsonb_ops : This is the default strategy that flattens the JSON to extract independent paths, keys and values to create Entries. For example, if you have a JSON like this: { "shop" : "ShopA" , "products" : [ { "product_name" : "mouse" , "price" : 50 } , { "product_name" : "keyboard" , "price" : 100 } ] } It will create a GIN Index as follows, n...

Introduction GIN GIN (Generalized Inverted Index) also uses a B-tree data structure, so it shares similar characteristics with B-Tree. However, unlike B-tree, which stores each value along with a Tid to a single row in the main table, GIN stores the value along with information to access multiple rows sharing that same value (referred to as a Posting List). Therefore, the difference in usage is that B-tree is used to search for a row containing a specific value, while GIN is used to search for small values nested inside a row. Posting List You can understand it as a list containing IDs, which are primary keys corresponding to each row in the main table. If the table does not have an ID, it will use a physical identifier called ctid (Column Tuple Identifier) created by Postgres, with a structure consisting of (Page number, row index within the Page). Based on this ctid, Postgres can access any row in the main table. However, as you know, data in the heap is arranged arbitrarily and when...