Database Connection Pooling with pool

Three users, one shared database connection, and one slow query is enough to freeze a Shiny app. The whole session blocks while every other user waits for the connection to come back. Outside Shiny, paying the cost of DBI::dbConnect() on every iteration of a batch script wastes tens of milliseconds per call, and every reconnect spends another round trip on TCP setup, TLS, and auth.

The pool package removes both pain points. It keeps a set of DBI connections open, hands them out on demand, and recycles them. In Shiny, multiple users hit the database in parallel up to the configured cap. In batch scripts, you stop paying reconnect costs on every iteration. This guide walks through dbPool(), the DBI methods that work on a pool, transactional helpers, the Shiny pattern, and the mistakes that bite in production.

TL;DR

• A pool keeps N DBI connections open and lends them out concurrently, which is what Shiny needs to serve more than one user at a time.
• pool::dbPool(driver, ...) is the constructor most people need. It forwards the ... arguments straight to DBI::dbConnect().
• A pool is a DBI connection. Pass it to dbGetQuery, dbWriteTable, dbplyr::tbl(), dplyr::copy_to().
• Use poolWithTransaction() for multi-statement work. Do not call dbBegin() / dbCommit() against a pool by hand.
• Always close the pool. In Shiny, hook into onStop(function() pool::poolClose(pool)).
• Set an explicit maxSize. The default Inf works for SQLite but can exhaust a Postgres server under load.

How do you create a database connection pool with dbPool?

The workhorse is pool::dbPool(). It takes a DBI driver and the same connection arguments you would pass to DBI::dbConnect():

library(pool)
library(RSQLite)

pool <- dbPool(
  drv    = RSQLite::SQLite(),
  dbname = ":memory:",
  minSize = 1,
  maxSize = 5
)

The driver and any ... arguments are forwarded to dbConnect(), so any DBI backend works: RPostgres::Postgres(), RMariaDB::MariaDB(), odbc::odbc(), duckdb::duckdb(). The remaining arguments control the pool itself.

Argument	Default	What it does
minSize	1	Connections kept warm at all times
maxSize	Inf	Hard cap on simultaneous checked-out connections
onCreate	NULL	One-argument function called on every brand-new connection
idleTimeout	60	Seconds an idle connection above minSize stays open
validationInterval	60	Seconds between background health checks on free connections
validateQuery	NULL	Probe query used during validation; pool picks a default if NULL

Two defaults deserve attention. maxSize = Inf is fine for SQLite or DuckDB but dangerous against a real Postgres server with a max_connections ceiling, because a runaway Shiny app can exhaust it. Set an explicit cap. A good rule of thumb: number of concurrent R workers times 2, bounded by what the server allows.

onCreate is the cleanest place for per-connection session setup. Run it once on each brand-new connection, not on every checkout. Below is a Postgres pool that sets search_path for every connection it hands out, which keeps your application code free of schema prefixes:

pool <- dbPool(
  drv      = RPostgres::Postgres(),
  host     = "db.internal",
  dbname   = "analytics",
  user     = Sys.getenv("DB_USER"),
  password = Sys.getenv("DB_PASSWORD"),
  minSize  = 2,
  maxSize  = 10,
  idleTimeout = 300,
  onCreate = function(conn) {
    DBI::dbExecute(conn, "SET search_path TO app, public;")
  }
)

Typical uses: SET search_path on Postgres, PRAGMA foreign_keys = ON on SQLite, SET TIME ZONE 'UTC' on warehouses that default to local time. Do not use it for one-time schema setup. That belongs in a migration script, not a callback that runs on every new connection.

How do you use a pool like a DBI connection?

A Pool object inherits the DBI connection methods. You can pass it straight to the functions you already know. This is the SQLite pool from earlier, used to load mtcars and aggregate it with a single SQL query:

DBI::dbWriteTable(pool, "mtcars", mtcars)

