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'. AI Retrieval-Augmented Generation Platform: triggered by 'LLM responses requiring more factual accuracy or domain-spec'.

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'. AI Retrieval-Augmented Generation Platform: triggered by 'Document ingestion p99 > 500ms due to embedding API call in '.

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'. AI Retrieval-Augmented Generation Platform: triggered by 'Index scan range too large for per-query latency targets; te'.

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

Retrieval quality metrics (MRR, NDCG) declining despite stable query volume; users reporting irrelevant context being surfaced; pgvector IVFFlat probes set below recommended value for current document count

Tier 1: Vector Index Recall Degradation: IVFFlat index not rebuilt after significant document additions; or probes too low for current index size. Recommended evolution: Schedule periodic index rebuilds triggered by document count growth (e.g., rebuild at 2x the document count present at last index build); increase ivfflat.probes to improve recall at cost of query latency; evaluate HNSW for recall-critical workloads .

PostgreSQL process memory > 8GB; OOM killer events on the database host; vector query p99 latency increasing as shared_buffers evicts vector index pages; pg_stat_bgwriter showing high buffers_clean rate

Tier 2: PostgreSQL Memory Pressure from Vector Operations: Vector index (HNSW or large IVFFlat) and embedding storage competing with relational data for shared_buffers. Recommended evolution: Increase PostgreSQL shared_buffers to 40% of available RAM; move vector tables to a dedicated tablespace on NVMe; partition large vector tables by document category to reduce per-query index scan range; evaluate dedicated pgvector replica for query isolation .