3 ms·

What is way way faster here? Are you only comparing COPY versus individual row INSERT statements? I prefer using COPY ... STDIN/STDOUT. AFAICT, you get the sa

What is way way faster here? Are you only comparing COPY versus individual row INSERT statements? I prefer using COPY ... STDIN/STDOUT. AFAICT, you get the same bulk table access performance. But, the external file access is via the client, separating DB rights and filesystem rights. When appropriate, you can also SSH to the server and invoke psql as a regular user there, manipulating files under that user's control on the server filesystem. The psql command also provides built-in \COPY command to make it easy to combine several such commands into one transaction in a SQL script. It looks a little like the SQL COPY command, but accesses files on the client side and executes equivalent COPY ... STDIN/STDOUT statements on the server. In Python, you can use the copy_expert() method with psycopg2 connections to access the COPY ... STDIN/STDOUT feature for similar bulk ETL. However, Python will become the performance bottleneck if you try to do any real row-level processing rather than just relaying byte buffers to/from the filesystem.

aasasd 8y ago
MySQL's ‘load data infile’ is much faster than anything going through the client, precisely because it avoids layers of processing. (At least it was way faster back when I was using it.) Dunno about this business in Postgre.
pgaddict 8y ago
You're missing the point. COPY TO/FROM PROGRAM is useful e.g. when you already have the data locally on the server (same filesystem, ...). COPY FROM STDIN/STDOUT are useful when the data are on some other system (say, on a different server, etc). Of course, you might write a script that reads the local data and loads it through a regular client, i.e. something like COPY t FROM PROGRAM 'gunzip -c /path/to/compressed/data.gz'; might be rewritten like gunzip -c /path/to/compressed/data.gz | psql db -c 'copy t from stdin'; but well, that requires some access to the server and ability to execute commands on it (although not as a postgres superuser). Ultimately it's a tradeoff - don't trust your users? Don't give them superuser access, don't grant them the role. It's possible to restrict that in other ways (e.g. security-definer functions + extra validations).
- saltcured 8y ago
  I'm not missing the point. I was responding to a comment about performance. In my experience, COPY ... STDIN/STDOUT are just as fast the other COPY, given the same filesystems. I think many people are unnecessarily conflating bulk COPY advantages with superuser rights here. We use it all the time via psql authenticated with unix domain socket. In most situations where there are users performing ETL and such, I think it is much safer to have them using a non-superuser role and manipulating files in their own user or group space. You can have all the performance of a bulk COPY without the risk of mangling the service-related files under the postgres user. I think it is better to give regular SSH accounts to these users than to give them elevated DB privileges so they can use server-side file or program COPY. Also, with psql you can do \copy ... program within your SQL script. It has all the expressive power of a server-side copy but actually runs commands under the invoking user instead of the postgres service.

aasasd 8y ago

MySQL's ‘load data infile’ is much faster than anything going through the client, precisely because it avoids layers of processing. (At least it was way faster back when I was using it.) Dunno about this business in Postgre.

pgaddict 8y ago

You're missing the point. COPY TO/FROM PROGRAM is useful e.g. when you already have the data locally on the server (same filesystem, ...). COPY FROM STDIN/STDOUT are useful when the data are on some other system (say, on a different server, etc). Of course, you might write a script that reads the local data and loads it through a regular client, i.e. something like COPY t FROM PROGRAM 'gunzip -c /path/to/compressed/data.gz'; might be rewritten like gunzip -c /path/to/compressed/data.gz | psql db -c 'copy t from stdin'; but well, that requires some access to the server and ability to execute commands on it (although not as a postgres superuser). Ultimately it's a tradeoff - don't trust your users? Don't give them superuser access, don't grant them the role. It's possible to restrict that in other ways (e.g. security-definer functions + extra validations).

saltcured 8y ago
I'm not missing the point. I was responding to a comment about performance. In my experience, COPY ... STDIN/STDOUT are just as fast the other COPY, given the same filesystems. I think many people are unnecessarily conflating bulk COPY advantages with superuser rights here. We use it all the time via psql authenticated with unix domain socket. In most situations where there are users performing ETL and such, I think it is much safer to have them using a non-superuser role and manipulating files in their own user or group space. You can have all the performance of a bulk COPY without the risk of mangling the service-related files under the postgres user. I think it is better to give regular SSH accounts to these users than to give them elevated DB privileges so they can use server-side file or program COPY. Also, with psql you can do \copy ... program within your SQL script. It has all the expressive power of a server-side copy but actually runs commands under the invoking user instead of the postgres service.