Architecture Review: Audit and Compliance Platform

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.

Affects 1 node. (Write-Heavy Transactional)

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.

Affects 0 nodes

Seed 'WAL Saturation Risk Probe' identifies 2 metrics relevant to wal_saturation.

Metrics to instrument: error_rate, p95_latency_ms

Trigger: Compliance audit finding that audit records were modified after write; regulatory requirement (SOC 2 Type II, SOX, HIPAA) for tamper-evident audit log; inability to reconstruct historical actor activity from mutable state. Migrate from 'Application-level audit log in mutable table with update/delete allowed' to 'Append-only partitioned audit log with cryptographic integrity chain'. Bootstrap pre-migration state as a sealed genesis block per partition. Run the new append-only path in parallel with the mutable path for 30 days, comparing record counts. Decommission the mutable path only after chain integrity is verified across all source systems.

Existing mutable audit records cannot be retrofitted with a cryptographic chain; the chain starts from the migration cutover date: pre-migration records must be bootstrapped as a sealed historical block; Application code that previously used UPDATE or DELETE to correct audit records must be refactored; correction events must be new append-only records, not retroactive modifications

Trigger: Compliance report generation taking > 5 minutes against PostgreSQL; month-end audit export queries competing with write path and causing ingestion latency spikes; need for fast aggregate queries across 12+ months of audit history. Migrate from 'PostgreSQL full-text queries for compliance reports' to 'ClickHouse for aggregate compliance analytics with CDC-based replication'. Validate ClickHouse query latency against representative compliance workloads (actor access reports, resource access timelines, change diff exports) before cutting over report generation. Target p99 < 2s for the most common compliance query patterns.

ClickHouse CDC consumer lag means compliance reports have a staleness window; this must be disclosed to auditors and reflected in the compliance tooling; Initial ClickHouse population from PostgreSQL must complete before CDC takes over: initial sync ordering must preserve the chain sequence across all partitions

Simulation demonstrates critical degradation of redis, postgresql

Without a runbook, recovery from this failure mode will be ad-hoc

Simulation demonstrates critical degradation of postgresql

Without a runbook, recovery from this failure mode will be ad-hoc

Evolution from Unified OLTP + Analytics on PostgreSQL → Separated OLTP (PostgreSQL) + OLAP (ClickHouse/Snowflake)

Migration complexity: medium. Rollback: always.

Evolution from Single Redis Node / Sentinel Cluster → Distributed Redis Cluster (Consistent Hash Ring)

Migration complexity: medium. Rollback: complex.

Signal: PostgreSQL write p99 > 20ms with low connection count; pg_stat_activity showing transactions serialized on the same partition's chain-tip read; ingestion throughput plateauing well below hardware limits; auto_explain showing sequential scan on audit_events for "SELECT hash FROM audit_events ORDER BY id DESC LIMIT 1"

Bottleneck: Per-partition chain-tip read before each insert serializing concurrent audit writers. Evolution: Introduce partition-level chain sequence tables: a single row per partition tracking the current chain tip with an advisory lock, eliminating the full table read. Alternatively, shard the integrity chain by source system or tenant, accepting per-shard chains rather than a single global chain. Use PostgreSQL INSERT ... RETURNING with sequence-assigned IDs to eliminate the pre-insert read entirely, deferring chain hash computation to an async integrity sealer that appends hashes in order without blocking the write path.

Signal: Compliance investigator queries returning in > 30s; PostgreSQL showing high sequential scan counts on audit_events partitions; investigator-facing API p99 > 10s; pg_stat_statements showing actor_id-scoped queries without partition pruning in the query plan

Bottleneck: Missing secondary index table for actor_id and resource_id lookup paths across time-partitioned audit data. Evolution: Build a secondary index table audit_events_by_actor(actor_id, event_time, event_id) populated synchronously on insert. Accept the additional write per event as the cost of O(log n) actor-scoped queries. Alternatively, route actor-scoped queries to ClickHouse where columnar storage makes actor_id filters efficient without a secondary B-tree index.