Re: Does the delete+insert is fater and less cpu consumer than update?

On Thu, Dec 14, 2017 at 3:08 PM, hmidi slim <hmidi(dot)slim2(at)gmail(dot)com> wrote:

> I didn't make any test for the performance between them and I want first
> of all to know if the update consumes more cpu in case of a large amount of
> data and with table with join.
>

Clearing the entire table, via truncate (not delete), and inserting all new
records is going to be the best solution you can get. The problem is most
use cases involve joins to other tables and dropping an entire table is
simply not possible. If you can go this route it is also worth considering
whether you make said table UNLOGGED. The vast majority of my ETL purposed
tables are routinely truncated and defined unlogged. But they are also
usually not involved in views or production queries.

The best thing to do is only update those records that have changed, delete
those that no longer exist and insert new ones. For records that have
changed there are advantages to only updating non-index-used columns (I
believe the system detects actual changes, so the index-used can still
appear in the SET clause of the update). If an indexed column changes then
there will be no material difference between delete+insert and update *since
an update is, in its general implementation, a delete+insert anyway.*

You speak about CPU consumption but database admins/users are usually more
concerned with IO/Disk than CPU - especially when performing writes.

David J.