I always make sure to update only those columns they’ve changed if I know in advance. This is to minimize my data getting polluted with bad data should a bug creep in application logic.
That said, there are often times it’s difficult to known in advance. For instance user profile update which is a web form with like 10 fields all of which can be edited. Even if you can figure out exact fields that have changed to write that bespoke query will be intractable as it will lead to combinatorial explosion of update queries.
> This is to minimize my data getting polluted with bad data should a bug creep in application logic.
Yeah makes sense, seems like begging for a race condition. In Postgres it also generates vacuum churn.
So if you do know that I updated a few fields how does that work? Do you run a few single-field updates in a single transaction or do you generate dynamic SQL?
In the case I described I just update all the fields. Anything else is just way too tedious and big prone. I hear that ORMs come in handy in such case but then they have their own set of bigger problems as others have pointed out here.
The good part though is such cases to update all the fields are very few. In 90-95% updates I exactly know which fields are getting updates and for what reasons.
That said, there are often times it’s difficult to known in advance. For instance user profile update which is a web form with like 10 fields all of which can be edited. Even if you can figure out exact fields that have changed to write that bespoke query will be intractable as it will lead to combinatorial explosion of update queries.