Does the delete+insert is fater and less cpu consumer than update?
Hi,
I have a table named 'establishment' which has a join with a table
'category' and a table 'product'. I added indexes to foreign keys to
enhance the execution time of the update or delete of data.
I have to update the table establishment when I get a new data received
from an external api. The amount of updated data is about 3000-5000 rows.
I used an update query to update the modified rows, however when I search
on the net I found that some people said that update is cpu intensive
consumer and delete+insert is faster.
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.
hmidi slim <hmidi.slim2@gmail.com> writes:
I used an update query to update the modified rows, however when I search
on the net I found that some people said that update is cpu intensive
consumer and delete+insert is faster.
It's possible that there are RDBMSes somewhere for which that is true.
But as far as Postgres is concerned, it's nonsense.
regards, tom lane
On Thu, Dec 14, 2017 at 3:08 PM, hmidi slim <hmidi.slim2@gmail.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.
If you use partitioned tables,
and just want to reload data from some partitions
you can then use the truncate partition syntax
or develop a procedure like described here
/messages/by-id/AM4PR03MB171327323DCD2069A532756190850@AM4PR03MB1713.eurprd03.prod.outlook.com
Regards
PAscal
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html