Question

Earlier, I asked a question about the update function in the car repository, and needed few clarifications on what might be the best practice.

The update function in our car repository is all about updating the car object for an owner.

Since we don’t know in advance what the owner might want to update, we are first preloading the car object and then update the entire record in the database.

const updatedCar = new Car({
  ...car,
  ...updates,
  id: carId,
})

The SQL query looks like this

 
public async update(tx: Transaction...
`UPDATE cars SET
    name = $(name),
    state = $(state),
    license_plate = $(licensePlate),
    info = $(info)
    WHERE id = $(id) RETURNING *`,
    { ...car },
  )
  ....

The renter side

For the renter, they will only need to either lock or unlock the car.

This being the only property they will need to update.

We wanted to create another function in the repository (maybe called updateCarState) that only updates the state column in the database instead of using the same update function which in this case, would be updating the whole row with the same data except for the changing state.

A Query would look something like this

public async updateCarState(...)
`UPDATE cars SET
 	state = $(state)
 	WHERE id = $(id) RETURNING *`,
{ state },
)   ....

We wanted to do this because a renter will lock and unlock the car very often compared to the user who will do the car update seldomly.

We ended up not implementing the second function (updateCarState) because we said that it’s probably not needed, and we will have extra code to test🙊(not to mean that we are lazy…). ( We even went far to believe that postgres probably optimize these sort of updates 🥲)

While we might surely be wrong about the postgres optimization regarding unchanged update, we are still curious about some points.

• 💡 Does postgres perform that optimization ?
• 💡 Will the creation of that function be beneficial in terms of performance or best practice ?
• 💡 For a small objects (car object in this case) does it matter that much to avoid the recreation of that row when updating?
• [!] What is considered best practice in this case ?
  • ➡️ Creating the extra function that updates the column that we need
  • ➡️ or simply use the update function that is already implemented and tested for both the renter and the owner
• 🔥 When should one start worrying about optimization when it comes to updating records in the database?
• [?] Is there a rule of thumb one should follow when it comes to interacting with our databases ?