Hello,
this is a standard problem during bulk copy.
here some suggestions; for example disable indexes.
The main issue is related to index, lock escalation and log writing.
In other dbms you should set log off on the table, but postgresql does not seem to have this feature.
Anyway, using an explicit lock table exclusive should prevent lock escalation.
So: disable indexes in target table
lock exclusive both table
insert data
truncate old table
If this doesn't work you can consider using the copy command.
Il giorno gio 6 lug 2023 alle ore 18:12 Dimitrios Apostolou <
jimis@gmx.net> ha scritto:
On Thu, 6 Jul 2023, Dimitrios Apostolou wrote:
> + First I chose the method to DELETE...INSERT everything.
Just to clarify, the query looked more like
WITH rows AS ( DELETE FROM tbl_legacy RETURNING * )
INSERT INTO tbl
SELECT * FROM rows;
> I noticed that the postgres process was growing without bounds, up to
> the point that it consumed almost all of swap space and I had to kill
> the command. Any idea why that happens?
Also note that my settings for work_mem, temp_buffers, shared_buffers etc
are all well below the RAM size and postgres has never shown unbound
growth again. Postgres version is 15.2.
Dimitris
-- Domenico L.
per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]