Latency at the edge with Rust/WebAssembly and Postgres: Part 2
Tags: postgres, webassembly, cloudflare, worker
Published: 2024-07-11 23:12:32

---
title: Latency at the edge with Rust/WebAssembly and Postgres: Part 2
published: true
date: 2024-06-06 00:00:00 UTC
tags: postgres,WebAssembly,Cloudflare, Worker
canonical_url: https://exograph.dev/blog/wasm-pg-explorations-2
---

In the [previous post](https://exograph.dev/blog/wasm-pg-explorations-1), we implemented a simple Cloudflare Worker in Rust/WebAssembly connecting to a [Neon](https://neon.tech/) Postgres database and measured end-to-end latency. Without any pooling, we got a mean response time of 345ms.

The two issues we suspected for the high latency were:

> **Establishing connection to the database** : The worker creates a new connection for each request. Given that a secure connection, it takes 7+ round trips. Not surprisingly, latency is high.
> 
> **Executing the query** : The query method in our code causes the Rust Postgres driver to make two round trips: to prepare the statement and to bind/execute the query. It also sends a one-way message to close the prepared statement.

In this part, we will deal with connection establishment time by introducing a pool. We will fork the driver to deal with multiple round trips (which incidentally also helps with connection pooling). We will also learn a few things about Postgres's query protocol.

<svg viewbox="0 0 12 16"><path fill-rule="evenodd" d="M6.5 0C3.48 0 1 2.19 1 5c0 .92.55 2.25 1 3 1.34 2.25 1.78 2.78 2 4v1h5v-1c.22-1.22.66-1.75 2-4 .45-.75 1-2.08 1-3 0-2.81-2.48-5-5.5-5zm3.64 7.48c-.25.44-.47.8-.67 1.11-.86 1.41-1.25 2.06-1.45 3.23-.02.05-.02.11-.02.17H5c0-.06 0-.13-.02-.17-.2-1.17-.59-1.83-1.45-3.23-.2-.31-.42-.67-.67-1.11C2.44 6.78 2 5.65 2 5c0-2.2 2.02-4 4.5-4 1.22 0 2.36.42 3.22 1.19C10.55 2.94 11 3.94 11 5c0 .66-.44 1.78-.86 2.48zM4 14h5c-.23 1.14-1.3 2-2.5 2s-2.27-.86-2.5-2z"></path></svg>

> Source code

> The source code with all the examples explored in this post is available on [GitHub](https://github.com/exograph/wasm-pg-cloudflare-explorations). With it, you can perform measurements and experiments on your own.

## Introducing connection pooling[​](https://exograph.dev/blog/wasm-pg-explorations-2#introducing-connection-pooling "Direct link to Introducing connection pooling")

If the problem is establishing a connection, the solution could be a pool. This way, we can reuse the existing connections instead of creating a new one for each request.

### Application-level pooling[​](https://exograph.dev/blog/wasm-pg-explorations-2#application-level-pooling "Direct link to Application-level pooling")

Could we use a pooling crate such as [deadpool](https://github.com/bikeshedder/deadpool)?

While that would be a good option in a typical Rust environment (and Exograph uses it), it is not an option in the Cloudflare Worker environment. A worker is considered stateless and should not maintain any state between requests. Since a pool is a stateful object (holding the connections), it can't be used in a worker. If you try to use it, you will get the following runtime error on every other request:

```
Error: Cannot perform I/O on behalf of a different request. I/O objects (such as streams, request/response bodies, and others) created in the context of one request handler cannot be accessed from a different request's handler. This is a limitation of Cloudflare Workers which allows us to improve overall performance. (I/O type: WritableStreamSink)
```

<button type="button" aria-label="Copy code to clipboard" title="Copy"><span aria-hidden="true"><svg viewbox="0 0 24 24"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewbox="0 0 24 24"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button>

When the client makes the first request, the worker creates a pool and successfully executes the query. For the second request, the worker tries to reuse the pool, but it fails due to the error above, leading to the eviction of the worker by the Cloudflare runtime. For the third request, a fresh worker creates another pool, and the cycle continues.

The error is clear: we cannot use application-level pooling in this environment.

### External pooling[​](https://exograph.dev/blog/wasm-pg-explorations-2#external-pooling "Direct link to External pooling")

Since application-level pooling won't work in this environment, could we try an external pool? Cloudflare provides [Hyperdrive](https://developers.cloudflare.com/hyperdrive) for connection pooling (and more, such as query caching). Let's try that.

```rust
#[event(fetch)]
async fn main(_req: Request, env: Env, _ctx: Context) -> Result<Response> {
    let hyperdrive = env.hyperdrive("todo-db-hyperdrive")?;

    let config = hyperdrive
        .connection_string()
        .parse::<tokio_postgres::Config>()
        .map_err(|e| worker::Error::RustError(format!("Failed to parse configuration: {:?}", e)))?;

    let host = hyperdrive.host();
    let port = hyperdrive.port();

    // Same as before
}
```

<button type="button" aria-label="Copy code to clipboard" title="Copy"><span aria-hidden="true"><svg viewbox="0 0 24 24"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewbox="0 0 24 24"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button>

Besides how we get the host and port, the rest of the code (to connect to the database and execute the query) remains the same as the one in [part 1](https://exograph.dev/blog/wasm-pg-explorations-1).

You will need to create a Hyperdrive instance using the following command (replace the connection string with your own):

```sh
npx wrangler hyperdrive create todo-db-hyperdrive --caching-disabled --connection-string "postgres://..."
```

<button type="button" aria-label="Copy code to clipboard" title="Copy"><span aria-hidden="true"><svg viewbox="0 0 24 24"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewbox="0 0 24 24"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button>

We disable [query caching](https://developers.cloudflare.com/hyperdrive/configuration/query-caching/) since that will cause skipping most database calls. Due to the empty cache, the first request will hit the database. For subsequent requests (which execute the same SQL query in our setup), Hyperdrive will likely serve them from its cache. We are interested in measuring the latency to include database calls. With caching turned on, the comparison to the baseline would be apples-to-oranges.

<svg viewbox="0 0 14 16"><path fill-rule="evenodd" d="M6.3 5.69a.942.942 0 0 1-.28-.7c0-.28.09-.52.28-.7.19-.18.42-.28.7-.28.28 0 .52.09.7.28.18.19.28.42.28.7 0 .28-.09.52-.28.7a1 1 0 0 1-.7.3c-.28 0-.52-.11-.7-.3zM8 7.99c-.02-.25-.11-.48-.31-.69-.2-.19-.42-.3-.69-.31H6c-.27.02-.48.13-.69.31-.2.2-.3.44-.31.69h1v3c.02.27.11.5.31.69.2.2.42.31.69.31h1c.27 0 .48-.11.69-.31.2-.19.3-.42.31-.69H8V7.98v.01zM7 2.3c-3.14 0-5.7 2.54-5.7 5.68 0 3.14 2.56 5.7 5.7 5.7s5.7-2.55 5.7-5.7c0-3.15-2.56-5.69-5.7-5.69v.01zM7 .98c3.86 0 7 3.14 7 7s-3.14 7-7 7-7-3.12-7-7 3.14-7 7-7z"></path></svg>

> For the real-world scenario, you may enable caching to balance database load and freshness of data.

Next, you will need to put the Hyperdrive information in wrangler.toml:

```toml
[[hyperdrive]]
binding = "todo-db-hyperdrive"
id = "<your-hyperdrive-id>"
```

<button type="button" aria-label="Copy code to clipboard" title="Copy"><span aria-hidden="true"><svg viewbox="0 0 24 24"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewbox="0 0 24 24"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button>

Let's test this worker.

```sh
curl <worker-url>INTERNAL SERVER ERROR
```

<button type="button" aria-label="Copy code to clipboard" title="Copy"><span aria-hidden="true"><svg viewbox="0 0 24 24"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewbox="0 0 24 24"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button>

Hmm... that failed.

<svg viewbox="0 0 16 16"><path fill-rule="evenodd" d="M8.893 1.5c-.183-.31-.52-.5-.887-.5s-.703.19-.886.5L.138 13.499a.98.98 0 0 0 0 1.001c.193.31.53.501.886.501h13.964c.367 0 .704-.19.877-.5a1.03 1.03 0 0 0 .01-1.002L8.893 1.5zm.133 11.497H6.987v-2.003h2.039v2.003zm0-3.004H6.987V5.987h2.039v4.006z"></path></svg>

> Fast moving ground

> This is due to an issue with the current Hyperdrive implementation. The support for prepared statements is still new and (currently) works only with caching enabled. I have made the Cloudflare team aware of it. I think this will be fixed soon 🤞.

As things change, I will add updates here.

What's going on? Postgres has two kinds of query protocols:

1. [Simple query protocol](https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-SIMPLE-QUERY): With this protocol, you must supply the SQL as a string and include any parameter values in the query (for example, `SELECT * FROM todos WHERE id = 1`). The driver makes one round trip to execute such a query.
2. [Extended query protocol](https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY): With this protocol, you may have the SQL query with placeholders for parameters (for example, `SELECT * FROM todos WHERE id = $1`), and its execution requires a preparation step. We will go into detail in the [next section](https://exograph.dev/blog/wasm-pg-explorations-2#hyperdrive-with-the-extended-query-protocol).

Let's explore both protocols.

## Hyperdrive with the simple query protocol[​](https://exograph.dev/blog/wasm-pg-explorations-2#hyperdrive-with-the-simple-query-protocol "Direct link to Hyperdrive with the simple query protocol")

To explore the simple query protocol, we will use the [`simple_query`](https://docs.rs/tokio-postgres/latest/tokio_postgres/struct.Client.html#method.simple_query) method. Since it doesn't allow specifying parameters, we inline them.

```rust
#[event(fetch)]
async fn main(_req: Request, env: Env, _ctx: Context) -> Result<Response> {

    /// Hyperdrive setup as before

    let rows = client
        .simple_query("SELECT id, title, completed FROM todos WHERE completed = true")
        .await
        .map_err(|e| worker::Error::RustError(format!("Failed to query: {:?}", e)))?;

    ...
}
```

<button type="button" aria-label="Copy code to clipboard" title="Copy"><span aria-hidden="true"><svg viewbox="0 0 24 24"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewbox="0 0 24 24"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button>

Does it work and how does this perform?

```sh
$ oha -c 1 -n 100 <worker-url>
  Slowest:      0.2871 secs
  Fastest:      0.0476 secs
  Average:      0.0633 secs
```

<button type="button" aria-label="Copy code to clipboard" title="Copy"><span aria-hidden="true"><svg viewbox="0 0 24 24"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewbox="0 0 24 24"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button>

That's more like it! The mean response time is **63ms** , a significant improvement over the previous 345ms.

Since the simple query protocol needed only one round trip, Hyperdrive was able to use an existing connection without too much additional logic, so it worked without any issues and performed well.

But... the simple query protocol forces us to use string interpolation to inline the parameters in the query, which is a big no-no in the world of databases due to the risk of SQL injection attacks. So let's not do that!

## Hyperdrive with the extended query protocol[​](https://exograph.dev/blog/wasm-pg-explorations-2#hyperdrive-with-the-extended-query-protocol "Direct link to Hyperdrive with the extended query protocol")

Let's go back to the extended query protocol and figure out why Hyperdrive might be struggling with it. As it happens, all external pooling services deal with the same issue; for example, only recently did [pgBouncer](https://www.pgbouncer.org/2023/10/pgbouncer-1-21-0) start to support it.

When using the extended query protocol through [`query`](https://docs.rs/tokio-postgres/latest/tokio_postgres/struct.Client.html#method.query), the driver executes the following steps:

1. **Prepare** : Sends a prepare request. This contains a name for the statement (for example, "s1") and a query with the placeholders for parameters to be provided later (for example, $1, $2 etc.). The server sends back the expected parameter types.
2. **Bind/execute** : Sends the name of the prepared statement and the parameters serialized in the format appropriate for the types. The server looks up the prepared statement by name and executes it with the provided parameters. It sends back the rows.
3. **Close** : Closes the prepared statement to free up the resources on the server. In `tokio-postgres`, this is a fire-and-forget operation (doesn't wait for a response).
<!-- --> ![](https://exograph.dev/assets/images/extended-quey-light-609b32186fae816db2d153a8a7a66f17.png#gh-light-mode-only)

When you add in a connection pool, the driver must invoke the "bind/execute" and "close" with the same connection it used for "prepare". This requires some bookkeeping and is a source of complexity.

What if we combine all three steps into a single network package? This is what Exograph's fork of `tokio-postgres` ([fork](https://github.com/exograph/rust-postgres/tree/exograph), [PR](https://github.com/sfackler/rust-postgres/pull/1147)) does. The client must specify the parameter values _and their types_ (we no longer perform a round trip to discover parameter types). This way, the driver can serialize the parameters in the correct format in the same network package.

```rs
#[event(fetch)]
async fn main(_req: Request, env: Env, _ctx: Context) -> Result<Response> {

    /// Hyperdrive setup as before

    let rows: Vec<tokio_postgres::Row> = client
        .query_with_param_types(
            "SELECT id, title, completed FROM todos where completed <> $1",
            &[(&true, Type::BOOL)],
        )
        .await
        .map_err(|e| worker::Error::RustError(format!("query_with_param_types: {:?}", e)))?;

    ...
}
```

<button type="button" aria-label="Copy code to clipboard" title="Copy"><span aria-hidden="true"><svg viewbox="0 0 24 24"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewbox="0 0 24 24"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button>

How does this perform?

```
$ oha -c 1 -n 100 <worker-url>
  Slowest:      0.2883 secs
  Fastest:      0.0466 secs
  Average:      0.0620 secs
```

<button type="button" aria-label="Copy code to clipboard" title="Copy"><span aria-hidden="true"><svg viewbox="0 0 24 24"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewbox="0 0 24 24"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button>

Nice! The mean response time is now 62ms, which matches the simple query protocol (63ms).

## Summary[​](https://exograph.dev/blog/wasm-pg-explorations-2#summary "Direct link to Summary")

Let's summarize the mean response times for the various configurations:

| Method ➡️ | `query` | `simple_query` | `query_with_param_types` |
| --- | --- | --- | --- |
| Pooling ⬇️ | Timing in milliseconds |
| --- | --- |
| None | 345 | 312 | **312** |
| Hyperdrive | [see above](https://exograph.dev/blog/wasm-pg-explorations-2#external-pooling) | 63 | **62** |

With connection pooling through Hyperdrive, we have brought the mean response time by a factor of 5.5 (from 345ms to 62ms)!

<svg viewbox="0 0 12 16"><path fill-rule="evenodd" d="M6.5 0C3.48 0 1 2.19 1 5c0 .92.55 2.25 1 3 1.34 2.25 1.78 2.78 2 4v1h5v-1c.22-1.22.66-1.75 2-4 .45-.75 1-2.08 1-3 0-2.81-2.48-5-5.5-5zm3.64 7.48c-.25.44-.47.8-.67 1.11-.86 1.41-1.25 2.06-1.45 3.23-.02.05-.02.11-.02.17H5c0-.06 0-.13-.02-.17-.2-1.17-.59-1.83-1.45-3.23-.2-.31-.42-.67-.67-1.11C2.44 6.78 2 5.65 2 5c0-2.2 2.02-4 4.5-4 1.22 0 2.36.42 3.22 1.19C10.55 2.94 11 3.94 11 5c0 .66-.44 1.78-.86 2.48zM4 14h5c-.23 1.14-1.3 2-2.5 2s-2.27-.86-2.5-2z"></path></svg>

> Round trip cost

> The 33ms improvement between `query` (345ms) and `query_with_param_types` (312ms) is likely to be due to saving the extra round trip for the "prepare" step but needs further investigation.

The source code is available on [GitHub](https://github.com/exograph/wasm-pg-cloudflare-explorations), so you can check this yourself. If you find any improvements or issues, please let me know.

So what should you use? Assuming that the issue with Hyperdrive and `query` method has been fixed:

- If you don't want to use Hyperdrive, use the `query_with_type_params` method with the forked driver. It does the job in one roundtrip and gives you the best performance without any risk of SQL injection attacks.
- If you want to use Hyperdrive:
  - If you frequently make the same queries, the `query` method will likely do better. Hyperdrive may cache the "prepare" part of the step, making subsequent queries faster.
  - If you make a variety of queries, you can use the `query_with_param_types` method. Since you won't execute the same query frequently, Hyperdrive's prepare statement caching is unlikely to help. Instead, this method's fewer round trips will be beneficial.

Watch Exograph's [blog](https://exograph.com/blog) for more explorations and insights as we ship its Cloudflare Worker support. You can reach us on [Twitter](https://twitter.com/exographdev) or [Discord](https://discord.gg/EPdqyvCpNw). We would appreciate a [star on GitHub](https://github.com/exograph/exograph)!