Connections, Migrations, and Backups

Build the mental model

Opening a new database connection is not free — each one costs memory and setup time, and a busy application can easily try to open more connections than the database can handle at once.

Connection pooling shares a limited, reusable set of connections across many requests. This matters even more for serverless architectures, where many short-lived function instances could otherwise each open their own connection at once.

1. Create

Write the migration describing the schema change.

2. Review

Have someone else check it before it runs anywhere important.

3. Backup

Take a safety-net backup before applying, in case something goes wrong.

4. Apply

Run the migration against the target database.

5. Verify

Confirm the schema and the application both behave as expected.

A backup is not enough

A backup that has never been restored is only a hypothesis, not a guarantee. The only way to know a backup actually works is to practice restoring from it — before you need it in an emergency.

Replication supports availability, read scaling, and disaster recovery, but it is not a substitute for backups — a mistake replicates too. Scaling more broadly includes vertical scaling, read replicas, caching, and partitioning or sharding.

text

CONNECTION POOL AND MIGRATION FLOW
----------------------------------
MANY REQUESTS          CONNECTION POOL         DATABASE
   req 1  ---\
   req 2  ----\      +-----------------+
   req 3  -----+----> | limited, reused |------> [ DB ]
   req 4  ----/       | connections     |
   req 5  ---/        +-----------------+
   (some requests wait briefly for a free connection)

MIGRATION FLOW
--------------
  Create -> Review -> Backup -> Apply -> Verify
  (never hand-edit schema directly against production)

Connect it to a real scenario

Connection pooling is usually handled by a pooler library or managed proxy, not something you build yourself. The discipline is setting the pool size sensibly and understanding what happens when it fills up.

Never skip the review or backup step because a migration looks small — small migrations are exactly the ones people stop being careful about. Seed data should stay in its own labeled script, away from real user records.

For hands-on migration and pooling configuration: PostgreSQL's safe-schema-migrations and connections-pooling-and-security lessons.
For hands-on replication and high availability: Redis's replication and sentinel-high-availability lessons.

Connection pooling shares a limited set of reusable connections across many requests, preventing an unbounded flood from overwhelming the database.
Migrations should be created, reviewed, backed up against, applied, and verified — never hand-edited directly on production.
A backup is only a hypothesis until restore is actually tested; replication supports availability but does not replace backups.

A backup is not enough — restore must be tested

A backup you have never restored from is an untested assumption, not a safety net. Schedule real restore drills, not just backup jobs.

Try the working example

javascript

// Simplified simulation of a connection pool: only `poolSize` requests can
// hold a connection at once. Everyone else has to wait for one to free up.
function simulateConnectionPool(poolSize, requestCount) {
  const immediate = Math.min(poolSize, requestCount);
  const waiting = Math.max(0, requestCount - poolSize);
  return { poolSize, requestCount, immediate, waiting };
}

console.log(simulateConnectionPool(10, 8));
console.log(simulateConnectionPool(10, 45));

You should see

With a pool size of 10 and 8 incoming requests, all 8 get a connection immediately and 0 wait. With the same pool size of 10 but 45 requests, 10 get a connection immediately and 35 have to wait — demonstrating exactly why an application that opens one connection per request without a limit can quickly overwhelm the database once traffic exceeds what the pool was sized for.

5-minute try-it

Call simulateConnectionPool(20, 20) and then simulateConnectionPool(20, 21). What is the immediate/waiting split for each, and why does adding just one extra request past the pool size change the result so sharply?

One important caution

Letting each serverless function instance or app process open its own unpooled connection, which can multiply far beyond what the database can actually handle under load.

Treating a completed backup job as proof the data is safe, without ever running a restore drill to confirm the backup can actually be used.

Wikipedia: Connection pool — How Databases Work