How To Dev

Introduction When joining multiple tables together (such as 3 tables or more), PostgreSQL never joins all tables at the same time, but at any single point in time, it can only join 2 datasets. Suppose you need to join multiple tables, the process of bridging steps will be performed as follows First, Postgres will select 2 tables to join together to create an Intermediate Result Postgres will treat the Intermediate Result as a completely new table and will use it to join with the next table to create the next Intermediate Result This process repeats over and over until all tables have been joined. Algorithms used When joining multiple tables, Postgres still uses algorithms like Nested Loop Join, Hash Join and Merge Join But corresponding to each dataset and the index of the tables, Postgres will choose different algorithms when joining rather than fixing a single algorithm For example, when joining 4 tables A, B, C and D together Suppose table A is small with about 100 rows joining with...

Introduction In SQL and specifically in PostgreSQL, there are 4 types of Joins as follows: INNER JOIN: Only retrieves records that have a match in both tables. LEFT JOIN: Retrieves all records from the left table, and if there is no match in the right table, the values are set to NULL. RIGHT JOIN: The opposite of LEFT JOIN, rarely used because it can be rewritten in reverse using LEFT JOIN. FULL OUTER JOIN: Retrieves all records from both tables, filling with NULL where there is no match. Nested Loop Join When executing a join, the Postgres Optimizer automatically performs an analysis based on the datasets of the 2 tables to select the most efficient and suitable algorithm for the current situation First, let us look at the Nested Loop Join. This is the most basic join algorithm, and its operational mechanism is very straightforward: it takes each item from one table to compare it with every item from the other table, acting exactly like two nested for loops in programming Use cases Th...

Introduction In this article, we will explore another type of index, which is the Hash index. True to its name, when using this index type, Postgres passes the value to be indexed through a hash function to generate a number ranging from 0 to 4,294,697,295 with a uint32 (4 bytes) type. Index Creation Process As soon as you create a Hash index, Postgres pre-allocates Pages to store the indexes. If the table does not have data yet, Postgres will still create: 1 Metapage : Always located at the first position (Block 0). This page stores the configuration of the index. 1 Bitmap page : Used to manage which pages in the file are currently empty to avoid Overflow pages. 10 default Primary Bucket Pages . Thus, right from the start, your newly created Hash Index file will have a size of 12 Pages x 8KB/Page = 96 KB. If the table already contains data, based on the configuration information about the Ram limit that Postgres can use, it will create the maximum number of Pages (that it can create)...

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