Connection pools, backups and permissions

In short: A database behind a live website needs a pool that reuses open connections, backups you can actually restore from, and a user with only the permissions it needs.

Problem

Friday night

The shop runs a promotion and 400 people arrive within five minutes. The site stops, and the log says:

FATAL:  sorry, too many clients already

The program opens a new connection to the database for every page and closes it at the end. Opening one is not free: a trip over the network, a password login and, in PostgreSQL, a new process on the server. And the server accepts a limited number of connections (max_connections, 100 by default). Four hundred pages at once are four hundred connections.

The idea

A connection pool

A pool keeps a few connections open. A page takes a free one, uses it and gives it back, and the next page takes the same one. Opening is paid once, and the database never sees more connections than the size of the pool.

Here is a small pool of three connections, written by hand so you can see what it does. borrow gives a free connection, and if there is none it opens a new one, but never more than three. giveBack returns it:

Java

static final int MAX = 3;
static final Deque<Connection> free = new ArrayDeque<>();
static int opened = 0;

static Connection borrow() throws SQLException {
    if (!free.isEmpty()) return free.pop();
    if (opened == MAX) throw new SQLException("the pool is empty");
    opened++;
    Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
    setup(conn);
    return conn;
}

static void giveBack(Connection conn) {
    free.push(conn);
}

static String productName(int id) throws SQLException {
    Connection conn = borrow();
    try (PreparedStatement ps = conn.prepareStatement("SELECT name FROM products WHERE id = ?")) {
        ps.setInt(1, id);
        try (ResultSet rs = ps.executeQuery()) {
            if (!rs.next()) return "no such product";  // the connection is never given back
            String name = rs.getString("name");
            giveBack(conn);
            return name;
        }
    }
}

for (int id : new int[] { 1, 99, 5, 98, 97, 7 }) {
    try {
        System.out.println("product " + id + ": " + productName(id));
    } catch (SQLException e) {
        System.out.println("product " + id + ": error: " + e.getMessage());
    }
}
System.out.println("open connections: " + opened + ", free: " + free.size());

C#

const int Max = 3;
var free = new Stack<SqliteConnection>();
var opened = 0;

foreach (var id in new[] { 1, 99, 5, 98, 97, 7 })
{
    try
    {
        Console.WriteLine($"product {id}: {ProductName(id)}");
    }
    catch (InvalidOperationException e)
    {
        Console.WriteLine($"product {id}: error: {e.Message}");
    }
}
Console.WriteLine($"open connections: {opened}, free: {free.Count}");

SqliteConnection Borrow()
{
    if (free.Count > 0) return free.Pop();
    if (opened == Max) throw new InvalidOperationException("the pool is empty");
    opened++;
    var conn = new SqliteConnection("Data Source=:memory:");
    conn.Open();
    Setup(conn);
    return conn;
}

void GiveBack(SqliteConnection conn) => free.Push(conn);

string ProductName(int id)
{
    var conn = Borrow();
    var cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT name FROM products WHERE id = $id";
    cmd.Parameters.AddWithValue("$id", id);
    var name = cmd.ExecuteScalar();
    if (name is null) return "no such product";  // the connection is never given back
    GiveBack(conn);
    return (string)name;
}

Python

MAX = 3
free = []
opened = 0


def borrow():
    global opened
    if free:
        return free.pop()
    if opened == MAX:
        raise RuntimeError("the pool is empty")
    opened += 1
    conn = sqlite3.connect(":memory:")
    setup(conn)
    return conn


def give_back(conn):
    free.append(conn)


def product_name(product_id):
    conn = borrow()
    row = conn.execute("SELECT name FROM products WHERE id = ?", (product_id,)).fetchone()
    if row is None:
        return "no such product"  # the connection is never given back
    give_back(conn)
    return row[0]


for product_id in [1, 99, 5, 98, 97, 7]:
    try:
        print(f"product {product_id}: {product_name(product_id)}")
    except RuntimeError as e:
        print(f"product {product_id}: error: {e}")
print(f"open connections: {opened}, free: {len(free)}")

JavaScript

