ON CONFLICT DO UPDATE Is Rewriting Rows You Never Changed

In a detailed exploration of the inefficiencies associated with PostgreSQL's `INSERT ... ON CONFLICT DO UPDATE` statement, particularly when rows are rewritten unnecessarily, Mehdi Abaakouk highlights the costly impact of such operations on high-volume tables. The issue arises when entire rows are rewritten even if no actual data change occurs, resulting in increased write amplification, tuple churn, and replication traffic, particularly on static tables. To address this, Abaakouk suggests implementing a simple SQL fix using a `WHERE` clause to prevent updates when no data has changed, thereby reducing unnecessary overhead. However, the solution must be applied judiciously, as some updates may have side effects or dependencies that necessitate leaving them unsuppressed. The article emphasizes the importance of aligning update strategies with data behavior and identifies areas in the codebase where further optimization can reduce resource consumption and improve database performance.