Обсуждение: \COPY command and indexes in tables

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, ✉ guru@unixarea.de, 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)

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

Hi Jayadevan,

> On 19. Nov, 2020, at 11:07, Jayadevan M <maymala.jayadevan@gmail.com> 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