Re: Moving data from huge table slow, min() query on indexed column taking 38s

Hello,
this is a standard problem during bulk copy.

here <https://www.postgresql.org/docs/15/populate.html> 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(at)gmx(dot)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.]