const MAX = 3;
const free = [];
let opened = 0;

function borrow() {
  if (free.length > 0) return free.pop();
  if (opened === MAX) throw new Error("the pool is empty");
  opened++;
  const db = new DatabaseSync(":memory:");
  setup(db);
  return db;
}

function giveBack(db) {
  free.push(db);
}

function productName(id) {
  const db = borrow();
  const row = db.prepare("SELECT name FROM products WHERE id = ?").get(id);
  if (row === undefined) return "no such product"; // the connection is never given back
  giveBack(db);
  return row.name;
}

for (const id of [1, 99, 5, 98, 97, 7]) {
  try {
    console.log(`product ${id}: ${productName(id)}`);
  } catch (e) {
    console.log(`product ${id}: error: ${e.message}`);
  }
}
console.log(`open connections: ${opened}, free: ${free.length}`);

See it

A leaked connection

The function for a product's name has two ways out. When the product exists, it gives the connection back. When it does not, it returns early and the connection stays taken:

Java

static String productName(int id) throws SQLException {
    Connection conn = borrow();
    try (PreparedStatement ps = conn.prepareStatement("SELECT name FROM products WHERE id = ?")) {
        ps.setInt(1, id);
        try (ResultSet rs = ps.executeQuery()) {
            if (!rs.next()) return "no such product";  // the connection is never given back
            String name = rs.getString("name");
            giveBack(conn);
            return name;
        }
    }
}

Output

product 1: White bread
product 99: no such product
product 5: Fresh milk
product 98: no such product
product 97: no such product
product 7: error: the pool is empty
open connections: 3, free: 0

C#

string ProductName(int id)
{
    var conn = Borrow();
    var cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT name FROM products WHERE id = $id";
    cmd.Parameters.AddWithValue("$id", id);
    var name = cmd.ExecuteScalar();
    if (name is null) return "no such product";  // the connection is never given back
    GiveBack(conn);
    return (string)name;
}

Output

product 1: White bread
product 99: no such product
product 5: Fresh milk
product 98: no such product
product 97: no such product
product 7: error: the pool is empty
open connections: 3, free: 0

Python

def product_name(product_id):
    conn = borrow()
    row = conn.execute("SELECT name FROM products WHERE id = ?", (product_id,)).fetchone()
    if row is None:
        return "no such product"  # the connection is never given back
    give_back(conn)
    return row[0]

Output

product 1: White bread
product 99: no such product
product 5: Fresh milk
product 98: no such product
product 97: no such product
product 7: error: the pool is empty
open connections: 3, free: 0

JavaScript

function productName(id) {
  const db = borrow();
  const row = db.prepare("SELECT name FROM products WHERE id = ?").get(id);
  if (row === undefined) return "no such product"; // the connection is never given back
  giveBack(db);
  return row.name;
}

Output

product 1: White bread
product 99: no such product
product 5: Fresh milk
product 98: no such product
product 97: no such product
product 7: error: the pool is empty
open connections: 3, free: 0

Three lookups of a product that does not exist, and the pool is empty. The sixth request is perfectly normal, but it gets no connection. A real pool does not fail at once: it waits for a free connection (up to 30 seconds in HikariCP), and the page hangs. That is why a leak shows up as “the site is slow” first, and then as “the site does not respond”.

The idea

Giving it back goes in finally

The fix is to give the connection back whichever way the function leaves:

Java

static String productName(int id) throws SQLException {
    Connection conn = borrow();
    try (PreparedStatement ps = conn.prepareStatement("SELECT name FROM products WHERE id = ?")) {
        ps.setInt(1, id);
        try (ResultSet rs = ps.executeQuery()) {
            return rs.next() ? rs.getString("name") : "no such product";
        }
    } finally {
        giveBack(conn);
    }
}

Output

product 1: White bread
product 99: no such product
product 5: Fresh milk
product 98: no such product
product 97: no such product
product 7: White cheese
open connections: 1, free: 1

C#

