Compare Scenarios

Migration Step 1

Both scenarios define a migration step at this stage. Write-Heavy Transactional Platform: triggered by 'Dual-write inconsistency events observed in production: DB w'. Content Management Platform: triggered by 'Content read API p99 > 100ms during peak traffic; PostgreSQL'.

Migration Step 2

Both scenarios define a migration step at this stage. Write-Heavy Transactional Platform: triggered by 'Primary write CPU > 70% sustained during peak windows; WAL v'. Content Management Platform: triggered by 'Full-text search p99 > 500ms; inability to implement faceted'.

Migration Step 3

Both scenarios define a migration step at this stage. Write-Heavy Transactional Platform: triggered by 'Audit completeness requirements grow beyond point-in-time ba'. Content Management Platform: triggered by 'Month-end content performance reports generating sequential '.

PgBouncer wait_queue > 0 sustained; application p99 write latency rising faster than PostgreSQL p99; pool_mode=transaction showing >80% utilization

Tier 1: Connection Pool Saturation: PgBouncer pool_size too small for write concurrency profile. Recommended evolution: Increase PgBouncer pool_size incrementally; profile transaction duration to right-size pool; consider separate pools for write-heavy and read-only workloads .

PostgreSQL checkpoint_completion_target warnings in logs; wal_buffers flushing more than once per second; pg_stat_bgwriter shows checkpoints_req rising; write p99 > 20ms without query explanation

Tier 2: WAL and Checkpoint Pressure: Write rate exceeding PostgreSQL's WAL flush and checkpoint throughput. Recommended evolution: Tune checkpoint_completion_target to 0.9; increase wal_buffers to 64MB; move PostgreSQL WAL to a dedicated NVMe volume separate from data directory .

PostgreSQL pg_stat_statements showing > 10 distinct query patterns with high call counts from the content list API path; database queries per second growing linearly with API request rate for content list endpoints (should be sub-linear with proper batch fetching); p99 for content list API > 200ms during moderate traffic

Tier 1: N+1 Query Amplification: ORM-level N+1 patterns in content relationship traversal: author fetch, category fetch, related content fetch as independent queries per article. Recommended evolution: Audit every content API response with query logging enabled and count queries per request for each content type; implement eager loading for all included relationships (JOIN for 1:1, IN-clause batch for 1:N); validate that content list endpoints produce a fixed number of queries regardless of list size (O(1) queries, not O(N)); add a query count assertion to integration tests for content list endpoints to prevent regression .

PostgreSQL read replica CPU spike correlated exactly with publish events; Redis cache hit rate dropping to near 0% immediately after publish for popular content; content API p99 spiking from < 20ms to > 500ms during the 1–3 second window after a high-traffic content item is published

Tier 2: Cache Invalidation Thundering Herd: Cache key deletion on publish triggering simultaneous cache miss stampede for all concurrent readers of popular content. Recommended evolution: Implement cache-aside with probabilistic early expiration (PER): before the cache TTL expires, a fraction of reads proactively refresh the cache value while other reads continue serving the cached value; this eliminates the hard expiry boundary that causes simultaneous misses; alternatively, on publish, write the new content value directly into the cache key before invalidating the old one (update-in-place rather than delete-and-miss) to eliminate the invalidation gap .