Using COPY in Postgres

Question

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

Answer 1

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.

Answer 2

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.