Unlocking Parallel Query in postgres_fdw
October 20–23
postgres_fdw is the standard way to query remote PostgreSQL servers, but every scan uses a cursor, and cursors prevent the remote server from using parallel query plans. For large analytical queries, this means leaving significant performance on the table.
This talk presents streaming_fetch, a new option for postgres_fdw that replaces cursor-based fetching with libpq's chunked rows mode. When enabled, the remote query gets results stream back in a configurable chunk, Now, since this doesn't use cursors, the remote server can use a parallel plan if it is performant. The option is available at both server and table level and defaults to off, so existing behavior is preserved.
The interesting engineering challenge is multi-scan coordination. Cursors are naturally multiplexed on a single connection — several can be open simultaneously with no conflict. Streaming queries cannot: only one query can be in flight on a connection at a time. When a second scan starts on the same connection the ongoing stream must be drained and buffered before the new query can be sent. We solved this by draining remaining chunks into a tuplestore scan then the query fetches from it on demand.
The talk covers the design of the fetch mechanism, the connection state tracking required to coordinate concurrent scans and the rescan handling that ties it together. We will also discuss what the patch deliberately does not do — async execution is incompatible with streaming mode and is disabled automatically , this is a work in progress and the patch is available in commitfest.
The talk will provide a concrete understanding of how postgres_fdw's scan lifecycle works, where the parallel-query barrier lies today, and how streaming fetch removes it.
