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 Table Partitioning is a feature that physically divides a large table into smaller tables (called partitions), but logically, your application still sees it as a single table. When creating a table partitioning (parent table), that table is actually just a virtual table containing metadata, it has no corresponding heap file on the hard disk and its storage size is zero. On the other hand, each partition (child table) created is an independent physical table. PostgreSQL will allocate a separate heap file for each child table. Advantages Query Performance: When querying data, PostgreSQL will activate the Partition Pruning mechanism to skip irrelevant partitions and only scan the necessary partition, reading only the exact heap file of that partition on the disk. Data Lifecycle Management: Data retention is extremely efficient because you do not have to operate on a single massive table, which would cause table locks and generate many WAL files that slow down the system For i...

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