Connection Pooling & Migrations

Two everyday-backend topics that share nothing except being about databases at scale: connection pooling (reusing DB connections) and migrations (versioning our schema changes). Both come up constantly once we run real services.

Why connections are expensive

Opening a database connection isn’t cheap. Each one means a TCP handshake, then TLS negotiation, then authentication, then the database forks or assigns a backend process/thread and allocates memory for it.

In simple language — opening a connection is like hiring and onboarding a new employee. Doing it fresh for every single query would be madness.

So if every web request opened its own connection, ran one query, and closed it, we’d waste most of our time on setup and teardown. Worse, databases cap how many connections they’ll accept (Postgres defaults to ~100). A traffic spike could open thousands and knock the database over.

What a connection pool is

A connection pool is a fixed set of already-open connections that we keep around and hand out on demand. A request borrows one, runs its query, and returns it to the pool instead of closing it. The next request reuses the same warm connection.

Think of it like a fleet of taxis idling at a stand — riders hop in and out, but the cars never go home. No re-hiring per trip.

// one pool for the whole app — created once, reused forever
const pool = new Pool({ max: 10 });          // never opens > 10 connections
const { rows } = await pool.query("SELECT 1"); // borrows + returns automatically

Pool sizing gotchas

Bigger is not better. The classic mistake is setting max to some huge number “to be safe”.

• Every app instance has its own pool. Run 10 app pods with max: 20 and that’s 200 connections aimed at the database — easily past its limit.
• A pool bigger than the database can handle just moves the queue from the pool to the database, which handles contention worse.
• A common starting point is small — often around (cores * 2) per instance — then tune with real metrics.
• Always set a connection timeout so a request fails fast instead of hanging forever when the pool is drained.

Migrations — versioning the schema

A migration is a versioned script that changes the database schema — add a table, add a column, create an index. We check them into git alongside the code, so the schema evolves in lockstep with the app.

In simple language — migrations are “git for our database structure”. Every change is a numbered step everyone runs in the same order.

Key properties:

• Versioned & ordered — each migration has a number/timestamp; they run in sequence. The database records which ones it’s already applied, so re-running is safe (idempotent at the runner level).
• Forward-only in production — we roll forward with a new migration to fix a mistake, rather than un-applying old ones on a live system.
• Up / down — an up applies the change, a down reverses it. down is mostly a dev-time safety net; in prod we prefer a new forward migration.

Tools that do this: Flyway and Liquibase (JVM world), knex/Prisma Migrate (Node), Alembic (Python), Rails migrations. They all share the same idea — a folder of ordered scripts plus a tracking table.

-- 0007_add_last_login.up.sql
ALTER TABLE users ADD COLUMN last_login timestamptz;

-- 0007_add_last_login.down.sql
ALTER TABLE users DROP COLUMN last_login;

Zero-downtime migrations — expand/contract

Here’s the senior-level bit. If we deploy a schema change and the app change at the same instant, there’s a window where old code hits the new schema (or vice versa) and things break. The fix is the expand-contract (a.k.a. parallel-change) pattern — never break compatibility in a single step.

Say we’re renaming name to full_name:

1. Expand — add the new full_name column. Old code ignores it, still works.
2. Migrate — deploy code that writes to both columns and backfill existing rows.
3. Switch — deploy code that reads from full_name.
4. Contract — once nothing touches name, drop it in a later migration.

Each step is backward-compatible, so at no point are the running app and the schema out of sync. The golden rule: additive changes first, destructive changes last, and never in the same deploy.

Interview soundbite

Connections are expensive (TCP + TLS + auth + a server-side process), so we keep a connection pool of warm connections and borrow/return them per request — sized small and tuned, because every app instance has its own pool and databases cap total connections. Migrations are versioned, ordered, forward-only schema scripts (Flyway, Liquibase, knex) with up/down steps, checked into git. For zero downtime we use expand-contract: add the new thing, dual-write and backfill, switch reads, then drop the old thing in a later deploy — additive first, destructive last, never in one step.