Compare Scenarios

Migration Step 1

Both scenarios define a migration step at this stage. Audit and Compliance Platform: triggered by 'Compliance audit finding that audit records were modified af'. Financial Ledger Platform: triggered by 'Audit requirement to reconstruct account state at any histor'.

Migration Step 2

Both scenarios define a migration step at this stage. Audit and Compliance Platform: triggered by 'Compliance report generation taking > 5 minutes against Post'. Financial Ledger Platform: triggered by 'Kafka publish failures causing financial transaction rollbac'.

Migration Step 3

Both scenarios define a migration step at this stage. Audit and Compliance Platform: triggered by 'SIEM consumer lag causing it to fall behind retention window'. Financial Ledger Platform: triggered by 'Financial dashboard query p99 > 500ms causing dashboard-driv'.

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"

Tier 1: Integrity Chain Write Serialization: Per-partition chain-tip read before each insert serializing concurrent audit writers. Recommended 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. .

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

Tier 2: Actor Query Full-Partition Scan: Missing secondary index table for actor_id and resource_id lookup paths across time-partitioned audit data. Recommended 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. .

pg_locks shows contended rows on accounts table; write p99 > 50ms; deadlock errors in application logs; pg_stat_activity showing many transactions waiting for RowExclusiveLock on the same account rows

Tier 1: Hot Account Lock Contention: Concurrent debit/credit transactions competing for the same account row versions. Recommended evolution: Implement optimistic locking with version column and retry; or queue concurrent updates for the same account entity through an account-scoped serialization queue at the application layer; or partition the accounts table by account range .

Write p99 > 100ms with synchronous_commit = remote_apply; replica WAL apply lag visible in pg_stat_replication; network jitter between primary and replica causing write latency spikes correlating with replication ACK delays

Tier 2: Synchronous Replication Write Latency: Synchronous replication write-ahead wait amplifying network latency for every committed transaction. Recommended evolution: Co-locate primary and replica in the same availability zone for lowest replication RTT; tune wal_sender_timeout and recovery_min_apply_delay; evaluate whether synchronous_commit = on (durable to primary WAL only) is acceptable for your regulatory risk model .