4 ms·
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 bench
by willvarfar 7y ago
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.
- ants_a 7y agoPostgreSQL 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#PROTOCOL-FLOW-EXT-QUERY 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.
- anarazel 7y agoAnd 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 ...