\COPY command and indexes in tables

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

\COPY command and indexes in tables

Matthias Apitz

Hello,

We load large tables in some customer installation (some millions of rows)
from file with:

    TRUNCATE TABLE tableName ;
    \COPY tableName FROM 'fileName' WITH ( DELIMITER '|' )

and got to know that the loading nearly stops (without any real CPU
consumption) in the middle. The wild guess is that we forgot to DROP the indexes on
the tables. The doc https://www.postgresql.org/docs/13/sql-copy.html
does not give any hints related indexes.

There seems to be even tools available which address this issue on the
flight, like https://www.californiacivicdata.org/2018/01/25/index-drop-and-copy/

Any comments on this?

        matthias

--
Matthias Apitz, ✉ [hidden email], http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Без книги нет знания, без знания нет коммунизма (Влaдимир Ильич Ленин)
Without books no knowledge - without knowledge no communism (Vladimir Ilyich Lenin)
Sin libros no hay saber - sin saber no hay comunismo. (Vladimir Ilich Lenin)


Reply | Threaded
Open this post in threaded view
|

Re: \COPY command and indexes in tables

Laurenz Albe
On Thu, 2020-11-19 at 08:38 +0100, Matthias Apitz wrote:
> We load large tables in some customer installation (some millions of rows)
> from file with:
>
>     TRUNCATE TABLE tableName ;
>     \COPY tableName FROM 'fileName' WITH ( DELIMITER '|' )
>
> and got to know that the loading nearly stops (without any real CPU
> consumption) in the middle. The wild guess is that we forgot to DROP the indexes on
> the tables.

If it does not consume CPU, it must be stalled somehow.

Are there any wait events in "pg_stat_activity".

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com



Reply | Threaded
Open this post in threaded view
|

Re: \COPY command and indexes in tables

Jayadevan
In reply to this post by Matthias Apitz




We load large tables in some customer installation (some millions of rows)
from file with:

    TRUNCATE TABLE tableName ;
    \COPY tableName FROM 'fileName' WITH ( DELIMITER '|' )


May be you could also make sure that loading actually stopped, by checking the size of the data directory. In another session, you could try 
watch du -h  -s <datadirectory>

Regards,
Jayadevan
Reply | Threaded
Open this post in threaded view
|

Re: \COPY command and indexes in tables

Paul Förster
Hi Jayadevan,

> On 19. Nov, 2020, at 11:07, Jayadevan M <[hidden email]> wrote:
>
> May be you could also make sure that loading actually stopped, by checking the size of the data directory. In another session, you could try
> watch du -h  -s <datadirectory>

that might be misleading if you have the pg_wal directory inside PGDATA.

Cheers,
Paul