A Slow Download Can Keep Your Rust Database Pool Busy

Article · Through the layers

Markdown version · More Rust database questions

An export starts quickly. Then the person downloading it loses most of their network speed. Other requests begin waiting for a database connection.

Suppose the export reads one row from PostgreSQL, sends it over HTTP, then reads the next row. That avoids collecting the whole result in a large vector. But the row stream still owns its database connection while the download is slow.

One slow download can keep one database connection busy. Enough of them can occupy the pool.

The fix depends on what the export promises. Small pages can return the connection between reads. A single consistent snapshot may require keeping one transaction. We will look at both the resource problem and that tradeoff, with a real HTTP test as well as a smaller database example.

The connection is borrowed for longer than the query timer suggests

A pool shares a limited number of database connections between requests. A connection is checked out while one request borrows it. The pool can lend it to another request after it is returned. PostgreSQL being idle does not make that connection free for another Rust task.

An async wait allows other tasks to run. It does not release the values held by the waiting task. That includes a SQLx connection borrowed by a row stream.

Start by recording acquisition time, query progress, and the whole checkout lifetime separately. The pool-wait investigation shows how to separate a pool wait from a PostgreSQL lock wait.

A small example makes ownership visible

The first PostgreSQL example uses a one-slot pool. It reads one row, signals the observer, then waits on a channel:

async fn consume(
    pool: PgPool,
    first: oneshot::Sender<()>,
    resume: oneshot::Receiver<()>,
    seen: Arc<AtomicUsize>,
) -> Result<()> {
    let mut connection = pool.acquire().await?;
    let mut rows =
        sqlx::query("SELECT i, repeat('x', 1024) AS payload FROM generate_series(1, 32) AS i")
            .fetch(&mut *connection);
    let row = rows.try_next().await?.ok_or("missing first row")?;
    assert_eq!(row.try_get::<i32, _>("i")?, 1);
    assert_eq!(row.try_get::<String, _>("payload")?.len(), 1024);
    seen.fetch_add(1, Ordering::SeqCst);
    first.send(()).map_err(|_| "observer left")?;
    resume.await?;
    while let Some(row) = rows.try_next().await? {
        assert_eq!(row.try_get::<String, _>("payload")?.len(), 1024);
        seen.fetch_add(1, Ordering::SeqCst);
    }
    Ok(())
}

The observer tries to acquire the only connection while that wait is still in place. It cannot. Releasing or cancelling the consumer lets the next borrower acquire and run a query.

The channel gives us a precise pause. It does not tell us what an HTTP server or socket will buffer. PostgreSQL might already have sent the small result into driver buffers. This example isolates ownership; it does not measure network pressure or process memory.

Follow the rows through a real HTTP response

The HTTP example adds an Axum server and a real loopback TCP client. It uses SQLx 0.8.6, Axum 0.8.9, and Tokio 1.48.0 against the existing PostgreSQL CI service.

PostgreSQL generates 512 fixed text records. Each is 4,096 bytes, with a row number at the front and a newline at the end. These are controlled test records, not customer data. Their fixed size lets the test check order, contents, and a useful payload bound.

The client reads the first record and stops reading. The server's socket wrapper records when an actual write returns Pending: the write cannot proceed yet. The test waits for that observation before checking the pool.

Small socket buffers make this condition practical to reproduce. Their sizes are test settings, not production tuning advice. There is no sleep used to guess when a download became slow.

Two producers face the same paused client. The first fetches a SQLx row stream and sends through a one-message channel. It is already in a background task, but still owns the checkout. The second fetches small pages and returns each checkout before waiting to send.

The tests check three paths:

A pool probe after the client resumes would answer a weaker question. Here, the client remains paused until the probe has its result. The checks also require the producer to be unfinished, so an already completed export cannot accidentally make the repair look successful.

