Managed Databases
Advanced 13 min read Managed Databases

Database Sizing and Connection Management

Right-size the box and tame the connections

Sizing databases means instance shape, storage and IOPS for the workload — and connection management is where many 'database is down' incidents actually live: exhausted max_connections, connection storms and pool mishaps.

connection poolmax_connectionspipelinePgBouncertransactional IDEinstance sizing
Prerequisites: Relational Managed Databases
Before we start

What you will learn

  • Size instance and IOPS from workload metrics
  • Explain the connection-budget and pool math
  • Operate PgBouncer/Proxy/connection multiplexing
In 60 seconds

A database instance has capped connections, memory and I/O. Sizing matches those limits to real patterns; pooling amortizes connections so 1,000 app pods share 50 sockets without 'too many connections'.

Introduction

What is this?

Sizing: instance shapes (db.t3.medium → io2 provisioned IOPS), storage class, and instance reconfiguration. Connection math: Postgres max_connections ~ hundreds; serverless pools and PgBouncer (transaction pooling) aggregate; Aurora/Azure have serverless/connection proxies; MySQL Threads_connected, wait_timeout tuning. Sizing signs: hit max_connections → pool; slow query → index/IOPS; memory pressure → buffer/instance size.

Motivation

Why do you need it?

  • Exhausting connections is the classic 'DB looks down' production incident.
  • Connection pooling cuts RAM-per-socket and network churn dramatically.
  • Right-sizing prevents throttle-hitting IOPS at the worst moment.
  • Interview staple: walk through 'too many connections' end to end.
Think about it

Mental model

Imagine

A phone switchboard with limited trunk lines

Think of

Connection pooling

Thousands of calls (app requests) share dozens of trunk lines (pooled connections); the switchboard (pool) queues and multiplexes instead of giving every caller a private handset connection.

Mechanics

How it works

  • Every connection costs memory (rdbuffers, stacks); max_connections bounds concurrency.
  • A pool keeps N sockets alive and lends them per-transaction (transaction pooling) or per-session.
  • Poolers (PgBouncer, Azure SQL connection pooling, Aurora read/write endpoints) manage multiplexing and limits.
  • wait_timeout/idle_in_transaction_session_timeout evicts idle drains.
  • Sizing: compute typical concurrent ops + idle review budget; scale read replicas for read-heavy splits.

Connection pooling path.

App pods
Pool
DB
Transactions
Idle drains
Hands-on

Examples

Pool and sizing in practice

Pool and sizing in practice
# PgBouncer transaction pooling dest:
# pools 100 app pods to 20 server connections
pgbouncer.ini:
[databases]
app = host=db.rds   pool_mode=transaction   max_client_conn=200
[pgbouncer]
default_pool_size = 20
# app config: connect to pgbouncer:6432, not the DB:5432
# Postgres: bound the DB
ALTER SYSTEM SET max_connections = 200;
UPDATE pg_settings_value ... -- careful; restart for effect

Client pods connect to the pool; the pool keeps a small warm set of real DB sockets.

Real world

In production

  • A 200-pod app fronts Aurora with PgBouncer (max_client_conn=200, default_pool_size=40).
  • Azure SQL uses the built-in connection pooling + MAX_CONNECTIONS bound per DTU.
  • A MySQL shop times out idle connections at 60s and pools via ProxySQL.
Guidelines

Best practices

  • Pool before you scale: a pool fixes the connection dimension without buying IOPS.
  • Set sane client idle timeouts and transaction-idle limits at the DB.
  • Size IOPS from p99 (burst) and connections from concurrent pods × app concurrency.
  • Keep pooling + read/write split visible; route reporting to replicas through the pool too.
Watch out

Common mistakes

  • Bumping max_connections instead of pooling — memory for sockets, then OOM.
  • A connection per request in the app (churn kills pool efficiency and adds latency).
  • Pool sized to 'nice to have' rather than peak concurrency × readiness spike.
  • Altering parameters without restart planning → half the config never applied.
Root cause analysis

Common issues

Error
Too many connections error
Root cause

Pool too small or app leaks connections

Fix

Enlarge pool client conns, set idle timeouts, find and fix the leak before touching max_connections.

Error
Pool starves during deploy restarts
Root cause

Sudden connection storm (all pods racing)

Fix

Stagger deploys, cap app-side pool size, and warm the pool before the burst.

When things break

Debugging tips

  • 'too many connections': check the pool/active sessions, then look at RUNNING queries not just count.
  • Connection leaks: session that never closes → max idle; set idle limits and watch pool metrics.
  • Sizing regression: compare IOPS baseline, latency p99, and 'idle in transaction' count.
  • If requests queue at the pool, see whether app-side connection limit or DB query time is the real bound.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/1
1

What does transaction pooling do with a client connection?

Go deeper

Official documentation

PgBouncer — Documentation
docs
AWS — RDS connection pooling
docs
Azure — Connection engineering
docs