string ProductName(int id)
{
    var conn = Borrow();
    try
    {
        var cmd = conn.CreateCommand();
        cmd.CommandText = "SELECT name FROM products WHERE id = $id";
        cmd.Parameters.AddWithValue("$id", id);
        return cmd.ExecuteScalar() as string ?? "no such product";
    }
    finally
    {
        GiveBack(conn);
    }
}

Output

product 1: White bread
product 99: no such product
product 5: Fresh milk
product 98: no such product
product 97: no such product
product 7: White cheese
open connections: 1, free: 1

Python

def product_name(product_id):
    conn = borrow()
    try:
        row = conn.execute("SELECT name FROM products WHERE id = ?", (product_id,)).fetchone()
        return row[0] if row else "no such product"
    finally:
        give_back(conn)

Output

product 1: White bread
product 99: no such product
product 5: Fresh milk
product 98: no such product
product 97: no such product
product 7: White cheese
open connections: 1, free: 1

JavaScript

function productName(id) {
  const db = borrow();
  try {
    const row = db.prepare("SELECT name FROM products WHERE id = ?").get(id);
    return row ? row.name : "no such product";
  } finally {
    giveBack(db);
  }
}

Output

product 1: White bread
product 99: no such product
product 5: Fresh milk
product 98: no such product
product 97: no such product
product 7: White cheese
open connections: 1, free: 1

Six requests with one connection: each gives it back and the next one takes it. With the real libraries you do not write a pool yourself. You take a connection from the pool and close it as in the first lesson of the module, and closing gives it back to the pool instead of really closing it:

Java

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/shop");
config.setUsername("shop_app");
config.setPassword(System.getenv("SHOP_DB_PASSWORD"));
config.setMaximumPoolSize(10);
HikariDataSource pool = new HikariDataSource(config);  // once, at startup

try (Connection conn = pool.getConnection()) {  // close() gives it back to the pool
    // the queries
}

C#

var password = Environment.GetEnvironmentVariable("SHOP_DB_PASSWORD");
var url = $"Host=localhost;Database=shop;Username=shop_app;Password={password};Maximum Pool Size=20";
await using var dataSource = NpgsqlDataSource.Create(url);  // once, at startup

await using (var conn = await dataSource.OpenConnectionAsync())  // Dispose gives it back to the pool
{
    // the queries
}

Python

password = os.environ["SHOP_DB_PASSWORD"]
engine = create_engine(  # once, at startup
    f"postgresql+psycopg://shop_app:{password}@localhost/shop",
    pool_size=5,
    max_overflow=10,
)

with engine.connect() as conn:  # the end of with gives it back to the pool
    ...  # the queries

JavaScript

const pool = new pg.Pool({  // once, at startup
  host: "localhost",
  database: "shop",
  user: "shop_app",
  password: process.env.SHOP_DB_PASSWORD,
  max: 10,
});

const client = await pool.connect();
try {
  // the queries
} finally {
  client.release();  // gives it back to the pool
}

More precisely, if you are curious

These examples are not run by the site's check, because they need PostgreSQL and the pool packages. The default sizes are: HikariCP 10, Npgsql 100, SQLAlchemy 5 (and 10 more when needed), pg 10.

A bigger pool does not mean a faster site. The database has as many cores as it has, and 200 queries at once just wait for each other. The HikariCP guide suggests starting at about twice the number of cores of the database server. If the site runs on five servers with 20 connections each, the database sees 100. When there are too many connections, a separate pool such as PgBouncer goes in front of PostgreSQL.

The idea

A backup

Some day the database will be damaged: a disk, a wrong query, a migration with a bug. A copy is the only way back. In SQLite VACUUM INTO writes the whole database to a new file while it is running, and ATTACH opens that file as a second database on the same connection:

SQLdatabase: shop

VACUUM INTO '/backup-2026-10-25-0300.db';
ATTACH '/backup-2026-10-25-0300.db' AS backup;

SELECT
  (SELECT COUNT(*) FROM orders) AS orders_now,
  (SELECT COUNT(*) FROM backup.orders) AS orders_in_copy;

