Streaming output, without server-side cursor?

[amachanic]

Hello, this is more a background question than an actual issue - apologies if this isn't the right place for this sort of thing.

In psycopg2, if I select a large number of rows, psycopg2 will internally buffer them prior to my being able to consume them (i.e. by iterating over the output cursor). This has caused some major problems when selecting back many millions of rows. The workaround I've found is to create a named server-side cursor and tune down the itersize parameter, but this seems to have some negative implications from a performance perspective.

So my question is, can we expect improvements in this area in psycopg3? Is the current implementation necessary due to some Postgres wire protocol limitation, or is this something that can potentially be made better?

Thanks for any insights you can provide here, and for your work on this project. I'm excited to see what you do with it, regardless of whether or not this scenario happens to be solved.

[dvarrazzo]

Hello @amachanic

there are limitations not so much in the wire protocol, but in the server itself. When you run a query, you will get the whole response back from the server.

There might be a few workarounds using psycopg3:

• Use a server-side cursor. You seem to have explored this solution already in psycopg2 and I wouldn't expect substantial differences.
• Use COPY ... TO STDOUT: unlike psycopg2, psycopg3 can parse the records received into Python objects. This requires a bit more configuration compared to the normal query process, because the server doesn't pass the column types to the client, so you will need to call set_types(). At a guess, this could be your best choice.
• CockroachDB CHANGEFEED and Materialize TAIL are implementations of a concept of "streaming query" and psycopg3 can consume them using cursor.stream(), which is a demonstration that the limitation is in the server, not in the protocol. You might consider if these databases are better suited to your use case.

If you try any of these solution with psycopg3 we would be interested in feedback. Thank you very much!

[amachanic]

Thank you for the detailed response! It is very helpful. I'm looking forward to experimenting especially with the COPY option - seems like a potentially perfect fit for my use case. Will be watching for a stable build from you.

[sanchitram1]

just curious -- is the performance impact you'd get from tuning down cursor.itersize only down to an increase in the number of round-trips?

I've got a 100M+ row table that I need to load into a graph structure (using rustworkx for performance) for my network analysis application, and it takes about 2:15 hours to load. i'm exploring the cursor implementation as an optimization, and was curious about the potential negative impacts of that approach.