Database commands coalescing

[sebastienros]

The Npgsql (PostgresQL driver for .NET) contributors have been looking into other drivers implementation to understand where improvements can be done.

Here is a current picture for the multi-queries scenario:

There are several frameworks around 30-34K (not all displayed here) and then a subsequent jump to ~60K.

After analyzing the network traffic between the Drogon framework and the Postgres server, which is represented in the following screenshot and validated by other specific payloads, it appears that queries issued from multiple http requests are coalesced in the same network packets and executed with a single "SYNC" command which will tell Postgres to execute them "as a batch".

Bundling together multiple commands in a single Sync batch has important consequences: if any command errors (e.g. unique constraint violation), all later commands are skipped until the Sync. Even more importantly, all commands are implicitly part of the same transaction.

Here we have not only implicit batching for the same request (20 queries), but more importantly batching across requests.

Our interpretation of the TechEmpower rule below leads us to believe that this method violates those rules

Except where noted, all database queries should be delivered to the database servers as-is and not coalesced or deduplicated at the database driver. In all cases where a database query is required, it is expected that the query will reach and execute on the database server. However, it is permissible to pipeline traffic between the application and database as a network-level optimization. That is, two or more separate queries may be delivered from the application to the database in a single transmission over the network, and likewise for query results from the database back to the application, as long as the queries themselves are executed separately on the database server and not combined into a single complex query.

We haven't looked if the other frameworks showing the same high performance are using the same technique.

/cc @an-tao @roji

[roji]

Note: most of the investigation work above was done by @NinoFloris and @vonzshik from the Npgsql project.

[an-tao]

https://2ndquadrant.github.io/postgres/libpq-batch-mode.html

here I used the batch mode of postgresql driver, this work has been done two years ago, and the coalescing happens only when:

