For batch insert it makes more sense to parse an insert statement for one row and then send bind-execute messages for each row. That would much more efficient than a huge insert statement with interpolated values.
Copy doesn't on conflict handling yet, although there doesn't seem to be a major reason why it couldn't. A workaround is to copy into a temporary table and do a insert-select from there.
What do you mean by bind-execute? I'm guessing you mean sending multiple statements in a string where the first PREPAREs and the rest EXECUTE? I haven't benchmarked anything like that.
I've done lots of exploring and profiling and comparing of the best ways to do big upserts into a variety of DBs. For Postgres, I've messed around with CTEs but found INSERT ON CONFLICT UPDATE to be fastest. And inserting large numbers of rows is a clear win over individual statements round-tripping.
PostgreSQL wire protocol has a concept extended query which splits out query execution into 3 steps, parse, bind and execute. https://www.postgresql.org/docs/devel/protocol-flow.html#PRO... If you send one parse message and multiple bind and execute messages you can insert multiple rows parsing the statement only once. Currently psycopg2 doesn't support that, which is the first thing the blog post was talking about.
If working in the confines of psycopg2, you could do something like insert .. on conflict .. select * from unnest(%s) rows(a int, b text, ...); and pass in an array of row types. Parsing a huge array is cheaper than parsing a huge SQL statement.
And relevant to the roundtrip issue at hand here: You can send bind/execute in a pipelined manner.
I'd benchmarked batch upserting at some point, and at that time for large amounts of data the fastest approach was somewhat unintuitive: A separate view with an INSTEAD trigger doing the upserting. That allows for use of COPY based streaming (less traffic than doing separate bind/exec, less dispatch overhead), and still allows use of upsert. Not a great solution, but ...
Copy doesn't on conflict handling yet, although there doesn't seem to be a major reason why it couldn't. A workaround is to copy into a temporary table and do a insert-select from there.