Re: \COPY command and indexes in tables

[Paul Förster]

Hi Jayadevan,

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

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

Cheers,
Paul

Re: \COPY command and indexes in tables

[Jayadevan M]

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

Regards,
Jayadevan

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

\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, ✉ g...@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)