Thread: 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.
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
Re: Does the delete+insert is fater and less cpu consumer than update?
From
"David G. Johnston"
Date:
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 https://www.postgresql.org/message-id/AM4PR03MB171327323DCD2069A532756190850@AM4PR03MB1713.eurprd03.prod.outlook.com Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html