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'. Analytics Data Platform: triggered by 'OLTP query p99 degrading during analytics reporting windows;'.

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'. Analytics Data Platform: triggered by 'Sub-minute analytics freshness SLA required; ETL scheduling '.

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'. Analytics Data Platform: triggered by 'Dashboard query p95 > 5s on frequently accessed aggregation '.

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. .

Kafka consumer group lag (bytes or offsets) growing for the analytics topic group; ClickHouse dashboard timestamps falling behind wall clock by > 60s; ClickHouse insert throughput < Kafka produce rate

Tier 1: Consumer Lag and Freshness Degradation: ClickHouse insert throughput insufficient for Kafka produce rate. Recommended evolution: Tune ClickHouse insert buffer size and async_insert settings; increase consumer parallelism up to the Kafka partition count; batch inserts into ClickHouse using the Buffer engine or materialized views with merge trees .

One Kafka partition offset growing significantly faster than others; one consumer instance CPU/network saturated while others are idle

Tier 2: Hot Partition and Skewed Consumer Load: Skewed partition key distribution: high-cardinality entity routing the same high-volume key to one partition. Recommended evolution: Add a secondary hash suffix to the partition key to distribute load; increase topic partition count (note: keyed ordering breaks for existing messages); re-evaluate partition key selection based on actual cardinality measurements .