Re: Moving data from huge table slow, min() query on indexed column taking 38s - Mailing list pgsql-general

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