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 How to handle data changes Postgres uses the MVCC mechanism, meaning when data changes occur, Postgres still retains old data versions and INSERT s new data DELETE : when deleting rows, Postgres only adds a flag to that row called Dead Tuple indicating that this row is no longer used This means that row still exists on disk and is not completely deleted, so it will need cleaning up When querying, it will be automatically ignored and will not return results INSERT : when adding a row Postgres will search for empty space in existing pages to INSERT new rows Or create a new page, if empty pages are exhausted Or override data of Dead Tuple rows according to the Free Space Map UPDATE : when an UPDATE is needed, it will DELETE the old row and INSERT a row with new data TRUNCATE is an operation belonging to the DDL (Data Definition Language) group, not DML like DELETE . Instead of marking Dead Tuple for each row, Postgres will completely delete the table's data fi...