1. all sqls are read-only;
2. all sqls in pipeline are in the same call stack of the current event-loop;
   so if any wrriten sql is executed, the sync comand is sent immediately, you could see the network traffic in the update test to comfirm it. The rules say it's not permited to combine mutiple sqls into a single complex query, I don't think drogon broke it because every reading sql is executed separately (they are in the same batch and sent together , it's a network-level optimization)

[marty1885]

Hi, I'm another maintainer of Drogon.

I've chatted with @an-tao privately. He is not as fluent in English. Poke me if you need help explaining this reply. Please allow me to explain my view and response of the rules in detail. My views may differ with his, so I don't represent his position. I think this in a reasonable framework-level optimization. Hopefully helping both parties understand the differences.

all database queries should be delivered to the database servers as-is and not coalesced or deduplicated at the database driver.

In drogon with PQ-batch support. We control when the sync happens. It's a framework level optimization. To be specific, a sync happens when a SQL query that modifies data occurs, enough commands are batched or when the web application wasn't busy adding more queries to the batch. Therefor a batch only consists of read queries or ends with a write query. Any write fails (transaction conflict, uniqueness failure, etc..) will not cause reads to fail. And no subsequent commands exists to be skipped.

In all cases where a database query is required, it is expected that the query will reach and execute on the database server.

Besides above. The SQL banchmark only reads from the DB. Thus it's impossible to have a transaction error causing queries to fail. The test itself guarantee that. I think we are safe in this regard too.

Furthermore, I also think this is a reasonable optimization in practice. Since a batch always ends with a write. We guarantee all reads are executed (Note that SELECT .. FOR UPDATE is considered writing). The only edge case is when the web app runs an intentionally bad SELECT query. Which could get placed in the middle of a batch. But I consider it an application bug and I won't blame the framework for it.

[Brar]

@marty1885
Just for clarification. By "intentionally bad SELECT query" you mean any SELECT query that fails and causes the whole transaction to rollback instead of only the failing query - no?

[marty1885]

No, I mean by some sequence of non-transaction queries.

co_await db->execSqlCoro("SELECT xxx FROM my_table WHERE ....");
co_await db->execSqlCoro("SELECT xxx FROM non_exist_table WHERE ....");;
co_await db->execSqlCoro("SELECT xxx FROM my_table WHERE ...."); // This gets dropped.

I think all queries in transaction are considered writing. Thus always not batched. Is it? If not, I think we need to add a special case. In any case, it doesn't invalidate this optimization. @an-tao

Also I think the performance benefits comes from sending all the queries at the same time. Only sending sync at the end is quark of pq_batch. We could spend some time look into the updated libpq and resolve the entire issue all together.

[vonzshik]

We guarantee all reads are executed (Note that SELECT .. FOR UPDATE is considered writing).

@marty1885 I assume you're talking about query parsing, which doesn't check for function and procedure calls (which can do inserts/updates). There is also a problem of PG adding a new keyword (however improbable it is), which might have side effects.

But I consider it an application bug and I won't blame the framework for it.

Speaking from my experience, I'm working on an app, which allows to generate queries via SQL with a bit of a meta language. As an example:

SELECT *
FROM "SomeTable"
WHERE 1 = 1
#param(ID, "ID")
#param(Value, "Value")

A user via UI can specify whether he wants to filter by ID or by Value (or both), and the corresponding SQL is generated with the required parameters. With some queries taking a hundreds of lines, it's not that rare for some combinations of parameters to generate an invalid SQL. While it's definitely not great, there is nothing requiring immediate fixing, since only the specific combination is broken. But if we were using drogon, that would start breaking unrelated queries, making that issue critical and requiring immediate fixing.

Just wondering, is batching (and possible issues from it) mentioned in drogon's documentation?

[Brar]

Sorry, by transaction I meant everything that the driver puts before a sync packet.

The issue I see is something like the following:

co_await db->execSqlCoro("SET statement_timeout TO 10000");
co_await db->execSqlCoro("SELECT xxx FROM my_table WHERE ....");

// Simulates a query that might happen to take slightly longer than statement_timeout under some load conditions
co_await db->execSqlCoro("SELECT pg_sleep(15)");

co_await db->execSqlCoro("SELECT xxx FROM my_table WHERE ...."); // This gets dropped.

[roji]

We guarantee all reads are executed (Note that SELECT .. FOR UPDATE is considered writing).

@marty1885 I assume you're talking about query parsing, which doesn't check for function and procedure calls (which can do inserts/updates). There is also a problem of PG adding a new keyword (however improbable it is), which might have side effects.

I want to make sure this crucial point by @vonzshik is clear... As I understand it, if a command is executed which happens to SELECT from a function, and around the same time another unrelated command is executed which produces an error (e.g. unique constraint violation), then any side-effects from the function may get rolled back, without any indication to the program that this happened. That is, the user code executing the function will continue as if the function completed successfully and its side effects were committed, when in fact they were silently rolled back.

There may be other such scenarios where "implicit" side-effects aren't detected. So I think the idea that a database driver can reliably parse SQL to know whether it's read-only is quite problematic.

@marty1885, you wrote:

Also I think the performance benefits comes from sending all the queries at the same time.

In our experience, sending the Sync at the end has quite a significant impact on performance (FWIW Npgsql does send all commands at the same time like Drogon, but with a Sync after each command instead of at the end). This is likely related with the PostgreSQL MVCC overhead of creating and committing many small transactions (Npgsql) vs. a single transaction which encompasses all commands (Drogon).

Only sending sync at the end is quark of pq_batch. We could spend some time look into the updated libpq and resolve the entire issue all together.

Importantly, Sync at the end is indeed the desirable behavior when application code wants to send a set of related commands, as a batch: either all commands complete or none do (I believe this was the intent of the PG pipelining mode). The crucial difference is that the application explicitly opts into this behavior - via some API gesture - whereas Drogon is applying this implicitly on unrelated commands, under the hood.

[marty1885]

@vonzshik

I assume you're talking about query parsing, which doesn't check for function and procedure calls (which can do inserts/updates). There is also a problem of PG adding a new keyword (however improbable it is), which might have side effects.

Yeah. We noticed that earlier too. We will patch that.

Just wondering, is batching (and possible issues from it) mentioned in drogon's documentation?

Drogon's batching support requires a patched version of libpq (we haven't implement support after the feature being upstreamed). So we expect the users to know what they get getting themselves into at this point.

@Brar

Yeah.. that's an issue. But in a different way. Thanks for finding it.

