You need to flush the stream using flush_output/1. B.t.w., there is no need to set the stream to binary. Socket streams start binary. Only for opening a file the default is to use text mode.
Did you actually measure that? It of course it depends on the driver, but my experience is not too bad. Anyway, you re not going to win using Prolog low-level I/O primitives. The Prolog overhead of byte-level I/O is quite surely going to be a lot higher than the ODBC overhead. Prolog is rather poorly equipped for low-level I/O. If you want full speed, write a wrapper around the native postgres client library in C or C++.
The point is, there are quite a few databases around and it is quite a bit of work to write a good and comprehensive database interface. Just check out the odbc wrapper sources. Probably the wrapper for an individual database is a little easier because the ODBC wrapper needs runtime quirks to deal with different features and limits provided by the various drivers, but still …
odbc is outdated, it seems to me that it is no longer worth using it now. I have experience using wired protocol libraries in other languages. I don’t want to pull dependencies into the project to connect with postgres.
It is all up to you I just had a quick look at PostgreSQL: Documentation: 14: Chapter 34. libpq — C Library. It doesn’t look all that different from interfacing to ODBC. This implies that a comprehensive Postgress Prolog client library is roughly as complicated as the ODBC client Talking to the wire instead of this library is unlikely to make this any better. That said, if you want only a tiny bit of the functionality (which is not unlikely for a specific application) a direct binding might be worth considering. I’d first do some measurements, for example time Prolog ODBC against Python native binding (if it has one) for the things you want to do. Profiling the Prolog ODBC may also give a rough indication how much you can gain (for example using valgrind, so no recompilation is needed).