How To Dev

Introduction SP-GiST (Space-Partitioned Generalized Search Tree) operates based on the core idea of space partitioning, applied to complex data such as geographical coordinates (longitude, latitude), long text strings or hierarchical structures (directories). Index Creation SP-GiST Index has key characteristics including: Non-overlapping space partitioning: When it divides space (or the dataset) into smaller parts, these parts do not overlap. A data point can only belong to branch A or branch B, not both. Unbalanced Trees: Unlike B-Tree which is always balanced between branches, SP-GiST allows branches to grow freely. Whichever branch has more complex data will be more detailed, while branches with less data stop early. This helps save memory effectively. For example, when there is a search keyword list consisting of: sam , sample , samsung , samurai and apple . If using B-Tree Index, it will store these exact words If using GIN Index, it will break down each word before storing If us...

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