co_await db->execSqlCoro("SELECT pg_sleep(15)"); suspends current coroutine from execution. No further code in the same coroutine gets executed. Since that statement always error out, it causes co_await <awaiter> to throw exception. And other SELECT statements in the same batch would also error out and throw. (My bad for the code I shared above. It's a quick illustration)

// coroutine 1
{
co_await db->execSqlCoro("SELECT pg_sleep(15)"); // This fails. Throw
co_await db->execSqlCoro("SELECT xxx FROM my_table WHERE ...."); // CPU never runs this because of throw
}

// coroutine 2
{
// Throws because the statement gets bundled and gets skipped.
co_await db->execSqlCoro("SELECT xxx FROM my_table WHERE ....");
}

@roji

In our experience, sending the Sync at the end has quite a significant impact on performance

Thanks for pointing it out. I'm discussing with @an-tao to determine that's the best action to fix this. Likely we'll add an option to opt-in to batch mode if we detect batch support.

---

Back to the original issue. I still think batching should be allowed in benchmark as it produces known behavior and does not break program flow when error happens. And it does not violate the rules under the benchmark.

[roji]

I assume you're talking about query parsing, which doesn't check for function and procedure calls (which can do inserts/updates). There is also a problem of PG adding a new keyword (however improbable it is), which might have side effects.

Yeah. We noticed that earlier too. We will patch that.

How do you propose to patch this? If the idea is to disable batching, that would mean the driver parsing to detect arbitrary function calls within arbitrary user SQL - that once again sounds quite problematic and brittle?

Back to the original issue. I still think batching should be allowed in benchmark as it produces known behavior and does not break program flow when error happens. And it does not violate the rules under the benchmark.

Stepping back here, all the various problems signaled above a result of Drogon applying implicit batching under the hood; this technique changes actual program behavior in a significant way - and does break program flow in ways that are very hard to predict and diagnose (as everything depends on timing). It's hard to see how this could be treated as a transparent network optimization.

Compare this with sending all the commands together, but with Syncs between each command; this indeed guarantees at the protocol level that each command runs independently of each other, and errors are guaranteed to not affect unrelated commands; IMHO this is what is meant by a network-level optimization.

[marty1885]

How do you propose to patch this? If the idea is to disable batching, that would mean the driver parsing to detect arbitrary function calls within arbitrary user SQL - that once again sounds quite problematic and brittle?

We'll add additional checks to prevent common cases. I think it's quite rate for this to happen since we haven't get user complaining it yet. Then figure out how to solve this.

Drogon applying implicit batching under the hood; this technique changes actual program behavior in a significant way - and does break program flow in ways that are very hard to predict and diagnose ... and errors are guaranteed to not affect unrelated commands; IMHO this is what is meant by a network-level optimization.

I think this is acceptable. At least for me, as a C++ developer. We are used to dealing with data races and feels fine as long as timing doesn't crash the application or causes unrecoverable state change. It's perfectly ok for lock-free C++ algorithms to return error when stuff doesn't work this try. This characteristic gives me the same vibe. The maintainers are also treating SQL this way.

So in conclusion, is the following allowed?

• Drogon must send sync for every command
• Add an batch flag to batch commands (and don't sync every command) that we know does not write

[roji]

Drogon applying implicit batching under the hood; this technique changes actual program behavior in a significant way - and does break program flow in ways that are very hard to predict and diagnose ... and errors are guaranteed to not affect unrelated commands; IMHO this is what is meant by a network-level optimization.

I think this is acceptable. At least for me, as a C++ developer. We are used to dealing with data races and feels fine as long as timing doesn't crash the application or causes unrecoverable state change. It's perfectly ok for lock-free C++ algorithms to return error when stuff doesn't work this try. This characteristic gives me the same vibe. The maintainers are also treating SQL this way.

I agree that if an error were returned and the program could try again, this would maybe be acceptable. But here we're talking about database changes being silently rolled back - without an error - because some other unrelated command happened to error. This is quite different from attempting a lock-free operation and retrying it; it's more like your lock-free operation saying it succeeded, when in fact it didn't. I have no idea how I'd write reliable code in this situation, and so IMHO this does qualify as causing "unrecoverable state change". Regardless of how rare such a scenario is or what kind of SQL it requires from users, I don't believe a database driver should do something like this.

I do agree that it would be helpful for the benchmark rules to be more explicit on these nuances - IMHO it's important for the benchmarks not to encourage unsafe/dangerous driver behavior (as least according to my viewpoint).

[marty1885]

database changes being silently rolled back - without an error

I to think about this. I think the current behavior is that driver notices something failed. Then framework understands some SQL commands gets dropped. Then the entire list of yet-to-be executed SQL calls throws error. Which is then recoverable.

Please help us determine what's the acceptable behavior for the benchmark. We will update our benchmark and framework.

[roji]

database changes being silently rolled back - without an error

I to think about this. I think the current behavior is that driver notices something failed. Then framework understands some SQL commands gets dropped. Then the entire list of yet-to-be executed SQL calls throws error. Which is then recoverable.

@marty1885 I'm not referring to yet-to-be-executed SQL statements - I do understand these are skipped and the user gets an error. The problem is with SQL statements which were already previously executed, already completed, and are now rolled back because of the error.

[marty1885]

Ahhh... I didn't realize that