0

I know COPY is the best utility to export the data from a table or a query to output files. But if we are doing an export like

\COPY (select * from table) to '/tmp/file.csv' WITH CSV;

How its different from running the select query and save the results later(like in a GUI tool) or psql -c'select * from table'> output.csv

Any advantage of using COPY in terms of performance and caching?

The overall idea is, I'm trying to understand the performance benefits of exporting the data via read from select vs COPY from python psycopg

0

Your example which redirects the output of psql is not going to produce the same format, so that will be one obvious difference. A less obvious one may be that it reads the entire data set into memory before iterating over it to export, so is slower and more likely to crash with memory problems.

2
  • " A less obvious one may be that it reads the entire data set into memory before iterating over it to export," - Is this for COPY process or PSQL?
    – TheDataGuy
    Nov 30 '20 at 11:48
  • That is for psql with plain SELECT. The \copy form streams its output.
    – jjanes
    Nov 30 '20 at 12:06
0

With COPY, the server formats the data, and there is no formatting effort on the client.

You should test it – that should be simple to benchmark.

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.