How To Dev

Introduction As mentioned in the previous article, we explored Autovacuum and Autoanalyze , but they have limitations in that they do not automatically run after changing just a few rows of data, requiring a specific threshold to be met. Causes Because operations to check for Dead Tuples and update statistics data require significant processing costs, handling it this way helps Postgres save CPU and Disk I/O . If data changes have not reached the threshold, meaning the volume of changes is insignificant compared to the entire table, the Query Planner can still provide an effective solution. Why Manual VACUUM Is Needed When operating in production, many factors can prevent Autovacuum from working effectively, including the following causes: Long-Running Transactions This is the most common cause because the MVCC mechanism of Postgres operates on the principle that Vacuum cannot reclaim any Dead Tuple if it remains visible to an active Transaction. Specific scenario: If you start...

Introduction Continuing from the previous article, here are other Query Tuning techniques based on common errors encountered when querying data and the process you can apply: Optimizing JOIN Statements Ensure columns used for JOIN have Indexes (usually Foreign Key connecting to Primary Key ). If Postgres uses a Nested Loop Join with a matching Index, it speeds up finding rows needed for comparison during JOIN If using a Merge Join , it skips the step of sorting two lists before joining Try filtering data using WHERE beforehand or directly inside the ON condition of the JOIN to reduce the row count matching between tables. The result of using JOIN is multiplication: A Join B = A x B Therefore, if conditions can minimize lists A and B, the generated dataset will be as small as possible Joining Too Many Tables By default, if joining more than 12 tables , Postgres cannot analyze and find the optimal JOIN execution plan because the number of combinations to process is too large At this ...