#1. Executive Summary & Problem Statement

Relational databases (PostgreSQL, MySQL, Aurora) are commonly the single most expensive persistent service in a cloud deployment. When performance degrades, engineering teams frequently default to vertical instance upscaling (e.g. jumping from db.r6g.xlarge to db.r6g.4xlarge), quadrupling monthly costs to $1,800/month.

In $> 80\%$ of cases, database CPU saturation is not caused by genuine business throughput, but by:

1. Unindexed slow queries scanning millions of rows per request.
2. Connection exhaustion from microservices opening unpooled PostgreSQL connections.
3. Over-provisioned read-replicas idling during off-peak hours.

---

#2. Connection Pooling Architecture with PgBouncer

Every PostgreSQL connection allocates 5 MB to 10 MB of dedicated server RAM and incurs kernel process-scheduling overhead. Under high concurrency, 500 direct client connections can consume 4 GB of RAM and overwhelm CPU scheduling.

Deploying PgBouncer in transaction pooling mode allows 10,000 application clients to share 50 backend database connections seamlessly:

[1,000 Microservice Pods] ──> [PgBouncer Pooler] ──> [50 Dedicated PG Conns] ──> [RDS Postgres]

PgBouncer Production Configuration

[databases]
app_db = host=aurora-cluster.prod.internal port=5432 dbname=app_production pool_mode=transaction

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
max_client_conn = 5000
default_pool_size = 50
reserve_pool_size = 10
server_idle_timeout = 60

---

#3. Detecting & Eliminating CPU-Saturating Queries

Deploy pg_stat_statements to identify the top 5 queries consuming the vast majority of CPU time:

SELECT 
    round((total_exec_time / 1000 / 60)::numeric, 2) AS total_minutes,
    calls,
    round((mean_exec_time)::numeric, 2) AS avg_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS percentage_cpu,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Adding a single composite index on queries taking 90% of total CPU time typically reduces database CPU utilization from 95% to 8%, allowing instances to be downsized immediately.

---

#4. Serverless Database Scaling Ceilings

For serverless databases (AWS Aurora Serverless v2, Neon):

• Always define a strict max_capacity ceiling (e.g., maximum 8 ACUs in production, 2 ACUs in staging).
• Without a ceiling, a batch ingestion job or a recursive query can trigger full scaling up to 128 ACUs, generating surprise bills of thousands of dollars within hours.