In the recorded HTTP execution, all three cases passed. The retained stream kept the pool occupied. The paged version allowed SELECT 7 while the client was paused, then delivered all 512 records when reading resumed. Both disconnect cases joined their producers, returned the tracked payload budget, and left the pool able to run SELECT 99. These are controlled observations, not throughput measurements.

Return the connection before waiting to send

The page producer reserves space before fetching. Each page has at most four of the fixed records:

let mut reservation = budget
    .clone()
    .acquire_many_owned(PAGE_BYTES as u32)
    .await
    .unwrap();
let page: Vec<(i32, String)> = sqlx::query_as(
    "SELECT i, lpad(i::text, 8, '0') || repeat('x', 4087) || chr(10) \
     FROM generate_series($1::int, $2::int) AS i",
)
.bind(first)
.bind((first + PAGE_ROWS - 1).min(ROWS))
.fetch_all(&pool)
.await?;

Passing the pool directly to fetch_all scopes the checkout to that fetch. The page owns its rows when the await returns. Sending them to the slow response does not need that database connection.

There are two separate limits here. The channel has room for one message. A shared byte allowance covers up to 32,768 bytes of these row bodies across pages and HTTP payload owners. Waiting for that allowance happens before the next database read.

Each row takes its share of the reservation. The full source uses Bytes::from_owner to keep that share with the buffer, including clones held by the HTTP stack. The reservation returns after the last owner releases the data. The Bytes documentation explains that ownership rule.

Returning the reservation immediately after send would be too early: the receiver or HTTP stack could still own the bytes. The producer-memory investigation follows that same mistake outside the queue.

Know what the byte limit covers

The example can reserve before a read because every generated body has a known maximum. A limit of four arbitrary text rows would not provide that guarantee.

A real export needs a justified row-size or chunk-size bound. A CSV or JSON encoder may also make the output larger than its input. Include the space that can hold both at once.

The test counts the owned HTTP payload it creates. It also checks that all byte permits return after completion or disconnect. This is not a cap on total process memory. SQLx protocol buffers, allocation overhead, task state, and kernel socket buffers are outside that counter. A proxy would add more buffering again.

Keep those observations separate. A bounded queue, a byte reservation, and process memory usage each tell you something different.

A disconnect needs to reach the producer

Moving database work into a task does not make its lifetime match the response automatically.

In this example, the response body owns a small guard. When the body is dropped, the guard requests cancellation of the producer:

impl Drop for StopProducer {
    fn drop(&mut self) {
        self.producer.abort();
        self.observations.body_dropped.store(true, Ordering::SeqCst);
        self.observations.body_changed.notify_one();
    }
}

Requesting cancellation is only the start. The test waits for the response to drop and joins the producer. It then waits for the owned payloads to disappear, checks that every byte permit returned, and runs SELECT 99 through the pool.

Those checks establish that this path recovered. They do not claim that aborting any Rust task instantly cancels a PostgreSQL statement. Database work already sent to the server has a separate lifetime, as the timeout investigation shows.

In a real service, follow the same chain through middleware and proxies. Check whether a downstream disconnect drops the upstream body, whether producer tasks remain running, and how long database cleanup takes.

Pages change the consistency question

The test generates fixed records, so nothing edits them between pages. That makes it useful for testing resource lifetime. It does not prove that paging through a changing customer table produces one consistent export.

If each page uses a new transaction, later pages can see later commits. Use a stable key for pagination and decide how edits and deletes should appear. A stable cursor alone does not create a snapshot of changing row contents.

If the export must reflect one point in time, a longer snapshot may be the right choice. Give that work a separate connection budget and a time limit. Another option is to create an export file in a bounded background job, then download that file without holding a database connection. Both choices need cleanup and cancellation rules.

The PostgreSQL transaction guide explains which operations must stay together. Release a connection early when the data promise allows it, and make the cost explicit when it does not.

For a slow export, the useful question is which resources it still owns while nothing moves. Follow the connection, the row data, and the response separately. Then check that the chosen repair gives another request room to progress and still produces the export you promised.