Oatmeal Agitante

my plan is to try something like this: https://gist.github.com/mangecoeur/1fbd63d4758c2ba0c470 … but instead use to_csv in a named pipe or StringIO buffer to avoid reading / writing a file.

1. There is turbodbc with arrow support http://turbodbc.readthedocs.io/en/latest/pages/advanced_usage.html#apache-arrow-support … though unsure of the write support (definitely has bulk read just using basic select that’s fast)

I did look at turbodbc but there didn't seem to be much info on how well executemanycolumns with a dataframe worked.

That parser is very neat! I thought of embarking down that road but I read this in the docs and it scared me: "a binary-format file is less portable across machine architectures and PostgreSQL versions" - what has been your experience here, esp across versions?

Definitely not wrong; there's no proper "spec" for the binary format other than the postgres source code! It just reuses the code postgres uses to send values around internally. I haven't run into problems there but I've never had to do a big version upgrade.

Also though, don’t underestimate the inefficiency of pandas’ csv read/write, when string columns are involved. Huge bottleneck and pandas significantly underperforms here compared to most csv read/write tools

Welp, we basically did every trick in the book and now it looks like you were right - to_csv is now the bottleneck like you said. Might try a parallel insert first, but this might be next.