How To Dev

Introduction BRIN (Block Range Index) works by grouping a cluster of pages in the HEAP together into a single range rather than creating an index for every single data row like a B-Tree. The structure of a data row inside a BRIN Index (called an Index Tuple) consists of the following three core components: blknum (Block Number): The block number starting the range (for example: 0, 128, 256, 512...). min_value: The minimum value of that column within a block range. max_value: The maximum value of that column within a block range. How It Works When executing a query with a BRIN Index, Postgres scans the index and checks whether the value being searched for falls within any [Min, Max] range If not, it skips that entire block range, eliminating a huge amount of redundant data. If yes, Postgres relies on the blknum to load the corresponding Pages in the HEAP up and checks them sequentially to find the exact data row. Advantages Super compact: Because it only stores the Min and Max for an en...

Introduction GiST (Generalized Search Tree) is also a high-order balanced tree, which has a hierarchical structure similar to the traditional B-Tree structure. However, the core difference lies in the storage content inside nodes including Root Node and Internal Nodes: each entry in a branch node contains two pieces of information: Predicate: This is the data area created by Postgres from a general level (at the root node) to a detailed level (at the internal node) Nodes at higher levels will contain a general description for all child nodes below them With such a structure, it helps to eliminate extremely quickly data areas that definitely do not satisfy the condition, instead of having to check each row one by one. TID points to lower-level child nodes. Leaf Nodes contain information Specific actual data of the field value TID points to the row data in the Heap Data type GiST supports well for handling data types without linear order (cannot be sorted from smallest to largest like re...

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...