[odb-users] Postgres Bulk Operations on Windows?

[Raphael Palefsky-Smith]

Hi Boris - reviving this old topic, apologies for not responding before.

I've been looking into pipeline mode some more, and it seems non-trivial to
get working. However, I'm wondering if there are other, simpler methods to
bulk insert a bunch of rows.

I'm currently inserting ~750 records in a loop, and it's taking about 10ms
per record. An EXPLAIN ANALYZE on the insert query reports about 0.150ms
execution time, so I suspected the network as the primary bottleneck.
Indeed, from looking at some Wireshark traces, it appears that we're
waiting for a complete roundtrip to the database for each record.

This makes sense given that the INSERT returns the newly created row's ID,
and ODB has no way of knowing whether or not I'm going to use that ID
mid-loop and needs to wait for its return before executing the next
persist. So nothing can be optimized automatically. But perhaps there are
tricks to do this insert without the per-row roundtrip? Maybe using
Postgres arrays, or some other mechanism short of the full pipeline mode?

I'll be running some tests using raw SQL, but if you know of any "idiomatic
libodb" ways of doing this, let me know. Thanks again!



On Tue, Jun 25, 2024 at 5:27 AM Boris Kolpackov <boris at codesynthesis.com>
wrote:

> Raphael Palefsky-Smith <me at raphieps.com> writes:
>
> > Is this something that might be supported later, or is there something in
> > the Windows networking stack that makes this impossible?
>
> I am a bit vague on this (it's been a while since I implemented this
> support), but I believe it was either unsupported in libpq or required
> a separate code on the ODB side (Winsock and all that fun). I think to
> be sure you will have to dig into libpq and, if pipelining is supported
> on Windows, see what it will take to support on the ODB side.
>