Handling Database Timeouts and Retries in Rust Services
Set acquire, connect and statement timeouts, tell transient errors from permanent ones, retry only what is safe with backoff and jitter, and keep a retry from doubling a write. SQLx and PostgreSQL examples.
A database call that never returns is worse than one that fails: it holds a connection, a request task and a user's patience. Timeouts convert hangs into errors, and retries convert some of those errors back into successes. Done carelessly, retries amplify an outage. This post sets the timeouts and a retry policy with SQLx. It compiles on Rust 1.90 with edition 2024, SQLx 0.8, Tokio 1.47 and PostgreSQL 17.
The timeouts
use sqlx::postgres::{PgConnectOptions, PgPoolOptions};
use std::{str::FromStr, time::Duration};
pub async fn pool(url: &str) -> Result<sqlx::PgPool, sqlx::Error> {
let opts = PgConnectOptions::from_str(url)?
.options([("statement_timeout", "5000"), ("idle_in_transaction_session_timeout", "10000")]);
PgPoolOptions::new()
.max_connections(20)
.acquire_timeout(Duration::from_secs(3)) // waiting for a free pooled connection
.idle_timeout(Duration::from_secs(300))
.max_lifetime(Duration::from_secs(1800))
.connect_with(opts)
.await
}| Timeout | What it bounds | Failure you see |
|---|---|---|
acquire_timeout | Waiting for a connection from the pool | sqlx::Error::PoolTimedOut: the pool is exhausted or the database is slow |
| Connect timeout | Opening a new TCP and TLS connection | I/O error or PoolTimedOut |
statement_timeout | One query's execution, enforced by PostgreSQL | SQLSTATE 57014 query canceled |
idle_in_transaction_session_timeout | A transaction left open doing nothing | Connection terminated by the server |
A pool timeout is the signal to investigate pool size and slow queries rather than to raise the number (pool sizing). For a single slow endpoint that legitimately needs longer, set SET LOCAL statement_timeout inside its transaction.
Classifying errors
use sqlx::Error;
pub fn is_transient(e: &Error) -> bool {
match e {
Error::PoolTimedOut | Error::Io(_) | Error::Tls(_) | Error::PoolClosed => true,
Error::Database(db) => matches!(
db.code().as_deref(),
Some("40001") // serialization_failure
| Some("40P01") // deadlock_detected
| Some("57P01") // admin_shutdown (failover)
| Some("53300") // too_many_connections
),
_ => false,
}
}Retryable: the operation may succeed if repeated unchanged. Not retryable: a unique violation (23505), a foreign key violation, a syntax error, a type mismatch, RowNotFound and 57014 query canceled by statement timeout, which will probably time out again and add load. Treat anything unknown as permanent; retrying a bug is wasted work.
Retry with backoff and jitter
use std::{future::Future, time::Duration};
pub async fn with_retry<T, F, Fut>(mut op: F, max_attempts: u32) -> Result<T, sqlx::Error>
where
F: FnMut() -> Fut,
Fut: Future<Output = Result<T, sqlx::Error>>,
{
let mut attempt = 0;
loop {
attempt += 1;
match op().await {
Ok(v) => return Ok(v),
Err(e) if attempt < max_attempts && is_transient(&e) => {
let base = 50u64 * 2u64.pow(attempt - 1); // 50, 100, 200 ms
let jitter = rand::random::<u64>() % base; // 0..base
tokio::time::sleep(Duration::from_millis(base + jitter)).await;
}
Err(e) => return Err(e),
}
}
}Exponential backoff spreads retries over time; jitter keeps a thousand clients that failed together from retrying together. Cap attempts and also cap total elapsed time against the request's own deadline: a request with a 2-second budget should not retry for 10 seconds. A circuit breaker around the database, or load shedding, protects it when it is clearly down (circuit breakers).
Retry the whole transaction
use sqlx::PgPool;
async fn transfer(pool: &PgPool, from: i64, to: i64, cents: i64) -> Result<(), sqlx::Error> {
with_retry(
|| async {
let mut tx = pool.begin().await?;
sqlx::query("UPDATE accounts SET balance = balance - $1 WHERE id = $2")
.bind(cents).bind(from).execute(&mut *tx).await?;
sqlx::query("UPDATE accounts SET balance = balance + $1 WHERE id = $2")
.bind(cents).bind(to).execute(&mut *tx).await?;
tx.commit().await
},
3,
)
.await
}After a serialization failure or deadlock the transaction is aborted; the correct retry re-runs everything from begin, including any reads that fed decisions. Retrying only the failed statement inside the same transaction does not work, since the transaction is already in an error state. Using a stricter isolation level makes 40001 more frequent and retry logic mandatory (SQLx transactions).
The duplicate-write trap
If a commit succeeds but the network drops before the acknowledgment reaches you, the error looks transient and the retry runs the write again. Make writes safe to repeat: a unique constraint on a client-supplied idempotency key, an INSERT ... ON CONFLICT DO NOTHING, or an update expressed as "set to X" rather than "add 1". See idempotency keys, and surface the timeout to users clearly when retries are exhausted: it is the error they will actually see.
