3

I'm trying to dump a table with 50M records to a file, and my goal is to reduce the time in which this action is performed. I usually use the COPY metrics TO 'metrics.csv' DELIMITER ',' CSV; This could take like an hour in the best cases. I'm also interested in exporting the data in some plain format(avoid using pd_dump directories).

One of the ideas is to somehow access this table by a condition or cursor that splits the whole table into equal size pieces, so you can perform for example 2 copy queries at the same time reducing the time to the half.

Example:

COPY (SELECT * FROM metrics WHERE id < 25000000) TO 'metrics_1.csv' DELIMITER ',' CSV;
COPY (SELECT * FROM metrics WHERE id >= 25000000) TO 'metrics_2.csv' DELIMITER ',' CSV;

Could partial indexes created on those conditions help?

Any ideas a good way to implement this partial copy dumps of a table? Is there any other solution to dump this table faster?

Postgresql 11 / 100GB RAM / 20 cores.

After some parallelization with COPY IO bounds doesn't seem to be the bottleneck.

enter image description here

  • What about increasing max_parallel_workers_per_gather (and maybe max_parallel_workers) to let Postgres do the parallel processing? – a_horse_with_no_name Mar 22 at 9:13
  • Right know it has max_parallel_workers_per_gather = 8 and max_parallel_workers = 16 Im going to increase them and paste the results, thank you @a_horse_with_no_name ! – Imanol Y. Mar 22 at 9:41
  • 2
    I would first look into why it is so slow to start with. I can COPY out 56 million rows in under 3 minutes, but they are skinny rows and occupy only 2.7GB once dumped. Can you share the size of the exported file, and the table definition? – jjanes Mar 22 at 12:49
  • 2
    @a_horse_with_no_name There is no native parallelization for COPY of a table. For COPY of a query, it would be parallel only if the query itself was, and that is unlikely for select with no joins and no functions. – jjanes Mar 22 at 12:57
  • 1
    @jjanes: I am aware that the writing of the data isn't parallelized, but the query itself could maybe profit from running in parallel (assuming that writing the flat isn't the bottleneck, but reading the data) – a_horse_with_no_name Mar 22 at 13:03
2

Your current idea of using a range query on the primary key is probably the best bet. I don't see how partial indexes could help with that. You would need a series of partial indexes which would in aggregate be a total index, and with BTREE indexes used for range queries that would be pointless. But, the success of this method is likely to depend on the rows of your table being physically approximately ordered by the primary key value. You can enforce this ordering by using the CLUSTER the command, but that is itself a very expensive operation to do.

If you want to get PostgreSQL to parallelize this for you, you would have use a dummy query which selects everything:

COPY (SELECT * FROM metrics) TO 'metrics.csv' DELIMITER ',' CSV;

and you will probably also need to dramatically lower the "parallel_tuple_cost setting", perhaps all the way to zero. But this is not likely to give you an actual improvement, because the bottleneck (if it is not disk IO) will be in converting your data from its internal binary format to the text format that is going to be output by the COPY. This conversion is always done in the leader process, not the parallel workers.

You can kind of get around this, by writing your queries so the heavy lifting is explicitly done in the query itself:

COPY (SELECT col1::text, col2::text, col3::text, ... FROM metrics) TO 'metrics.csv' DELIMITER ',' CSV;

Now the lead process still needs to scan all that text for things which need to be escaped/quoted, but at least it doesn't need to convert JSONB to text for all the rows by itself. In my hands, using 7 parallel workers (8 processes including the leader) cuts the time required to COPY...TO in half. The overhead then becomes the communication from the parallel workers to the lead process. The way to remove that is to go back your original suggestions of indexed range queries in separate sessions.

  • Im working still with this idea, it seems to be a good one. I will update my question when i have get this approach as far as i can. thanks! – Imanol Y. Mar 23 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.