How To Dev

Introduction Covering Index is used to enable Index-Only Scan with Non-key Columns , initialized using the INCLUDE clause PostgreSQL supports three types of indexes for this feature: B-Tree (Most common), GiST and SP-GiST All other index types ( GIN, BRIN, Hash ) do NOT support the INCLUDE clause. How it works When you create an index with INCLUDE as follows: CREATE INDEX idx_orders_user_id ON orders (user_id) INCLUDE (total_amount, status) Main column (user_id): Located at the upper nodes and leaf nodes of the B-Tree. Used for searching, filtering ( WHERE ) and sorting ( ORDER BY ). Payload columns (total_amount, status): ONLY stored at Leaf nodes. They do not participate in tree sorting or searching operations. The main purpose is to help Postgres perform an Index-Only Scan , meaning Postgres retrieves data for total_amount and status directly from the index without reading the Heap Page , making the SELECT query execution extremely fast. Advantages Avoiding placing those colu...

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