proposal: add new dblink function, dblink_wait_for_query()

On Tue, Sep 15, 2026 at 2:26 PM Merlin Moncure <mmoncure@gmail.com> wrote:

dblink has some nice capabilities for executing background queries, but it
is missing some functionality; there is no ability to open connections
asynchronously, and any polling of outstanding queries has do be done in
SQL with a dblink_is_busy() loop. The polling issue is more severe
especially if large amounts of result data are pulled over the connection
since only one PQconsumeInput can execute at any one time. This idea
attempts to mitigate that issue. dblink may be somewhat baroque, but it
remains to often be the best way to do db to db querying and the only way
to reliably way to issue background work in cloud SQL environments.

The basic idea here is to implement a new SQL API routine:
dblink_wait_for_query(_timeout INTERVAL, BOOL exit_immediately) -> TEXT[]

I have a WIP implementation of this. It was suprisingly easy. So far,
performance gains are nothing special, only 5-10% vs classic is_busy() loop
in most cases. It shines in scenarios where it waits on a mix of short
and long queries, as the client process doesn't need to loop, saving cpu
time and some tricky coding, which can be important in certain contexts.

The WaitEventSet must be rebuilt completely upon every new/existing query,
but FWICT that does not impact performance or unnecessarily complicate the
code, since a hash from socket to connection must be maintained anyways.
Other than that, there's not much to say about the implementation, the
WaitEventSet interface is really nice.

Code like this:
FOR c IN 1..conn_count
LOOP
IF finished[c]
THEN
CONTINUE;
END IF;

conn := 'test' || c;

IF dblink_is_busy('test' || c) = 0
THEN
_found := true;

PERFORM * FROM dblink_get_result(conn) AS R(V TEXT);
PERFORM * FROM dblink_get_result(conn) AS R(V TEXT);

finished[c] := true;
remaining := remaining - 1;

END IF;
END LOOP;

IF NOT _found
THEN
PERFORM pg_sleep(0.01);
END IF;

can be turned into this:
returned := dblink_wait_for_query(500.0, false);

FOREACH conn IN ARRAY returned
LOOP
PERFORM count(*) FROM dblink_get_result(conn) AS R(V TEXT);
PERFORM * FROM dblink_get_result(conn) AS R(V TEXT);
END LOOP;

Since performance gains were not quite as nice as I was hoping, I thought
I'd pause here and solicit feedback and/or other comments, or at least see
if there's any interest. m

merlin

On Tue, Sep 22, 2026 at 4:02 PM Merlin Moncure <mmoncure@gmail.com> wrote:

On Tue, Sep 15, 2026 at 2:26 PM Merlin Moncure <mmoncure@gmail.com> wrote:

dblink has some nice capabilities for executing background queries, but
it is missing some functionality; there is no ability to open connections
asynchronously, and any polling of outstanding queries has do be done in
SQL with a dblink_is_busy() loop. The polling issue is more severe
especially if large amounts of result data are pulled over the connection
since only one PQconsumeInput can execute at any one time. This idea
attempts to mitigate that issue. dblink may be somewhat baroque, but it
remains to often be the best way to do db to db querying and the only way
to reliably way to issue background work in cloud SQL environments.

The basic idea here is to implement a new SQL API routine:
dblink_wait_for_query(_timeout INTERVAL, BOOL exit_immediately) ->
TEXT[]

I have a WIP implementation of this. It was suprisingly easy. So far,
performance gains are nothing special, only 5-10% vs classic is_busy() loop
in most cases. It shines in scenarios where it waits on a mix of short
and long queries, as the client process doesn't need to loop, saving cpu
time and some tricky coding, which can be important in certain contexts.

The WaitEventSet must be rebuilt completely upon every new/existing query,
but FWICT that does not impact performance or unnecessarily complicate the
code, since a hash from socket to connection must be maintained anyways.
Other than that, there's not much to say about the implementation, the
WaitEventSet interface is really nice.

Code like this:
FOR c IN 1..conn_count
LOOP
IF finished[c]
THEN
CONTINUE;
END IF;

conn := 'test' || c;

IF dblink_is_busy('test' || c) = 0
THEN
_found := true;

PERFORM * FROM dblink_get_result(conn) AS R(V TEXT);
PERFORM * FROM dblink_get_result(conn) AS R(V TEXT);

