Connection Pools and Schema Migrations

One Handle, Many Connections

It is easy to look at a *sql.DB and imagine one fixed socket to the database. That is not how it works. A better picture is a taxi stand. Your service asks for rides all day long. Some taxis are already waiting, some are driving passengers, and some may leave if they have sat around too long. The stand coordinates all of that so callers do not manually open and close a brand-new taxi for every trip.

That coordination is called connection pooling. Reusing live connections is faster than rebuilding them for every request. But a pool also needs limits. Too few connections and requests wait in line. Too many connections and the database itself becomes overloaded because every application instance is trying to be helpful at once.

Tuning the Pool for Real Traffic

Three settings appear again and again because they answer three different failure modes. SetMaxOpenConns says how many live database connections your process may have in total. SetMaxIdleConns says how many finished connections it is worth keeping around for the next burst. SetConnMaxLifetime says how long a connection may live before being recycled, which helps when load balancers, proxies, or the database server itself dislike ancient connections lingering forever.

Imagine a checkout service running on four application instances. If each instance allows 100 open connections, the database may suddenly face 400 clients before you even count admin tools, background workers, or migrations. That is why pool tuning is not just a code detail. It is capacity planning in miniature.

Why Migrations Exist

Schema changes deserve the same discipline as code changes. You would not open a production source file in a text editor on the server, type a few random lines, and hope the rest of the team somehow guesses what changed. Hand-editing a production schema is the database version of that mistake.

A migration is a versioned, ordered change to the schema. Usually it lives in a file with a clear sequence number or timestamp. That file becomes the permanent record of how the schema evolved: create table, add column, rename index, backfill data, and so on. Because the changes are ordered, a fresh environment can replay them from the beginning and arrive at the same final shape as production.

Tracing a Schema Change

Suppose your support_tickets table originally knows the ticket title and whether the ticket is closed, but not how urgent the work is. Product asks for a priority field so the team can separate a spelling fix from a payment outage. A migration is how you teach the schema that new idea.

-- 20260720_add_priority_to_support_tickets.up.sql
ALTER TABLE support_tickets
ADD COLUMN priority TEXT NOT NULL DEFAULT 'normal';

1. Before the migration, ticket #42 might be stored as ('Printer room badge issue', false).
2. The migration adds a priority column with a default of 'normal'.
3. After the migration, the same row effectively becomes ('Printer room badge issue', false, 'normal').
4. New code can now update urgent incidents to 'high' without breaking older rows.

Quiz and Practice

Hands-On Project

Summary and Key Takeaways

• A *sql.DB is a shared connection pool manager, not a single fragile wire.
• Pool tuning is about balancing application throughput against total database capacity.
• SetMaxOpenConns, SetMaxIdleConns, and SetConnMaxLifetime solve different operational problems.
• Migrations make schema changes ordered, reviewable, and repeatable across environments.
• Never let production become the only place that knows how your schema evolved.