Oatmeal Agitante

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.

Ah, good luck! Curious to hear how it goes and happy to answer questions I can!

Hey, quick question, what is going on here in this whole if statement? Isn't returning col.astype('category') enough? I can see it being something with the types of the categories but then how does len(cats) help you decide anything? https://github.com/spitz-dan-l/postgres-binary-parser/blob/64f54da0c6e6821467a217bfc3b0726f10b12173/postgres_binary_parser/schema.py#L382 …

The intention was: turn it categorical, and force the categories to be strings. reduce_categories was written before remove_unused_categories was added to pandas, so it was intended to make the string conversion as efficient as possible. Probably not relevant to your use case