
SaaS Schema Migrations: Separate Expansion, Backfill, and Removal
Plan database changes around compatible application versions, explicit data mappings, measurable backfills, and verified recovery boundaries.
MVPs & SaaS Projects
Size SaaS database connection pools using the total number of processes, deployment overlap, wait behavior, and measured database capacity rather than one local setting.

LET’S BUILD SOMETHING USEFUL
Talk to Ataraxy Developers about your website, app, or custom software.
A developer increases a database pool limit to make a busy service respond faster. The setting appears modest on one process. In production, multiple replicas, background workers, and a rolling deployment multiply that limit until the database cannot accept more connections. The application now spends more time waiting or failing, even though each individual instance looks reasonably configured.
Connection pools should be budgeted across the whole system. The useful question is not how many connections one process can open, but how much concurrent database work the deployment can support with room for operations and recovery. Start with the topology, then measure waiting and query behavior. A larger pool is a capacity decision with tradeoffs, not a universal fix for slow requests.
List web replicas, job workers, scheduled tasks, administration tools, migration jobs, and any service that connects independently. Identify separate pools within each process. Include maximum autoscaling and temporary overlap between old and new instances during deployment. A spreadsheet of these consumers is often more useful than beginning with a tuning parameter in one application’s configuration.
The node-postgres pool sizing guide discusses considering pool limits across service instances and leaving capacity for scaling and management. Its examples are a starting point, not a measured answer for every workload. Use the actual database limits, hosting behavior, query mix, and connection consumers in your environment to establish a provisional budget.
For illustration, suppose a team plans four web instances with a maximum pool of eight each and two workers with six each. That represents up to forty-four application connections before other consumers. If a rollout temporarily adds two more web instances, the modeled ceiling rises by sixteen. These are hypothetical planning numbers, not recommended defaults. The exercise makes hidden multiplication visible.
Reserve capacity for required operational access and other known consumers. Check whether a managed service or proxy changes the effective limits. Document which numbers are configured maxima and which are measured typical usage. A budget based only on average connections can fail during startup, a burst of jobs, or a deployment. Review the maximum plausible combination that the system is actually allowed to create.
Measure how long a request waits to acquire a connection separately from how long its query runs. Also observe active, idle, and waiting work where the client library exposes those measures. A slow endpoint can be waiting for a connection, blocked inside the database, or spending time elsewhere. Without this separation, a pool change can hide the underlying problem and increase pressure on the database.
For a reporting endpoint, compare a representative request under ordinary load and a controlled burst. If the query itself becomes slower as concurrency increases, expanding the pool may amplify contention. If a small number of requests hold connections while performing unrelated network calls, shorten that ownership first. Capacity tuning should follow evidence about where time and resources are consumed.
The node-postgres pooling documentation emphasizes returning checked-out clients even when a query fails, and offers a pool query convenience method for work that does not require a transaction. Review every acquisition path for release on success, exception, and cancellation. A leaked client can gradually exhaust a pool even when traffic has not increased.
For a multi-statement transaction, use the same checked-out connection for its statements and define the rollback and release path, as the transaction documentation requires. Avoid keeping a transaction open while waiting for a person to confirm a dialog or for a slow external API to respond. Store the necessary intermediate business state instead of treating a database connection as a long-lived user session. Keep the transaction’s purpose bounded and test its failure path.
A pool with a finite size can still be overwhelmed if the application lets an unbounded queue of requests wait for it. Define an acquisition timeout and an admission policy appropriate to the workload. A rejected request should receive an understandable result and, where appropriate, a documented retry path. Do not claim success merely because the request entered an in-memory waiting queue.
Different work can need different treatment. Interactive account reads may deserve a short response budget, while exports can enter a durable job queue with controlled concurrency. Separating those paths can prevent a burst of heavy reports from consuming every opportunity for basic interactions. The exact arrangement depends on the application, but its resource budget should be explicit and visible to operators.
A shared pool does not automatically provide fair access. One tenant’s burst can occupy the available work slots. Review concurrency limits or admission controls at the application layer where the product requires fairness. Keep those controls tied to verified tenant identity and account policy. A client-supplied tenant field should not determine access to capacity without authorization.
Test a noisy tenant alongside ordinary tenants and inspect both latency and errors. Avoid creating a new independent pool for every request or tenant without understanding the connection multiplication. If the architecture uses separate tenant databases, model that topology directly. Capacity reporting should explain which pools share one database limit and which are truly independent resources.
A database proxy or external pooler can change how application connections map to database sessions. It does not remove the need for a capacity model. Review the proxy’s pooling mode, timeout behavior, and compatibility with features the application uses, such as session state or prepared statements. Consult the specific provider and version documentation before assuming a configuration is interchangeable with direct connections.
Keep application-level and database-level measurements distinct. A large number of client connections to a proxy does not necessarily equal the same number of active database sessions. Conversely, a healthy proxy connection count does not prove queries are fast or transactions are short. Observe the whole path and keep enough context to identify which layer is waiting or rejecting work.
Run controlled tests at the intended concurrency and during instance startup and replacement. Confirm that old processes release resources and new processes do not create an unexpected connection burst. Include a failed database connection, a slow query, and a worker that reaches its timeout. Verify that the service recovers without requiring a manual restart to clear leaked ownership.
Change one significant limit at a time and record the workload, observed waits, database behavior, and customer-visible result. AI can help summarize these measurements, but it should not invent an optimal pool size from a generic benchmark. Keep the final decision reproducible and revisit it when replica counts, workloads, or database capacity change.
Reliable pool sizing begins with the full deployment, including workers and rollout overlap. Budget total connections, measure acquisition waits separately from query execution, release clients reliably, and bound queued work. A documented capacity model and controlled tests make the setting explainable and help prevent a local tuning change from becoming a system-wide shortage.
LET’S BUILD SOMETHING USEFUL
Talk to Ataraxy Developers about your website, app, or custom software.