Architecture Review: Write-Heavy Transactional Platform
A high-volume transactional write architecture anchored on PostgreSQL, where write throughput, durability guarantees, and audit completeness must coexist. The outbox pattern ensures reliable event publishing to Kafka without two-phase commit, and WAL-based CDC provides a durable change log that can reconstruct system state. Connection pooling via PgBouncer bounds connection overhead at the database layer.
Evidence Confidence
Moderate
strong
Executive Summary
Write-Heavy Transactional Platform: moderate operational readiness (80% evidence confidence). 2 architectural strengths identified, 4 operational risks to manage. Primary concern: WAL Saturation. Requires Advanced operational maturity.
Readiness Rationale
Overall moderate readiness across 8 dimensions. Limited: team maturity. Strong: migration, observability, failure recovery.
Key Concerns
- !WAL Saturation
- !Write Amplification Cascade
Key Strengths
- +Write-heavy transactional workloads that emit downstream events (order placed, payment captured) benefit from the outbox pattern…
- +Kafka is the standard downstream target for WAL-based CDC pipelines: Debezium captures database WAL records and publishes them to…
8
Assessments
4
Tradeoffs
6
Sections
11
Recommendations
Readiness Assessments
8Architectural Tradeoffs
4Recommendations
11Monitor: Write Amplification Cascade
risk_monitoringEach logical application write triggers multiple physical writes through index maintenance, WAL generation, MVCC versioning, and replication, causing actual disk IOPS to exceed the provisioned I/O ceiling while the logical write rate appears modest.
Affects 0 nodes
Monitor: WAL Saturation
risk_monitoringPostgreSQL WAL (Write-Ahead Log) generation rate exceeds wal_buffers flush capacity or downstream replica/WAL archive bandwidth, causing write transactions to stall waiting for WAL flush and replication lag to grow unboundedly.
Affects 1 node. (Write-Heavy Transactional)
Implement: Monitor generic risk probe signals
observabilitySeed 'WAL Saturation Risk Probe' identifies 2 metrics relevant to wal_saturation.
Metrics to instrument: error_rate, p95_latency_ms
Single PostgreSQL with synchronous dual-write (DB + Kafka in application code) → PostgreSQL + outbox pattern + WAL CDC relay to Kafka
migration_planningTrigger: Dual-write inconsistency events observed in production: DB write succeeds but Kafka publish fails; or Kafka publish succeeds but DB transaction rolls back. Migrate from 'Single PostgreSQL with synchronous dual-write (DB + Kafka in application code)' to 'PostgreSQL + outbox pattern + WAL CDC relay to Kafka'. This migration eliminates the distributed consistency hazard at the cost of added write latency (~2ms per transaction for the outbox INSERT). The outbox relay should be deployed and stabilized before the synchronous dual-write is removed.
Outbox table migration requires careful schema design; wrong partitioning causes future cleanup overhead; CDC relay setup requires PostgreSQL logical_replication slot creation, which must be monitored from day one
PostgreSQL + PgBouncer + outbox + Kafka CDC → Domain-partitioned PostgreSQL + separate write services per partition
migration_planningTrigger: Primary write CPU > 70% sustained during peak windows; WAL volume exceeding 500MB/minute; lock contention on entity hot spots visible in pg_locks. Migrate from 'PostgreSQL + PgBouncer + outbox + Kafka CDC' to 'Domain-partitioned PostgreSQL + separate write services per partition'. Partition by the natural domain boundary (account, tenant, region) that minimises cross-partition writes. Run both write paths in parallel for a canary period before decommissioning the monolithic primary.
Cross-partition transactions require saga or two-phase commit: significantly higher complexity; Application must be decomposed to route writes by partition key; naive fan-out causes N+1 write problems
Prepare runbook for: Burst Traffic Cold Cache Stampede
simulation_preparednessSimulation demonstrates critical degradation of redis, postgresql
Without a runbook, recovery from this failure mode will be ad-hoc
Prepare runbook for: Connection Pool Exhaustion with Horizontal User Scale
simulation_preparednessSimulation demonstrates critical degradation of postgresql
Without a runbook, recovery from this failure mode will be ad-hoc
Plan evolution: OLTP Analytics Queries → OLTP + OLAP Separation
evolution_planningEvolution from Unified OLTP + Analytics on PostgreSQL → Separated OLTP (PostgreSQL) + OLAP (ClickHouse/Snowflake)
Migration complexity: medium. Rollback: always.
Plan evolution: PostgreSQL → Partitioned PostgreSQL
evolution_planningEvolution from Single-Node PostgreSQL → Partitioned PostgreSQL
Migration complexity: high. Rollback: rarely.
Monitor threshold: Tier 1: Connection Pool Saturation
scaling_monitoringSignal: PgBouncer wait_queue > 0 sustained; application p99 write latency rising faster than PostgreSQL p99; pool_mode=transaction showing >80% utilization
Bottleneck: PgBouncer pool_size too small for write concurrency profile. Evolution: Increase PgBouncer pool_size incrementally; profile transaction duration to right-size pool; consider separate pools for write-heavy and read-only workloads
Monitor threshold: Tier 2: WAL and Checkpoint Pressure
scaling_monitoringSignal: 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
Bottleneck: Write rate exceeding PostgreSQL's WAL flush and checkpoint throughput. 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
Scaling Pressure Signals
8PgBouncer wait_queue > 0 sustained; application p99 write latency rising faster than PostgreSQL p99; pool_mode=transaction showing >80% utilization
Threshold
Tier 1: Connection Pool Saturation
Likely Bottleneck
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
Threshold
Tier 2: WAL and Checkpoint Pressure
Likely Bottleneck
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
pg_locks shows contended rows with wait events > 5ms; write throughput plateauing despite available CPU; deadlock errors appearing in application logs
Threshold
Tier 3: Lock Contention and Hot Partition
Likely Bottleneck
Hot row contention: multiple writers competing for the same row version
Recommended Evolution
Partition the hot table by entity range or hash; introduce optimistic locking with retry for high-contention entities; consider queue-per-entity serialization via application-level lock tokens
PostgreSQL CPU > 80% sustained on write queries; WAL volume exceeding 1GB/minute; replication lag on replica > 30s; Kafka consumer lag growing despite healthy CDC relay
Threshold
Tier 4: Primary Write Ceiling
Likely Bottleneck
Single PostgreSQL primary write throughput ceiling (~5000–8000 TPS depending on row size)
Recommended Evolution
Introduce horizontal write partitioning by domain entity (e.g., per-account or per-region sharding); evaluate CockroachDB or YugabyteDB for distributed ACID writes if domain decomposition is insufficient
PgBouncer wait_queue > 0 sustained; application p99 write latency rising faster than PostgreSQL p99; pool_mode=transaction showing >80% utilization
Threshold
Escalation trigger: PgBouncer pool_size too small for write concurrency profile
Likely Bottleneck
Tier 1: Connection Pool Saturation
Recommended Evolution
Monitor: error_rate, p95_latency_ms
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
Threshold
Escalation trigger: Write rate exceeding PostgreSQL's WAL flush and checkpoint throughput
Likely Bottleneck
Tier 2: WAL and Checkpoint Pressure
Recommended Evolution
Monitor: error_rate, p95_latency_ms
pg_locks shows contended rows with wait events > 5ms; write throughput plateauing despite available CPU; deadlock errors appearing in application logs
Threshold
Escalation trigger: Hot row contention: multiple writers competing for the same row version
Likely Bottleneck
Tier 3: Lock Contention and Hot Partition
Recommended Evolution
Monitor: error_rate, p95_latency_ms
PostgreSQL CPU > 80% sustained on write queries; WAL volume exceeding 1GB/minute; replication lag on replica > 30s; Kafka consumer lag growing despite healthy CDC relay
Threshold
Escalation trigger: Single PostgreSQL primary write throughput ceiling (~5000–8000 TPS depending on row size)
Likely Bottleneck
Tier 4: Primary Write Ceiling
Recommended Evolution
Monitor: error_rate, p95_latency_ms
Migration Readiness
12Migration Stages
3Single PostgreSQL with synchronous dual-write (DB + Kafka in application code) → PostgreSQL + outbox pattern + WAL CDC relay to Kafka
infoMigration trigger: Dual-write inconsistency events observed in production: DB write succeeds but Kafka publish fails; or Kafka publish succeeds but DB transaction rolls back
PostgreSQL + PgBouncer + outbox + Kafka CDC → Domain-partitioned PostgreSQL + separate write services per partition
infoMigration trigger: Primary write CPU > 70% sustained during peak windows; WAL volume exceeding 500MB/minute; lock contention on entity hot spots visible in pg_locks
PostgreSQL + Kafka CDC → Event sourcing: append-only event log with read model projections
infoMigration trigger: Audit completeness requirements grow beyond point-in-time backups; need to reconstruct entity state at any historical moment; command/query separation would materially improve read scalability
Risks
9Outbox table migration requires careful schema design; wrong
warningOutbox table migration requires careful schema design; wrong partitioning causes future cleanup overhead
CDC relay setup requires PostgreSQL logical_replication slot
warningCDC relay setup requires PostgreSQL logical_replication slot creation, which must be monitored from day one
Cross-partition transactions require saga or two-phase commi
warningCross-partition transactions require saga or two-phase commit: significantly higher complexity
Application must be decomposed to route writes by partition
warningApplication must be decomposed to route writes by partition key; naive fan-out causes N+1 write problems
Event sourcing requires replay to reconstruct current state:
warningEvent sourcing requires replay to reconstruct current state: snapshot cadence must be designed from the start
Schema evolution for events is harder than for relational ta
warningSchema evolution for events is harder than for relational tables; backward compatibility must be enforced at write time
Cross-service workflows that previously used database transa
criticalCross-service workflows that previously used database transactions now require Saga orchestration. Mitigation: Design idempotent event handlers; implement compensating transactions for every multi-step workflow; test failure injection in staging
↗ modular-monolith-to-event-driven
Consumer lag silently accumulates: a lagging consumer is not
criticalConsumer lag silently accumulates: a lagging consumer is not a failed consumer. Mitigation: Alert on consumer lag rate-of-change, not absolute depth; implement dead letter queues with alerting
↗ modular-monolith-to-event-driven
Missing partition for current time window causes all INSERTs
criticalMissing partition for current time window causes all INSERTs to fail with 'no partition of relation found'. Mitigation: Create partitions 7-30 days in advance; alert when next partition does not exist before its time window opens
↗ postgresql-to-partitioned
Review Sections
6Referenced Intelligence