finished[c] := true;
remaining := remaining - 1;

END IF;
END LOOP;

IF NOT _found
THEN
PERFORM pg_sleep(0.01);
END IF;

can be turned into this:
returned := dblink_wait_for_query(500.0, false);

FOREACH conn IN ARRAY returned
LOOP
PERFORM count(*) FROM dblink_get_result(conn) AS R(V TEXT);
PERFORM * FROM dblink_get_result(conn) AS R(V TEXT);
END LOOP;

Since performance gains were not quite as nice as I was hoping, I thought
I'd pause here and solicit feedback and/or other comments, or at least see
if there's any interest. m

merlin

The API seems limited in that it can only return an array of ready
connections from ALL the connections available to the main connection,
which feels limiting. I'd almost want the API to accept an array of
connections.

But I'm not sure it's even necessary, as I suspect a lot of the gains
you're getting is just reduced context switches. Could you get the same
savings from something like this?

-- create sub-connections, add each conn string to conn_array, add each to
variable conn_array of type text[]
WHILE true
LOOP
EXIT WHEN length(conn_array) < 1;

SELECT array_agg(c.conn)
INTO finished_arr
FROM unnest(conn_array) as c(conn)
WHERE dblink_is_busy(c.conn) = 0

IF finished_arr IS NULL
THEN
pg_sleep(0.1);
CONTINUE;
END IF;

FOREACH finconn IN ARRAY finished_arr
LOOP
PERFORM ...
PERFORM ...
END LOOP;
conn_array := ARRAY (SELECT unnest(conn_array) EXCEPT SELECT
unnest(finished_arr));
END LOOP;

On Tue, Sep 22, 2026 at 3:21 PM Corey Huinker <corey.huinker@gmail.com>
wrote:

On Tue, Sep 22, 2026 at 4:02 PM Merlin Moncure <mmoncure@gmail.com> wrote:

On Tue, Sep 15, 2026 at 2:26 PM Merlin Moncure <mmoncure@gmail.com>
wrote:

The basic idea here is to implement a new SQL API routine:
dblink_wait_for_query(_timeout INTERVAL, BOOL exit_immediately) ->
TEXT[]

The API seems limited in that it can only return an array of ready

connections from ALL the connections available to the main connection,
which feels limiting. I'd almost want the API to accept an array of
connections.

That's the point; it pushes the 'sleep loop' into WaitEventSetWait()
(basically, epoll) so that the SQL layer activates when there's something
to do; you get ready to consume connections back out of the interface (with
'exit_immediately' = true, that's exactly one, but if passed false you can
wait for more). This is exactly analogous to poll vs epoll, with perhaps
less spectacular gains, since with large connection_counts (> 100) other
factors sneak in.

The basic problem solved is that there is no real optimal value for the
pg_sleep(); In my experience simple query rates in the 10s of thousands
are possible in ideal conditions, and it's difficult to prevent stalls on
the sleep with irregular traffic. Low sleep values can help with that, but
then you burn CPU. Having said all that, I do kinda agree with you, maybe
not for exactly the stated reasons.

Here's a more complete example showing the difference:
https://pastebin.com/kCfVMbF1 I'm getting around 40% less time spent using
the new interface, but this would be under absolutely ideal conditions. In
the real world the surrounding management might make those gains
insubstantial. The loop to is_busy is unrolled, but that's close to
reality since you have to keep track of what the connection is actually
doing since is_busy returns 0 for idle connections. Lowering connection
counts brings results to par, but that's not practical forother reasons.

The output yieled is:
NOTICE: extension "dblink" already exists, skipping
NOTICE: classic
NOTICE: 19000 queries to go!
<snip>
NOTICE: 1000 queries to go!
NOTICE: 0 queries to go!
NOTICE: 00:00:01.001021
NOTICE: modified
NOTICE: 19000 queries to go!
<snip>
NOTICE: 1000 queries to go!
NOTICE: 0 queries to go!
NOTICE: 00:00:00.589864

...note the 30k+ query rate.

Another point: this could be a foundation for asynchronous connection
support, since SSL connections can stall for quite some time. This is
obviously a much more complicated change. But I'm not sure this is worth
moving forward with -- I was expecting more honestly. Also of note, this
really drains file descriptors, at least on mac. Thanks for the feedback!

merlin