Postgresql – COPY Command spawning multiple processes

copypostgresqlredhat

I have a lengthy data pipeline that imports a CSV, creates a sequence of dependent views and then exports the results using COPY as a CSV. This process had been working fine, completing in around 30-60 seconds. Suddenly, this process has started running much slower when it reaches the copy command. Running SELECT usename, state, query FROM pg_stat_activity; shows three identical, active COPY commands under my username. The process does eventually complete but now takes up to 20 minutes or more. Other than changing some initial sub-setting, the data has remained unchanged. No one else is using this database but there are other users on the cluster.

Has anyone encountered this behavior before? Does anyone know what might cause a sudden slow down in a COPY operation?

Postgres 11.6, RHEL 7

Best Answer

You aren't giving us much to go on here. Like, what is the query? What do the other columns of pg_stat_activity show? What does OS command "top" show?

I was thinking parallel workers, but parallel worker processes won't show up with their own row in pg_stat_activity until v13. So I think the most likely thing to have changed is your workflow is now launching multiple simultaneous COPY commands for unknown reason. Could you crank up logging in the database (log_statement='all', log_connections='on')?

Related Solutions

PostgreSQL 9.2 – How to Automate COPY Command

The scripting answers above will obviously work.

But, assuming you have to, have to, have it executed from within the DB (let us know why :) ), the code below (minus the loop, etc.) should get you on your way.

You were very close btw.

CREATE or REPLACE FUNCTION function_1(rows text)
RETURNS void AS $$
begin
  execute 'COPY (
    SELECT * 
      FROM backup_table
     WHERE id = ' || rows || ' 
       AND status = 1
  ) TO ''/var/lib/pgsql/'||rows||'.dat''';
end
$$ language 'plpgsql';

EDIT 1: function loop example and formatting

CREATE or REPLACE FUNCTION function_loop(rows_arr text[])
RETURNS void AS $$
declare
  crtRow text;
begin
  ForEach crtRow in array rows_arr
  LOOP
     perform function_1(crtRow);  
  end loop;
end
$$ language 'plpgsql';

Executed as

select function_loop('{1,2,3}');

Please note there are no checks being performed here.

E.g. This will create a zero byte 3.dat even if there is no id=3 in the table

EDIT 2: A more extensible option for thousands of id would be

CREATE or REPLACE FUNCTION function_loop1(startId int, endId int)
RETURNS void AS $$
declare
  allIds text[];
  crtRow text;
begin
  select array_agg(id::text) into allIds 
    from backup_table
   where status=1 
     and id between startId and endId;
  ForEach crtRow in array allIds
  LOOP
    perform function_1(crtRow);  
  end loop;
end
$$ language 'plpgsql';

PostgreSQL COPY Command – Appending Data

I think you cannot (easily) do that (at least on your PostgreSQL version, see Daniel's answer). What you definitely can, however, is changing your approach, and do a UNION ALL from all those tables in the query part of your COPY. This means there is no looping, but you have to construct your query from the collected table names.

The result would look like

COPY (SELECT * FROM table1
      UNION ALL
      SELECT * FROM table2
      ...) 
    TO tmp/result.txt;

Notes:

there is no such thing as a psql function. psql is a client to PostgreSQL. It has built in commands (basically everything starting with \), however.
it is not so clear what you mean by 'archive logs'. If it is the write-ahead log (WAL), then with the above approach you don't have to worry about it. Otherwise, you can use unlogged tables.
if the tables are big, there is a chance your system will write (a lot of) temp files. This might make the execution not so fast.

Best Answer

Related Solutions

PostgreSQL 9.2 – How to Automate COPY Command

PostgreSQL COPY Command – Appending Data

Related Question