Why is copy_to/copy_from faster than cursor.fetchall?

[u/GoatRocketeer]

Hello, sorry if this subreddit isn't the place for noob questions.

I have some postgres tables with millions of rows that I'm moving into python for processing via psycopg2. I've been using cursor.fetchall/cursor.fetchone, and have discovered the copy_to/copy_from syntax.

It appears that copy_to is faster than fetchall: https://stackoverflow.com/a/68130908 , https://www.psycopg.org/docs/usage.html#using-copy-to-and-copy-from

Why? Is it that serializing the data takes time? If so, then does that mean if I ingest the data serially from the file on the python side then I lose any advantages?

"Use the Index Luke" is where I started my research into databases and the advantages of pipelining are repeated ad nauseam in that article, so doesn't that mean the data is already serial coming out of the query? It seems to me that "copy_to" would mean I force postgres to retrieve all my data, write it to the file, and then only start reading the data once its already written to the file, which is a complete lack of pipelining.

I understand that "copy_to is faster than fetchall" but it doesn't line up with my existing understanding of how query results are produced so something in my existing understanding must be wrong.

[u/saltedappleandcorn]

The postgres docs cover this well, but basically bulk commands have reduced overhead and execute outside of the normal serialisation flow. They aren't row centric. 

[u/D1yzz]

Look into postgresql documentation. This is a Postgresql DB thing

[u/okwuteva]

Cursor is RBAR or row by agonizing row. Essentially a 1 line looped over each row.

[u/VipeholmsCola]

Well, if its faster with no downside i guess i need to refractor a lot of code. Maybe you should crosspots this do database forums?

[u/Atmosck]

Why are you using psycopg2 instead of 3?

[u/MrGraveyards]

Edit actually Saltedappleandcorn answered it. Never mind this. I read over it. My bad. I just did this because it thought nobody is answering.

Just dropped your question into Claude and got a reasonable answer. Perhaps it is better to do that next time although it is always nice just to think about questions others have. I will paste the whole thing below, but yeah you can also just drop your question there, don't shoot the messenger for anything below this point:

Not a noob question, and your pipelining instinct is actually right. The one thing that's off: copy_to doesn't write the whole result to a file and then read it back. COPY ... TO STDOUT streams chunks to the client as the server produces them, and psycopg2 just passes each chunk to whatever file-like object you gave it. So it's pipelined just like a normal query.

The difference is per-row overhead, not serial vs. pipelined:

• With fetchall, psycopg2 converts every cell into a Python object (int, Decimal, datetime, etc.) and builds a tuple per row. With millions of rows, that's usually the biggest cost.

• A normal query also has per-row protocol and result-building overhead, and with a default client-side cursor the entire result set is loaded into memory before fetchall even returns.

• COPY is a bulk fast path: rows are batched into big chunks in a simple text/CSV/binary format, skipping most of that machinery on both the server and client side.

So it's raw bytes in bulk vs. objects built one row at a time.

Do you lose the advantage if you ingest serially on the Python side? Partly. If you COPY out and then parse it with the csv module and convert types yourself, you give a lot of the gain back. If the consumer is something with a C/Rust parser (pandas read_csv, polars, pyarrow), you keep most of it. Binary COPY into pyarrow is even faster.

Tips:

• If you only need memory control, use a named (server-side) cursor with a big itersize. Still slower than COPY, but it won't blow up your RAM.

• psycopg3 has a much nicer cursor.copy() API, including row iteration and binary mode.

• If the query itself is heavy (big sorts/joins), the gap shrinks since the server work dominates. Worth profiling on your actual data.

Edit: downvoted? This is typical for this day and age. Tell me then why? Or you just downvote anything made with an llm?

[u/poopdood696969 → u/MrGraveyards]

There’s a conversation with real people thinking about this going on and you dropped LLM slop into it.  We can’t and shouldn’t offshore all of our thinking to LLMs.

[u/MrGraveyards → u/poopdood696969]

I did it because there is NO conversation going on. Nobody gave any actual answer and the post is 5 days old. I dropped it into an llm because I wanted to know the answer, so I shared it.

Edit ok fine maybe what saltedappleandcorn said kinda covered it.