[odb-users] Postgres Bulk Operations on Windows?

[Raphael Palefsky-Smith]

I gave pipeline mode another shot and it was actually quite simple. I am
getting a *50x* speedup, even more on slower connections. So a huge thank
you for pipeline mode, it really does make a big difference. To get it
working, I changed all instances of:

#if defined(LIBPQ_HAS_PIPELINING) && !defined(_WIN32)

to:

#if defined(LIBPQ_HAS_PIPELINING)

And then I added the following two lines to pgsql/statement.cxx. There's
definitely a better way to link Winsock but I didn't want to dive into bpkg
just yet:

#include <WinSock2.h>
#pragma comment(lib, "Ws2_32.lib")

Cheers!

On Mon, Aug 12, 2024 at 5:08 PM Raphael Palefsky-Smith <me at raphieps.com>
wrote:

> 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.
>>
>