Polyglot Persistence and Workload Separation (CQRS Pattern)

Polyglot persistence recognizes that a single database cannot optimally serve all workload types within a complex application. The Command Query Responsibility Segregation (CQRS) pattern separates write workloads (commands) from read workloads (queries) using specialized stores for each. LinkedIn's Espresso and Pinot architecture exemplifies this: Espresso, a document oriented Online Transaction Processing (OLTP) store, handles member profile updates and activity writes with read your writes consistency within a data center, serving 100K+ writes per second with p99 latencies under 20 milliseconds. Meanwhile, Pinot, a columnar Online Analytical Processing (OLAP) engine, ingests change streams from Espresso in real time and powers analytical dashboard queries with sub second latencies over billions of records, handling 100K+ queries per second. The separation allows each system to optimize for its workload. Espresso uses row oriented storage with secondary indexes on frequently queried fields, optimizing for point lookups and single document updates. Pinot uses columnar compression and inverted indexes, optimizing for scanning millions of rows to compute aggregations like daily active user counts or engagement metrics. Without this separation, adding analytical queries to Espresso would create resource contention: long running aggregations would starve transactional updates, inflating p99 write latencies from 20 milliseconds to 200+ milliseconds during reporting hours. Netflix employs a similar pattern for its streaming service. EVCache (an in memory key value store based on Memcached) serves hot data like user preferences and recently watched titles with p99 latencies under 2 milliseconds, absorbing over 30 million requests per second globally. Cassandra stores durable user activity and viewing history with p99 write latencies under 10 milliseconds, handling 1 million writes per second across multiple regions with eventual consistency. Elasticsearch indexes content metadata for search queries, returning results in 100 to 300 milliseconds for complex full text searches with filters and ranking. This three tier approach costs operational complexity (managing three systems, synchronizing data) but delivers 10x better performance than forcing all workloads into a single database. The data flow requires careful orchestration. Changes to the primary OLTP store propagate to analytical systems via change data capture (CDC) streams, introducing replication lag typically between 100 milliseconds and 5 seconds depending on load. Applications must account for this: a user who updates their profile sees the change immediately in the transactional view but may wait 2 to 3 seconds before analytics dashboards reflect the update. For critical paths, hybrid queries that join fresh transactional data with slightly stale analytical aggregates provide acceptable consistency while maintaining low latency.

E-commerce platform separates order processing from analytics: PostgreSQL handles order transactions with ACID guarantees and p99 writes under 30 milliseconds, Redshift ingests order data via hourly batch jobs for business intelligence queries that scan millions of orders in under 10 seconds, avoiding long running analytics from blocking checkout flow

Social media feed architecture uses polyglot: DynamoDB stores user posts and relationships with p99 reads under 10 milliseconds for timeline generation, Elasticsearch indexes post content for full text search with 200 millisecond query latencies, Redis caches hot feed data with p99 under 1 millisecond, Kafka streams connect the systems with sub second lag