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

Thanks! I have completed the transfer by taking down the database and
working exclusively on it, but I still wonder how one could do it in
production without exclusive locks. The loop with small DELETE...INSERT
transactions I posted on the parent post bloats the table fast.

The link you posted contains very useful info, I was not expecting that
the constraints could blow up the memory like that. Comments from me:

Disabling and then re-enabling the foreign key constraints is easily done
with ALTER TABLE.

Unfortunately it doesn't seem to be the same case for indices. One has to
create the table without indices, and then create the indices separately.
With such a process there is a risk of ending up with non-identical
table schemas.

By the way, with COPY one must use an intermediate file, right? There is
no way to COPY from table to table directly?


Thanks,
Dimitris

On Thu, 6 Jul 2023, Lorusso Domenico wrote:

> 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.]
>
>