CODEWITHSUNDEEP
djangodatabasepostgresql
Alex Szabo
Alex Szabo

Reputation: 3276

What's more efficient? Read and Write If... or always write to db?

I have a database table, that has a column, which is being updated frequently (relatively).

The question is:

Is it more efficient to avoid always writing to the database, by reading the object first (SELECT ... WHERE), and comparing the values, to determine if an update is even necessary

or always just issue an update (UPDATE ... WHERE) without checking what's the current state.

I think that the first approach would be more hassle, as it consists of two DB operations, instead of just one, but we could also avoid an unnecessary write.

I also question if we should even think about this, as our db will most likely not reach the 100k records in this table anytime soon, so even if the update would be more costly, it wouldn't be an issue, but please correct me if I'm wrong.

The database is PostgreSQL 9.6

Upvotes: 0

Views: 106

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246523

It will avoid I/O load on the database if you only perform the updates that are necessary.

You can include the test with the UPDATE, like in

UPDATE mytable
SET mycol = 'avalue'
WHERE id = 42
  AND mycol <> 'avalue';

The only downside is that triggers will not be called unless the value really changes.

Upvotes: 1

Related Questions