You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@arrow.apache.org by "jonnor (via GitHub)" <gi...@apache.org> on 2025/07/25 10:43:15 UTC

[I] Expose postgres binary to Arrow conversion functions to Python [arrow-adbc]

jonnor opened a new issue, #3201:
URL: https://github.com/apache/arrow-adbc/issues/3201

   ### What feature or improvement would you like to see?
   
   Hi,
   thank you for the work on the ADBC library and specifically the Python support for Postgres. I have tested it over the last few weeks, and for fetching large dataframes (time-series in our case) - it is much faster than psycopg with pandas. Between 5-10x throughput, and with lower CPU usage both on client and database side. So that is very promising.
   I am now trying to integrate it into an existing Python web application, which uses psycopg2 for the database driver, with SQLAlchemy for connection management. And gevent for concurrency. And I would need to have the ADBC efficient queries integrated with that system somehow. Right now proper integration is not really possible, since the driver uses its own IO, with blocking reads. So the parts using ADBC would have to duplicate connection management (annoying but managable), and the blocking IO prevents concurrency with gevent (severely reduces performance, voiding the main motivation for using the project).
   
   So I was wondering if it would be possible to expose functions/classes that would take data on the Postgresql binary data format, and convert that into Arrow tables. That way, one could use the existing database driver for IO, and avoid conflicts wrt connection management and concurrency. Most PostgreSQL drivers support this now, for example there is `copy_expert()` in psycopg2, `cursor.copy()` in psycopg3, and `copy_from_query()` in asyngpg.
   
   I believe this would greatly ease the integration of ADBC into existing codebases. Which might in turn increase adoption. I am aware that this approach _might_ leave some performance on the table, and that is acceptable in my case - I am pretty sure it will continue to be much faster than approaches that use serialization.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@arrow.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Expose postgres binary to Arrow conversion functions to Python [arrow-adbc]

Posted by "WillAyd (via GitHub)" <gi...@apache.org>.
WillAyd commented on issue #3201:
URL: https://github.com/apache/arrow-adbc/issues/3201#issuecomment-3117702621

   I think what you've described is an accurate assessment of the current situation. Some possibilities, likely high effort and not a priority for the core arrow-adbc team, but of course further research is always encouraged @jonnor 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Expose postgres binary to Arrow conversion functions to Python [arrow-adbc]

Posted by "jonnor (via GitHub)" <gi...@apache.org>.
jonnor commented on issue #3201:
URL: https://github.com/apache/arrow-adbc/issues/3201#issuecomment-3117373507

   For inspiration, this is the approach taken by https://github.com/adriangb/pgpq
   However, it only supports writing to Postgres (converting Arrow to postgres _binary_), not reading from it (converting postgres binary to Arrow).


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Expose postgres binary to Arrow conversion functions to Python [arrow-adbc]

Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #3201:
URL: https://github.com/apache/arrow-adbc/issues/3201#issuecomment-3117454977

   Thoughts @WillAyd @zeroshade? I don't like saying no flat out but this is well outside our wheelhouse, IMO.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Expose postgres binary to Arrow conversion functions to Python [arrow-adbc]

Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #3201:
URL: https://github.com/apache/arrow-adbc/issues/3201#issuecomment-3117521327

   They're in https://github.com/apache/arrow-adbc/tree/main/c/driver/postgresql/copy


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Expose postgres binary to Arrow conversion functions to Python [arrow-adbc]

Posted by "jonnor (via GitHub)" <gi...@apache.org>.
jonnor commented on issue #3201:
URL: https://github.com/apache/arrow-adbc/issues/3201#issuecomment-3117421980

   And here are two other libraries that take this approach, for converting postgres copy binary to a DataFrame/table. However, they are unmaintained.
   https://github.com/grihabor/postgres-copy-binary
   https://github.com/spitz-dan-l/postgres-binary-parser
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Expose postgres binary to Arrow conversion functions to Python [arrow-adbc]

Posted by "jonnor (via GitHub)" <gi...@apache.org>.
jonnor commented on issue #3201:
URL: https://github.com/apache/arrow-adbc/issues/3201#issuecomment-3117496365

   Thanks for the very quick reponse! Understood that it might be a bit tricky with the architecture you have. Could you link to the current location of the Postgres<->Arrow converters? Maybe someone is interested in doing some surgery for that :D 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Expose postgres binary to Arrow conversion functions to Python [arrow-adbc]

Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #3201:
URL: https://github.com/apache/arrow-adbc/issues/3201#issuecomment-3117450992

   Hmm. The approach we took would make this hard: there is a generic C interface for all the drivers, with a common binding + dynamic loading, so there's not really room to add driver-specific functionality like this. It's maybe possible to take the Postgres<->Arrow converters and wrap them into a separate Python library, as they are somewhat separated out, but this would still require some surgery. 
   
   We are trying to work towards natively supporting asyncio, albeit that still wouldn't help when it comes to gevent (and is probably a ways off due to lack of resources). 
   
   I can think of _maybe_ one way to do this, which would be to abuse SetOption/GetOption: "set" an "option" to pass the raw binary data in, "get" an "option" (or maybe "execute a query") to get the Arrow data back out. (This would still need at least one memcpy since we can't guarantee the lifetime of the source data.) To be honest, I don't really want to do this because it would definitely be an abuse of the APIs. Even if we did do that, there are a ton of caveats: notably, the COPY data doesn't contain a schema/is not self-describing, so you would have to figure that out somehow. 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org