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