How To Dev

Introduction View Also known as Standard View or Virtual Table , this is essentially just an SQL query statement saved under a specific name. It does not store any data on the disk, it only stores the definition (SQL code block) within the system. Every time you execute SELECT * FROM my_view , Postgres runs the underlying SQL definition of that View directly against the base tables. The data is always up to date, if the source tables change, the View immediately reflects those modifications. Performance depends entirely on the original SQL query, if the original query is complex involving multiple table JOIN operations or AGGREGATE calculations, the View will run slowly because Postgres must recompute everything from scratch upon every invocation. Materialized View This is a View but it physically stores data on the disk just like a regular table. It saves the entire result of the query onto the disk at the moment it is created or refreshed. When you execute SELECT * FROM my_mat_vie...

Introduction The EXISTS clause is used to solve a single problem in data logic which is checking the existence of data based on a condition, without worrying about how many times it exists or what the specific value is. How it works Just like when using IN with EXISTS , Postgres will also automatically use Semi-Join algorithms (or Anti-Semi-Join for NOT EXISTS ) Semi Join (EXISTS) : Returns rows from the main table (left table) if there is at least one matching row in the sub-table. The optimizer stops searching as soon as it finds the first matching row for each record of the main table. Anti Join (NOT EXISTS) : Contrary to Semi Join , it returns rows from the main table if no matching rows are found in the sub-table. It still uses popular algorithms such as Nested Loop Semi Join, Hash Semi Join, Merge Semi Join with a Short-circuit mechanism, meaning that for each row value, it only needs to iterate until the first value is found, rather than finding all matching values like wh...

Introduction In this article, we will learn about Merge Join. How It Works Simply put, how Merge Join works is like sorting two lists in ascending order first, then placing them side by side for comparison. Assuming we need to compare the id column of two tables, A and B, the specific process is as follows: Step 1: Sort Phase PostgreSQL must ensure that both lists are already sorted by the order of the join column (for example, from smallest to largest). If the two tables are already sorted, such as when the join column is a Primary Key or has an Index, Postgres will skip this step and jump to step 2. Step 2: Merge Phase PostgreSQL will place two pointers starting from the first row of the two tables and begin comparing: If the results on both sides are equal: Return the result, then move the pointer in table B to the next row to continue checking (since data in a column can be duplicated). If the id in table A is smaller: Move the table A pointer to the next row. If the id in table A ...

Introduction In this article, we will learn about Hash Join, which is the preferred algorithm when joining two large tables without indexes, or when the data volume exceeds the optimization capability of a Nested Loop. How It Works Step 1: Build Phase First, PostgreSQL selects the smaller table to load into memory (RAM). Next, it takes each item and passes it into a Hash Function to receive a hash number. Postgres relies on this value to sort the results into the corresponding bucket, resulting in the creation of a Hash Table stored in RAM. Step 2: Probe Phase Now, PostgreSQL scans the larger table, also taking each item to pass into the Hash Function just like in step 1. Once the hash number is obtained, it only needs to access the corresponding bucket in the Hash Table to check. Hash Collision When using a Hash Function, there are still cases where two different values produce the same result, which is called a Hash Collision. In this case, values with the same Hash Number will resid...