Does Postgres Update / SET write to disk if the new value is the same as the one stored in the database?


#1

Does actual writing to disk happen on a scenario similar to this:
’’‘
Insert into … values …
On conflict (id)
Do
Set field1 = 10
’’'
If current stored value for field1 in DB is 10? Any idea? Thank you.


#2

Is this an ecto Query? SQL? Anything else?


#3
Raw SQL. Like this:

UPDATE users
SET first_name = 'Michael'
Where id = 7

Would actual writing to disk happen if first_name on row id 7 has already the value Michael?


#4

Depends on many things… Is your database actually discbased or in memory? Does your used database do optimisations of such kind or not?

You’ll have to consult the manual of your database engine and configuration.

edit

It might be more expensive to check and compare old values than to simply write, but this does depend on the complexity of the data, it might even depend on the caching state of that entry. Safest thing to do is to assume a write.


#5

Yes, it would flush to disk after commit based on normal postgresql settings


#6

Yep if you do not want update to happen you can add where id = 7 and first_name != ‘Michael’


#7

Here’s a possible solution (to avoid unnecessary writes to disk), any suggestions to improve this are welcome:

INSERT INTO orders (id, status, inserted_at, updated_at)
VALUES (4876, 0, NOW(), NOW())
ON CONFLICT (id) DO UPDATE
SET updated_at = NOW(), status = 1
WHERE orders.id = 4876 AND orders.status IS DISTINCT FROM 1;

#8

Do you have a reason to try to avoid the write, have you actually encountered performance problems due to this?


#9

Not performance issue but I will be running this SQL every 10 seconds to update a second database. Honestly, I am avoiding unneeded disk writes to save hard-drive life time expectancy :slight_smile:


#10

Why regularly and not on change?

Why only this valaue? Why not a replication?

Why not a proper backup?


#11

A write every 10 seconds will not have a meaningful impact on disk life expectancy.