Does it matter to delete and create new lines or update old ones?

Asked

Viewed 75 times

2

Not taking into account the technical difficulty of doing one thing or another, but rather the consistency of the bank, the speed of subsequent operations and the professionalism.

If I have a database table (Postgresql), with a PRIMARY KEY auto-incremented and need to update this table, what is the difference between cases?

1 - Do UPDATE lines that already exist and eventually create and delete lines when strictly necessary.

2 - Delete all lines referring to a register (the structure is relational and in this table are stored some lines referring to a single register) and create new lines with the new information.

Note: I can do both ways, that’s not what I want to know. What I want to know is in the first paragraph of the text.

database postgresql sql-update delete
  • 1

    Look, I don’t know in detail how the postgresql works, but in the sql-server, delete and include is faster, however, the LOG file gets full as the deleted lines will end up there, except if using the truncate table, but both solutions require receriar all indices, and this in both banks, this is a point of attention, both performance and processing, especially IO

  • It seems that your artificial key is not useful for anything since, as you say, keep the same value (in the case of UPDATE) or modify it (in the case of TRUNCATE/INSERT) does not bring any consequences to your data.

1 answer

5


Of course it makes a difference, one thing is one thing, another thing is something else.

Know that from the internal point of view Postgresql already creates a new line and creates another whenever it will make one UPDATE, because he works with a technique called MVCC (Multi Version Concurrency Control), which is simple to maintain isolation and consistency in transactions. So the performance is supposed to be essentially the same. Sure, there may be a few small details that can interfere a little, but as are implementation details I wouldn’t count that much.

If everything is done in a transaction, semantically it is to be the same thing if it is in serialized mode. If you are not in transaction or are in another mode you may have a greater chance of improper access to data that is not yet complete if you do the DELETE and INSERT.

If deleting and creating the primary key will be the same or new? This is important because it has a different semantics. If you are to keep the same, what is the advantage to your problem of deleting and creating another line?

If it makes any difference to your problem I would go from UPDATE. But your problem may require you to create a new line and delete it. If you don’t mind, make it simple.

If you’re going to do something different, should you justify the choice, can you justify erasure and creation? If it is very important to decide for one or the other there it is better that the decision is supported by a very thorough study and better analysis of the concrete case.

One thing I see people doing a lot of wrong is erasing or altering a data when the problem asks them not to do it. There are situations that you should never have data loss, so it should only exist INSERT na(s) table(s). I’m just saying this as curiosity, does not seem to be the case (I hope).

Browser other questions tagged database postgresql sql-update delete

You are not signed in. Login or sign up in order to post.