Sheet 09

Database Design (PostgreSQL OLTP)

QPS cascade, IOPS provisioned storage, connection pooling math, 30-day retention forecast, and replica sizing.

Actual Disk QPS4,800 QPS (95% Cached)

Query Demand & Cache Absorption Cascade

Logical Query Demand
20,000 QPS
Peak RPS × 3 queries
Read / Write Split
16,000 R / 4,000 W
80% Read / 20% Write
Cache Absorbed Reads
15,200 QPS
95% Redis hit rate
Actual Disk QPS
4,800 QPS
Hits PostgreSQL engine

IOPS & SSD Allocation

Required Sustained IOPS5,760 IOPS
Recommended (2x Headroom)11,520 IOPS
EBS Volume Provisioned231 GB (gp3 / io2)
IOPS / Query Factor1.2 iops / query

Connection Pool Sizing

Conn / App Pod (Cores × 2 + 1)9 conns
Active App Pods29 pods
Max Connections (postgresql.conf)392 connections
Connection PoolingPgBouncer (Transaction Mode)

30-Day Storage Projection

Daily Writes345.6M writes/day
30d Hot Table Data19.3 TB
With Indexes & WAL (2x)57.9 TB
Read Replicas Sized4 Nodes (r6i.xlarge)

Database Formulas & Derivations

1. Total Logical DB QPS

Peak_RPS × QUERIES_PER_API_CALL

2. Physical Storage QPS (post-cache)

Cache_Miss_Reads + Write_QPS

3. Recommended IOPS (2x Headroom)

Actual_DB_QPS × DB_IOPS_PER_QUERY × 2

4. Max Connections (postgresql.conf)

CEIL(Total_App_Instances × (Cores × 2 + 1) × 1.5)

5. Read Replicas Required

CEIL(Read_QPS / DB_READ_REPLICA_QPS)