c/driver/postgresql: Postgres bulk insert is slow

[judahrand]

Currently the Postgres bulk inserter uses a standard INSERT INTO ... VALUES query to insert data. This is slow compared to COPY data FROM STDIN WITH (FORMAT BINARY).

Given that we already use COPY to fetch data fast it would make sense to me to use the same trick in reverse to insert data fast. And example project which does something similar is https://github.com/adriangb/pgpq.

[judahrand]

Something similar is tracked in #81

[paleolimbot]

I have also run into this...there is a COPY reader in https://github.com/apache/arrow-adbc/blob/main/c/driver/postgresql/postgres_copy_reader.h and it wouldn't be overly difficult to flip it inside out and use it to write COPY data instead. This would also work for generating prepared statement data since I think that's basically how we implement it.

[judahrand]

This looks amazing! The performance gains look to be very impressive. I assume that this won't see a Python release for a little while given that there are a fair few more issues marked with the 0.8 milestone?

@WillAyd thank you very much for the great work! I'm really looking forward to removing my psycopg + pgpq dependencies.

[lidavidm]

I probably won't kick things off until ~end of October

[vahid110]

We hit the same bottleneck. I ended up writing something that wraps COPY FROM STDIN for PostgreSQL writes. Benchmarked at 850x faster than executemany for 1M rows on RDS. Posted it on GH maybe it would be useful for someone https://github.com/arrowjet/arrowjet