Re: POC: postgres_fdw insert batching

On Fri, Nov 27, 2020 at 10:47 AM tsunakawa(dot)takay(at)fujitsu(dot)com <
tsunakawa(dot)takay(at)fujitsu(dot)com> wrote:

Covering this one first:

I expect postgresExecForeignBatchInsert() would be able to use the libpq
> batching API, because it receives an array of tuples and can generate and
> issue INSERT statement for each tuple.

Sure, you can generate big multi-inserts. Or even do a COPY. But you still
have to block for a full round-trip until the foreign server replies. So if
you have 6000 calls to postgresExecForeignBatchInsert() during a single
query, and a 100ms round trip time to the foreign server, you're going to
waste 6000*0.1 = 600s = 10 min blocked in postgresExecForeignBatchInsert()
waiting for results from the foreign server.

Such batches have some major downsides:

* The foreign server cannot start executing the first query in the batch
until the last query in the batch has been accumulated and the whole batch
has been sent to the foreign server;
* The FDW has to block waiting for the batch to execute on the foreign
server and for a full network round-trip before it can start another batch
or let the backend do other work
This means RTTs get multiplied by batch counts. Still a lot better than
individual statements, but plenty slow for high latency connections.

* Prepare 1000 rows to insert [10ms]
* INSERT 1000 values [100ms RTT + 50ms foreign server execution time]
* Prepare 1000 rows to insert [10ms]
* INSERT 1000 values [100ms RTT + 50ms foreign server execution time]
* ...

If you can instead send new inserts (or sets of inserts) to the foreign
server without having to wait for the result of the previous batch to
arrive, you can spend 100ms total waiting for results instead of 10 mins.
You can start the execution of the first query earlier, spend less time
blocked waiting on network, and let the local backend continue doing other
work while the foreign server is busy executing the statements.

The time spent preparing local rows to insert now overlaps with the RTT and
remote execution time, instead of happening serially. And there only has to
be one RTT wait, assuming the foreign server and network can keep up with
the rate we are generating requests at.

I can throw together some diagrams if it'll help. But in the libpq
pipelining patch I demonstrated a 300 times (3000%) performance improvement
on a test workload...

Anyway, this thread's batch insert can be progressed (and hopefully
> committed), and once the libpq batching has been committed, we can give it
> a try to use it and modify postgres_fdw to see if we can get further
> performance boost.
>

My point is that you should seriously consider whether batching is the
appropriate interface here, or whether the FDW can expose a pipeline-like
"queue work" then "wait for results" interface. That can be used to
implement batching exactly as currently proposed, it does not have to wait
for any libpq pipelining features. But it can *also* be used to implement
concurrent async requests in other FDWs, and to implement pipelining in
postgres_fdw once the needed libpq support is available.

I don't know the FDW to postgres API well enough, and it's possible I'm
talking entirely out of my hat here.

> From: Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>
> > Not sure how is this related to app developers? I think the idea was
> > that the libpq features might be useful between the two PostgreSQL
> > instances. I.e. the postgres_fdw would use the libpq batching to send
> > chunks of data to the other side.
>
> > Well, my point was that we could keep the API, but maybe it should be
> > implemented using the proposed libpq batching. They could still use the
> > postgres_fdw example how to use the API, but the internals would need to
> > be different, of course.
>
> Yes, I understand them. I just wondered if app developers use the
> statement batching API for libpq or JDBC in what kind of apps.

For JDBC, yes, it's used very heavily and has been for a long time, because
PgJDBC doesn't rely on libpq - it implements the protocol directly and
isn't bound by libpq's limitations. The application interface for it in
JDBC is a batch interface [1][2], not a pipelined interface, so that's what
PgJDBC users interact with [3] but batch execution is implemented using
protocol pipelining support inside PgJDBC [4]. A while ago I did some work
on deadlock prevention to work around issues with PgJDBC's implementation
[5] which was needed because the feature was so heavily used. Both were to
address customer needs in real world applications. The latter increased
application performance over 50x through round-trip elimination.

For libpq, no, batching and pipelining are not yet used by anybody because
application authors have to write to the libpq API and there hasn't been
any in-core support for batching. We've had async / non-blocking support
for a while, but it still enforces strict request/response ordering without
interleaving, so application authors cannot make use of the same postgres
server and protocol capabilities as PgJDBC. Most other drivers (like
psqlODBC and psycopg2) are implemented on top of libpq, so they inherit the
same limitations.

I don't expect most application authors to adopt pipelining directly,
mainly because hardly anyone writes application code against libpq anyway.
But drivers written on top of libpq will be able to adopt it to expose the
batching, pipeline, or async/callback/event driven interfaces supported by
their client database language interface specifications, or expose their
own extension interfaces to give users callback-driven or batched query
capabilities. In particular, psqlODBC will be able to implement ODBC batch
query [6] efficiently. Right now psqlODBC can't execute batches efficiently
via libpq, since it must perform one round-trip per query. It will be able
to use the libpq pipelining API to greatly reduce round trips.

>
> But I'm not sure either if the libpq batching is likely to be committed
> in the near future. (The thread looks too long...)

I think it's getting there tbh.

>
> Regards
> Takayuki Tsunakawa
>
>
[1]
https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#executeBatch()
[2]
https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#addBatch()
[3]
https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/test/java/org/postgresql/test/jdbc2/BatchExecuteTest.java
[4]
https://github.com/pgjdbc/pgjdbc/blob/ff22a3c31bb423b08637c237cb2e5bc288008e18/pgjdbc/src/main/java/org/postgresql/core/v3/QueryExecutorImpl.java#L492
[5] https://github.com/pgjdbc/pgjdbc/issues/194
[6]
https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/executing-batches?view=sql-server-ver15