1

I have a table company like,

id  |  name  | age | address   | salary
----+--------+-----+-----------+--------
 1  | Ganesh |  32 | Chennai   |  20000
 2  | Allen  |  25 | Chennai   |  15000
 3  | Teddy  |  23 | Chennai   |  20000
 4  | Mark   |  25 | Chennai   |  65000
 5  | Paul   |  32 | Chennai   |  20000
 6  | Allen  |  25 | Chennai   |  15000
 7  | Teddy  |  23 | Chennai   |  20000
 8  | Paul   |  32 | Chennai   |  20000
 9  | Allen  |  25 | Chennai   |  15000
 10 | Teddy  |  23 | Chennai   |  20000

Now, what will happen in the background, when I update any one (or more) record(s) in this table?

update company set address = 'California' where id = 5;

My real doubt is, whether it will delete the whole old record and insert a record newly with given value or it just replace the given value in the specified column?

If the record is deleted for update, then the memory remain there?

1 Answer 1

2

In general, a new row version will be created by an update to support MVCC. This might not happen under certain conditions e.g. if the update does not change any indexed column.

The space occupied by the old row version will be freed by the auto-vacuum process and re-used for new data once there are no more transactions that would see that version.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.