Lesson 1 of 2Intermediate⏱ 15 mins

Connection Pools and State

Sharing database connections.

0% of path

Learning Objectives

Initialize a PgPool

##Why you never open a connection per request

Connecting to a database is slow: it involves a network handshake, authentication and setup. If every request opened its own connection, your server would spend more time connecting than working, and the database would quickly run out of connection slots.

A connection pool solves this. It opens a handful of connections once, keeps them alive and lends them to requests. When a request is done, the connection goes back to the pool for the next one.

#The pool lifecycle

At startup the pool opens its minimum number of connections.
A handler asks for a connection. If one is idle, it gets it instantly.
If all are busy, the request waits (up to a timeout) until one is returned.
The pool can grow up to a configured maximum.

#Creating a pool with SQLx

code_blockstoml

[dependencies]
actix-web = "4"
sqlx = { version = "0.8", features = ["runtime-tokio", "postgres"] }
tokio = { version = "1", features = ["full"] }

code_blocksrust

use actix_web::{web, App, HttpServer};
use sqlx::postgres::PgPoolOptions;

#[actix_web::main]
async fn main() -> std::io::Result<()> {
    let database_url = std::env::var("DATABASE_URL").expect("DATABASE_URL must be set");

    let pool = PgPoolOptions::new()
        .max_connections(10)
        .acquire_timeout(std::time::Duration::from_secs(3))
        .connect(&database_url)
        .await
        .expect("failed to connect to the database");

    let pool = web::Data::new(pool);

    HttpServer::new(move || App::new().app_data(pool.clone()))
        .bind(("127.0.0.1", 8080))?
        .run()
        .await
}

PgPool is already thread safe and cheap to clone, so you do not wrap it in a Mutex. web::Data shares the same pool with every worker.

#Using the pool in a handler

code_blocksrust

use actix_web::{get, web, HttpResponse};
use sqlx::PgPool;

#[get("/db-check")]
async fn db_check(pool: web::Data<PgPool>) -> HttpResponse {
    match sqlx::query("SELECT 1").execute(pool.get_ref()).await {
        Ok(_) => HttpResponse::Ok().body("database is up"),
        Err(_) => HttpResponse::ServiceUnavailable().body("database is down"),
    }
}

Passing pool.get_ref() gives SQLx a &PgPool. It borrows a connection for the duration of the query and returns it automatically.

#Sizing the pool

Too small and requests queue up waiting for a connection.
Too large and you overwhelm the database, which has its own connection limit.
A good starting point is 5 to 20 connections per application instance. Remember that instances multiply: ten servers with a pool of 20 means 200 connections.

#Common settings

max_connections: the upper limit.
min_connections: connections kept warm.
acquire_timeout: how long a request waits for a free connection before failing.
idle_timeout: close connections unused for a while.

"What If?" Experiment

Learning by doing. Modify the code to see what happens.

Goal

See what happens when the pool is exhausted.

Starting Code

.max_connections(1).acquire_timeout(Duration::from_secs(1))

Suggested Modification

Run two slow queries at once with a pool of one connection.

Expected Result

The second request waits, then fails with a pool timed out error.

Explanation: With one connection the second request has to queue, and the acquire timeout turns the wait into an error.

Common Mistakes

Anticipate and avoid these frequent pitfalls when working with this concept.

Connecting inside the handler

It looks simple and works for one request at a time.

Incorrect Pattern

let conn = PgConnection::connect(&url).await?; // inside every request

Correct Pattern

pool: web::Data<PgPool> // shared pool from app_data

Explanation: Per-request connections are slow and will exhaust the database under load. Share one pool.

Pool size larger than the database allows

Multiple instances each opening many connections add up quickly.

Incorrect Pattern

.max_connections(200) // on each of 10 instances

Correct Pattern

.max_connections(10)

Explanation: Postgres usually allows about 100 connections. Size pools so total connections stay under that limit.

Practical Exercise

Add a database health check

Task

Create GET /health/db that runs SELECT 1 and returns 200 or 503.

Requirements

Take web::Data<PgPool> in the handler
Return 200 when the query succeeds
Return 503 when it fails

Need a hint?

Try solving the exercise in your local environment first. Once you have a solution, or if you get completely stuck, reveal the answer.

Real-World Application

Every production service runs a pool, usually with the size configurable through an environment variable, and exposes a database health check for load balancers and Kubernetes probes.

Key Takeaways

Create one pool at startup and share it with web::Data.
PgPool is thread safe and cheap to clone; do not wrap it in a Mutex.
Tune max_connections and acquire_timeout to protect the database.
Remember that every app instance has its own pool.

Finished reading?

Mark this lesson as done to track your progress and earn XP.

Next Lesson

2. Async Database Queries with SQLx