Executive Summary & Production Diagnostic
When multi-tenant B2B SaaS platforms scale past 10,000 Requests Per Second (RPS), database performance degradation is rarely caused by "slow hardware." It is almost exclusively caused by unindexed composite filtering, buffer cache thrashing, and connection starvation.
During a recent backend performance optimization sprint for a high-throughput SaaS platform, peak-hour API requests were hitting a severe P95 latency wall of 4,200ms (4.2 seconds). The primary RDS PostgreSQL instance was pegged at 92% CPU utilization, triggering read timeouts and database pool exhaustion.
This case study breaks down how we used pg_stat_statements, EXPLAIN (ANALYZE, BUFFERS), partial B-Tree indexing, and PgBouncer transaction-level connection pooling to drop P95 API response times to 14.2ms (a 99.6% reduction) while cutting RDS instance costs by $2,400/month.
Technical Metric Benchmark Comparison
| Metric / Benchmark Area | Initial Production State | Post-Optimization Benchmark | Improvement / Impact |
|---|---|---|---|
| P95 API Response Latency | 4,200ms (4.2 sec) | 14.2ms | 99.6% Latency Drop |
| PostgreSQL Primary CPU Load | 92% (Sustained Bottleneck) | 14% (Idle Headroom) | 84.7% CPU Reduction |
| Shared Buffer Hit Ratio | 82.4% (Disk I/O Thrashing) | 99.8% (In-Memory) | Near-Zero Disk I/O |
| RDS Instance Monthly Cost | $3,600 / mo (db.r6g.4xlarge) | $1,200 / mo (db.r6g.xlarge) | $2,400/mo ($28.8k/yr) Saved |
🔍 Root Cause Analysis: The 22M Row Sequential Scan
Using pg_stat_statements ordered by total_exec_time, we isolated a single telemetry API endpoint executing over 4,000 times per minute:
-- Slow Query Pattern:
SELECT id, tenant_id, payload, created_at
FROM tenant_events
WHERE tenant_id = 'tenant_9941'
AND status = 'PROCESSED'
ORDER BY created_at DESC
LIMIT 50;
Running EXPLAIN (ANALYZE, BUFFERS) revealed that PostgreSQL was forced to scan 22,400,000 rows sequentially because existing indexes only covered tenant_id independently.
-- EXPLAIN Output:
Seq Scan on tenant_events (cost=0.00..682490.12 rows=52 width=142) (actual time=4182.110..4189.421 rows=50 loops=1)
Filter: ((status = 'PROCESSED'::text) AND (tenant_id = 'tenant_9941'::text))
Buffers: shared read=84102 hit=1204
🛠️ Step-by-Step Remediation Engineering
1. Partial Composite B-Tree Indexing
Instead of creating a massive index on all 22 million rows (which inflates WAL write latency), we engineered a Partial Composite B-Tree Index targeting only active/processed records:
-- 1. Create Partial Composite Index without blocking production writes
CREATE INDEX CONCURRENTLY idx_events_tenant_created_partial
ON tenant_events (tenant_id, created_at DESC)
WHERE status = 'PROCESSED';
Why this worked:
- ▸Index Size Reduction: The index size dropped from 1.4 GB down to 180 MB, allowing the entire index to permanently reside in PostgreSQL
shared_buffersRAM. - ▸Zero Write Amplification: Inserts for un-processed rows bypass the index entirely.
2. Verified Execution Plan (EXPLAIN ANALYZE)
After applying the partial index, the query execution plan shifted from a 4.2-second Sequential Scan to a lightning-fast Index Scan:
-- Post-Index EXPLAIN Output:
Index Scan using idx_events_tenant_created_partial on tenant_events (actual time=0.042..0.088 rows=50 loops=1)
Index Cond: (tenant_id = 'tenant_9941'::text)
Buffers: shared hit=4 read=0
-- Execution Time: 0.120 ms!
3. PgBouncer Transaction Pool Tuning
To prevent microservice container restarts from exhausting PostgreSQL backends:
- ▸Deployed PgBouncer in front of RDS.
- ▸Switched pool mode to
transaction. - ▸Set
default_pool_size = 30withmax_client_conn = 5000.
💡 Key Lessons for Engineering Leaders
- ▸Beware of Single-Column Indexes: Multiple single-column indexes on high-cardinality tables force PostgreSQL to perform Bitmap Index Scans, which degrade heavily under concurrency.
- ▸Use Partial Indexes: If 80% of your queries filter on
status = 'active', make your index partial. You will save gigabytes of RAM and speed up write operations. - ▸Monitor
shared_buffersHit Ratio: If your buffer hit ratio drops below 99%, your database is reading from EBS block storage instead of RAM.