PostgreSQL 14+ Pipelining Support

[MicahLyle]

PostgreSQL 14 introduced the ability to Pipeline queries.

Are there any plans to support this with asyncpg in any capacity? At the time of writing, I'm looking to optimize large numbers of inserts and updates, and pipelining seems like it could be an interesting solution. I checked the existing issues and didn't seem to find anything related to this so I thought I'd open a fresh issue/feature request.

[elprans]

Pipelining is already supported via executemany.

[chdsbd]

@elprans executemany discards the results, so it doesn't work in all the cases that pipelining would be useful.

In my case, it would be useful to be able to pipeline SELECT statements.

[elprans]

@chdsbd can you describe your use case in some detail?

[chdsbd]

I have multiple queries that I want to run against Postgres.

Currently I can send these queries with two await conn.fetch(...), but it would be better if I didn't have to wait for the first one to return.

select * from comments where user_id = 5;
select * from edits where user_id = 5;

[elprans]

Pipelining only makes sense if you are sending lots and lots of queries as is the case with bulk insert. For regular queries it makes very little sense in the face of substantial implementation complexity, cancellation issues etc. In your case you can trivially emulate pipelining by querying both tables at the same time like so:

    comments, edits = await conn.fetchrow('''
        select
           (select array_agg(comments.*) from comments where user_id = 5),
           (select array_agg(edits.*) from edits where user_id = 5)
    ''')

[chdsbd]

@elprans Thanks for the suggestion!

[FeldrinH]

Pipelining only makes sense if you are sending lots and lots of queries as is the case with bulk insert. For regular queries it makes very little sense in the face of substantial implementation complexity, cancellation issues etc.

I somewhat disagree. Pipelining also makes sense with a smaller number of queries if the query execution time is dominated by network latency.

Also, if executemany already supports pipelining then doesn't that mean that asyncpg already has the implementation complexity in its codebase, or is the executemany pipelining somehow simpler than the general case?