DBI::dbGetQuery(pool, "
  SELECT cyl, AVG(mpg) AS avg_mpg
  FROM mtcars
  GROUP BY cyl
")
#>   cyl avg_mpg
#> 1   4   26.66
#> 2   6   19.74
#> 3   8   15.10

dbExecute, dbWriteTable, dbReadTable, dbExistsTable, and dbListTables all dispatch the same way. So does dbplyr, and you do not need to register anything special for it to work because the pool satisfies the DBI connection contract:

library(dplyr)
library(dbplyr)

tbl(pool, "mtcars") |>
  filter(cyl == 4) |>
  summarise(n = n(), mpg = mean(mpg))

dbplyr collects results on a checked-out connection with no extra wiring. dplyr::copy_to(pool, df, "name") works the same way.

One DBI feature does not work directly on a pool: streamed dbSendQuery() / dbFetch() chunks. For those, call pool::poolCheckout(pool) to get a raw connection, do the streaming, then pool::poolReturn(conn) to give it back. Skip the poolReturn() and the connection leaks. This is the most common pool bug, and it almost always traces back to a missed poolReturn() after a poolCheckout() in an error path.

How do you run transactions against a pool?

If you need a transaction, use pool::poolWithTransaction(). It checks out a connection, calls dbBegin() before your function, dbCommit() on success, and dbRollback() on error. The example below inserts an order and decrements inventory in a single atomic write; either both rows land or neither does:

poolWithTransaction(pool, function(conn) {
  DBI::dbExecute(
    conn,
    "INSERT INTO orders (user_id, total) VALUES ($1, $2)",
    params = list(42, 99.95)
  )
  DBI::dbExecute(
    conn,
    "UPDATE inventory SET stock = stock - 1 WHERE sku = $1",
    params = list("ABC-001")
  )
})
# Both rows land, or neither does. Conn auto-returns.

Two things are worth noticing. First, the connection always returns to the pool, even when the function throws, so there is no leak risk. Second, the body of func may call DBI::dbBreak() to silently roll back and exit early. That is useful when you have detected a condition that should abort the write without raising a user-facing error. Side effects inside func (assignments to globals, writes to files) do persist after return. Only the database transaction is rolled back.

Do not manage transactions by hand with dbBegin() / dbCommit() against a pool. If anything throws between them, the connection stays checked out and nothing in your code releases it. The transaction wrapper exists exactly to prevent that class of bug. The official pool documentation calls this out as the first thing to check when chasing connection leaks.

How do you use a connection pool in Shiny apps?

A pool defined once at the top of app.R is shared across all user sessions. The pattern below creates a SQLite pool, hooks cleanup into onStop(), and renders the first ten rows of a users table:

library(shiny)
library(pool)
library(RSQLite)

pool <- dbPool(
  drv    = RSQLite::SQLite(),
  dbname = "app.db",
  minSize = 1,
  maxSize = 5
)

# Critical: release the pool when the app stops
onStop(function() pool::poolClose(pool))

ui <- fluidPage(tableOutput("rows"))

server <- function(input, output, session) {
  output$rows <- renderTable({
    dbGetQuery(pool, "SELECT id, name FROM users LIMIT 10")
  })
}

shinyApp(ui, server)

Two things matter. First, onStop() runs when the app exits, in interactive use or under shiny::runApp(). Without it, the pool (and every connection inside it) survives the R process. Second, define the pool once, at the top level of the script (or in global.R for a multi-file app). Do not put dbPool() inside reactive() or observe(). That recreates the connection set on every reactive flush and undoes the point of pooling.

The same pattern works in Plumber APIs: create the pool at the top of the file, close it in onStop(). See Building a Plumber REST API for the surrounding plumbing.

What are the most common pool mistakes?

Calling dbDisconnect() on a pool. It disconnects the currently checked-out connection inside the pool, not the pool itself, leaving you in an inconsistent state. The right call is pool::poolClose(pool), exactly once, when you are done.

Forgetting to close the pool. In a short script, the pool lives as long as the R process, which is usually fine. In a long-running daemon or an RStudio background job it can quietly hold server-side slots. Use on.exit(poolClose(pool), add = TRUE) right after creating the pool, or call it explicitly at the end of your entry point:

pool <- dbPool(RSQLite::SQLite(), dbname = "app.db")
on.exit(pool::poolClose(pool), add = TRUE)

Defaulting to maxSize = Inf. Fine for in-process engines (SQLite, DuckDB), risky for Postgres. A Shiny app that spawns many concurrent requests will fan out to that many server-side connections, and Postgres has its own ceiling. Pick a number that matches your server’s max_connections minus headroom for other clients.

idleTimeout shorter than the server’s wait_timeout. If the database server drops idle TCP connections after 60 seconds and your idleTimeout is also 60, you can hit “server has gone away” errors under low traffic. The default 60s is a sensible modern value, but match it to your server config, or set a longer idleTimeout and let the pool’s validationInterval detect and replace dead connections.

Pools inside reactive contexts. Defining dbPool() inside server() or inside an observe() recreates connections on every flush. Always create the pool outside reactive code, in global.R for Shiny, or at the top of a script.

Implicit library(pool). The DBI methods on a pool dispatch without an explicit library(pool) call, but poolClose() is namespaced. In production code, write pool::poolClose() so it works regardless of what has been loaded. The same logic applies to poolWithTransaction() and poolCheckout().

Sharing pools across processes. R’s future::multisession and furrr workers each run as separate R processes. Connections are not shared across processes, so each worker needs its own pool. See Parallel Computing with furrr for the pattern.

How do you check a pool’s state?

For debugging a stuck or slow app, a pool’s diagnostic info is available through the S4 class. format(pool) gives a quick read; the underlying dbGetInfo() is useful when you are hunting a leak. Both numberFreeObjects and numberTakenObjects are shown below; the sum of the two should stay constant at maxSize once the pool is warm:

pool:::dbGetInfo(pool)
#> $class               "Pool"
#> $valid               TRUE
#> $minSize             1
#> $maxSize             5
#> $idleTimeout         60
#> $pooledObjectClass   "SQLiteConnection"
#> $numberFreeObjects   3
#> $numberTakenObjects  2

The two numbers that matter most are $numberTakenObjects and $numberFreeObjects. If $numberTakenObjects keeps climbing and never returns to its baseline, a poolReturn() is missing somewhere, almost always a poolCheckout() that was not paired with the return on an error path. The export status of this helper has shifted between pool versions; treat it as a diagnostic-only call rather than something to commit to a stable namespace.

When should you use pool over plain DBI?

Concern	DBI::dbConnect()	pool::dbPool()
Connect cost per query	paid every time	paid once
Concurrent Shiny users	serialised, blocking	parallel, up to maxSize
Stale connection recovery	manual	automatic revalidation
Cleanup	one dbDisconnect() per conn	one poolClose() at end

For one-off scripts and analyses, plain dbConnect() / dbDisconnect() is simpler. For Shiny apps, Plumber APIs, and any R process that holds a database connection open across more than a single query, pooling is the right tool. The complexity tax is small (one extra function call, one onStop() hook) and the pay-off shows up the first time a second user hits your app at the same time as the first.

Frequently asked questions

What happens when a pool runs out of connections? Checkout calls block until a connection is returned, or throw immediately if you set a timeout. With the default maxSize = Inf, checkouts never block on the pool itself, but the database server may still refuse new connections once its own limit is hit.

Should I close the pool at the end of every script? Yes, with on.exit(pool::poolClose(pool), add = TRUE). For Shiny, use onStop(function() pool::poolClose(pool)). The pool will not close itself when the R process ends, and orphan connections waste server-side slots.

Can I use pool with a DBI backend that does not support streaming? Yes. dbGetQuery, dbExecute, dbWriteTable, and dbplyr all work. Only dbSendQuery() / dbFetch() (the streaming pattern) requires poolCheckout() first. The on.exit() hook is the safe way to guarantee the connection returns even when an error fires mid-stream:

conn <- pool::poolCheckout(pool)
on.exit(pool::poolReturn(conn), add = TRUE)

rs <- DBI::dbSendQuery(conn, "SELECT * FROM big_table")
on.exit(DBI::dbClearResult(rs), add = TRUE)

repeat {
  chunk <- DBI::dbFetch(rs, n = 1000)
  if (nrow(chunk) == 0) break
  # process chunk ...
}

Do I need library(pool) for the DBI methods to work? No, they dispatch through S4 and S3 generics on the pool object. But poolClose(), poolWithTransaction(), and poolCheckout() are namespaced, so call them as pool::poolClose() in production code.

How do I set per-connection session variables? Pass an onCreate function to dbPool(). It runs once on every brand-new connection, which makes it the right place for SET search_path, PRAGMA foreign_keys = ON, or any other per-connection state.

See also

• Database Interface with R’s DBI Package: the DBI foundation that pool builds on
• SQLite R: Database Operations with DBI and RSQLite: the simplest local backend to pool against
• Building a Plumber REST API: the same pooling pattern for API endpoints