VACUUM INTO '/backup-2026-10-25-0300.db'; ATTACH '/backup-2026-10-25-0300.db' AS backup; SELECT (SELECT COUNT(*) FROM orders) AS orders_now, (SELECT COUNT(*) FROM backup.orders) AS orders_in_copy;

▶ RunRestore the queryFresh database

Here is why the copy is worth it. A script deletes the coffee reviews by mistake, and the copy from a minute ago brings them back:

SQLdatabase: shop

VACUUM INTO '/backup-2026-10-25-1415.db';
DELETE FROM reviews WHERE product_id = 17;

ATTACH '/backup-2026-10-25-1415.db' AS backup;
INSERT INTO reviews
SELECT * FROM backup.reviews WHERE product_id = 17;

SELECT id, product_id, rating FROM reviews WHERE product_id = 17;

VACUUM INTO '/backup-2026-10-25-1415.db'; DELETE FROM reviews WHERE product_id = 17; ATTACH '/backup-2026-10-25-1415.db' AS backup; INSERT INTO reviews SELECT * FROM backup.reviews WHERE product_id = 17; SELECT id, product_id, rating FROM reviews WHERE product_id = 17;

▶ RunRestore the queryFresh database

Run the box a second time and VACUUM INTO stops, because the file already exists. It is the same in real life: every copy is a new file with the date and time in its name.

More precisely, if you are curious

In PostgreSQL pg_dump makes a copy of one database, and pg_basebackup with a WAL archive lets you restore to an exact moment (“to 14:14, before the script”). In MySQL it is mysqldump. A common rule is 3-2-1: three copies, on two kinds of media, one of them somewhere else.

Try it

Checking after a restore

A copy you have never restored from is only a hope. After every restore (at least to a test database) you check that the database is whole:

SQLdatabase: shop

PRAGMA integrity_check;

PRAGMA integrity_check;

▶ RunRestore the queryFresh database

and how many rows each table has, to compare with the numbers before the copy.

For each of the shop's six tables show its name (column table_name) and its number of rows (column row_count).

SQLdatabase: shop

SELECT 'customers' AS table_name, COUNT(*) AS row_count FROM customers;

SELECT 'customers' AS table_name, COUNT(*) AS row_count FROM customers;

Check▶ Run

Exercise

Bring back what was deleted, without losing what is new

Last night's copy has been restored into the table reviews_2026_10_24. This morning a script deleted reviews from reviews, and then a customer wrote a new review. Bring the deleted reviews back from the copy without losing the new one.

SQLdatabase: shop

SELECT * FROM reviews_2026_10_24 WHERE id NOT IN (SELECT id FROM reviews);

SELECT * FROM reviews_2026_10_24 WHERE id NOT IN (SELECT id FROM reviews);

Check▶ Run

The idea

As many privileges as it needs

SQLite has no users: whoever can open the file can do everything. Server databases do, and the site logs in with its own user that can do only what it needs:

CREATE ROLE shop_app LOGIN PASSWORD '…';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO shop_app;

No DROP, no ALTER and no access to the other databases on the server. If someone breaks into the site, they can do as much as shop_app. The migrations run with a different user that owns the tables, and both passwords stay out of the code, as in the first lesson of the module.

Test

Check yourself

1. The site opens a new PostgreSQL connection for every page. What happens during a rush?

The database makes a pool by itselfEvery page pays for opening one, and beyond max_connections the database refuses new connectionsNothing, the database is fast

2. The pool has 10 connections. A function returns early before giving its connection back. What happens?

Nothing, if the query was a SELECTEvery such path loses one connection, until the pool is emptyThe pool takes it back by itself after the query

3. When do you know a backup works?

When the file is bigger than zeroWhen you have restored the database from it and the data is thereWhen the backup command gave no error

4. Which privileges does the user the site logs in with need?

Only SELECTSELECT, INSERT, UPDATE and DELETE on its tables, without DROP and without changing the schemaAll of them, so that no query fails

Check

Start the lesson over

Related lessons

• Builds on: DELETE: deleting rows · Connecting to the database from code · SQL injection and parameters · Schema migrations
• Tasks on this topic: The database in a program