Use Write-Heavy Transactional Platform as the Foundational Architecture Pattern
Deterministic ADR derived from topology, simulation, and advisor intelligence for Write-Heavy Transactional Platform. Traceable to YAML knowledge entities.
Context
Payment processors, order management systems, IoT ingestion pipelines, and audit logging platforms all share the same structural problem: they must durably record every write, guarantee at-least-once event delivery downstream, and maintain strict ACID consistency: while sustaining write rates that eventually exceed what a single PostgreSQL primary can absorb. Naive synchronous dual-writes (write to DB + publish to Kafka in one transaction) create distributed consistency hazards. The outbox pattern solves this by making event publishing a side effect of the same committed transaction, consumed asynchronously by a CDC relay. Primary operational risks include: WAL slot retention crisis: a stalled CDC consumer causes PostgreSQL to retain all WAL since the slot's confirmed_flush_lsn, exhausting disk within hours at high write rates; Checkpoint amplification under write bursts: frequent dirty page flushes at high write volume cause I/O spikes that stall all concurrent writes during checkpoint; Outbox table growth: if the CDC relay falls behind, the outbox table accumulates unprocessed rows, degrading write performance via index bloat and VACUUM pressure.
Decision
We will adopt the **Write-Heavy Transactional Platform** architecture pattern. This is a high-complexity architecture appropriate for teams at experienced backend team level or above. The advisor rates this pattern as 'advanced' operational maturity.
Rationale
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. The primary architectural strength is: Write-heavy transactional workloads that emit downstream events (order placed, payment captured) benefit from the outbox pattern…. Write-heavy transactional workloads that emit downstream events (order placed, payment captured) benefit from the outbox pattern to ensure events are published exactly when the database transaction commits: never before, never after. Key trade-off: Adds one INSERT per transaction to the outbox table: minor but nonzero write amplification. Operational note: Outbox table grows with write volume: implement TTL-based cleanup or partition pruning. Evidence: Payment processors like Stripe use outbox-style patterns to ensure webhook delivery matches transaction commits. Core technology stack: postgresql, kafka.
Architectural 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…
Accepted Tradeoffs
- ⚠Outbox pattern adds a synchronous outbox INSERT to every transaction, increasing write latency by ~1–3ms per operation; the benefit is guaranteed at-least-once event delivery (idempotent consumers dedupe on event ID for effectively-once)
- ⚠WAL-based CDC requires PostgreSQL logical replication to be enabled, adding WAL volume overhead and a mandatory replication slot monitoring obligation
- ⚠PgBouncer transaction-mode pooling is incompatible with named prepared statements and advisory locks: applications must avoid both
- ⚠Kafka decouples write durability from downstream consumer availability, but introduces eventual consistency between the transaction record and any derived read model
- ⚠Increasing write throughput via batching reduces per-row latency but increases per-transaction size, raising checkpoint pressure and WAL segment rotation frequency
Risks
Each 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.
PostgreSQL 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.
Concurrent writers to the same rows serialize behind each other's row locks, so latency is set not by the work a transaction does but by how long it waits for the writers ahead of it. On a hot row the queue depth, and therefore the tail latency, grows with concurrency while throughput flattens. Blocked writers hold connections open, so a single contended row can drain the connection pool as a secondary failure.
PostgreSQL's checkpoint process periodically flushes all dirty shared buffer pages to disk, causing a predictable I/O storm at each checkpoint interval that spikes disk utilisation and elevates write transaction latency for the duration of the flush.
Alternatives Considered
AI Retrieval-Augmented Generation Platform shares core technology (kafka, postgresql) with the chosen architecture but applies different structural patterns; Write-Heavy Transactional Platform is a better fit for the identified workload profile.
Analytics Data Platform shares core technology (kafka, postgresql) with the chosen architecture but applies different structural patterns; Write-Heavy Transactional Platform is a better fit for the identified workload profile.
API Gateway Platform shares core technology (kafka, postgresql) with the chosen architecture but applies different structural patterns; Write-Heavy Transactional Platform is a better fit for the identified workload profile.
Audit and Compliance Platform shares core technology (kafka, postgresql) with the chosen architecture but applies different structural patterns; Write-Heavy Transactional Platform is a better fit for the identified workload profile.
Scaling Thresholds
Signals indicating the architecture is approaching its scaling limits:
Tier 1: Connection Pool Saturation
Signal: PgBouncer wait_queue > 0 sustained; application p99 write latency rising faster than PostgreSQL p99; pool_mode=transaction showing >80% utilization
Evolution: Increase PgBouncer pool_size incrementally; profile transaction duration to right-size pool; consider separate pools for write-heavy and read-only workloads
Tier 2: WAL and Checkpoint Pressure
Signal: 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
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
Tier 3: Lock Contention and Hot Partition
Signal: pg_locks shows contended rows with wait events > 5ms; write throughput plateauing despite available CPU; deadlock errors appearing in application logs
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
Tier 4: Primary Write Ceiling
Signal: 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
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
Migration Path
Single PostgreSQL with synchronous dual-write (DB + Kafka in application code) → PostgreSQL + outbox pattern + WAL CDC relay to Kafka
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
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
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
Operational Requirements
- Minimum team maturity: Experienced Backend Team: This scenario has high operational complexity. It is recommended for Experienced Backend Team teams or higher.
- Runbooks and alerting for high-severity risks: 3 high-severity risks identified. Each requires a documented runbook, alerting threshold, and on-call response procedure before running in production.
- Event stream operations expertise: This architecture includes event stream infrastructure (Kafka, Kinesis, or similar). Operations requires consumer group management, partition assignment, dead-letter handling, and lag monitoring.
- Replica lag monitoring and lag-aware routing: Read replicas must be monitored for replication lag. The application router must include a max_lag_ms threshold; queries above that threshold must be redirected to the primary.